The bcp program is optimized to load data into tables that do not have indexes associated with them. It 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, use fast and fast-logged bcp. This includes indexes 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.
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.
