Copying Tables with Indexes

The bcp utility is optimized to load data into tables that do not have indexes associated with them.

bcp loads data into tables without indexes at the fastest possible speed. Fast-logged bcp logs any data changes to the table.

When you copy data into a table that has one or more indexes, depending on the index type and the locking scheme, you can use fast bcp. This includes indexes that are implicitly created using the unique integrity constraint of a create table statement. However, bcp does not enforce the other integrity constraints defined for a table.

Note: The log can grow very large during slow bcp because bcp logs inserts into a table that has indexes. After the bulk copy completes, back up your database with dump database, then truncate the log with dump transaction after the bulk copy completes and after you have backed up your database with dump database.

The performance penalty for copying data into a table that has indexes in place can be severe. If you are copying in a very large number of rows, it may be faster to drop all the indexes beforehand with drop index (or alter table, for indexes created as a unique constraint); set the database option; copy the data into the table; re-create the indexes; and then dump the database. Remember to allocate disk space for the construction of indexes: about 2.2 times the amount of space needed for the data.