sqlsurfer11 (12/23/2012)

---

Do I drop the indexes and the primary key and then insert data and add them back? .

sqlsurfer11 (12/23/2012)

---

Hi I need to refresh data in a table from another table. It has about ~14 million records that need to be inserted.
(was planning to use import data task, there are no dependencies except for a view)

The destination table (identical to source table) has a primary key and clustered index. It also has few non clustered indexes.
Do I drop the indexes and the primary key and then insert data and add them back?

Or do I just directly copy data without dropping indexes. This seems to me to negatively impact performance. But, please let me know.

Peter Bannister (12/24/2012)

---

Hi,
a while ago I had to create an environment copy for a large database at a client site.

I tried several methods to copy the large tables, but the one which provided the fastest copy was to drop the table and indexes - "select into" the add back all the indexes and stats.

A couple of caveats:
1) this was a copy to development!!!
2) The logging method was simple
3) I generated a script to run through the indexes and recreate them

I hope this gives you food for thought.

CHeers

Peter

Eric M Russell (12/25/2012)

---

The reason why SELECT INTO <new table> is faster than INSERT INTO <existing table> is because SELECT INTO doesn't transaction log the data pages being inserted. In the event of rollback, SQL Server simply has to discard the table.

Bhuvnesh (12/25/2012)

---

any article/reference for this ??

Eric M Russell (12/25/2012)

---

The reason why SELECT INTO <new table> is faster than INSERT INTO <existing table> is because SELECT INTO doesn't transaction log the data pages being inserted. In the event of rollback, SQL Server simply has to discard the table. However, when inserting into an existing table, SQL Server has to keep track of what pages are being modified to support a rollback.

SELECT TOP 10 *
FROM   Fn_dblog(NULL, NULL)
WHERE  allocunitname = <Your_table_name>
ORDER  BY [Log Record Length] DESC