When SQL Server bcps into an empty table with an index what does it do internally?

Question

If I have an empty table with an index and I do a bcp, does SQL Server (internally) drop/disable the index, load the data and then re-apply/enable/build the index?

Answer 1

No it doesn't but it will do its best to make it as fast as possible. Also you can help a little bit too by following some basic guidelines (see http://msdn.microsoft.com/en-us/library/ms177445.aspx).

Answer 2

No, the indexes are maintained throughout the operation. In fact, Microsoft says you can improve bcp performance by using the -h ORDER hint to sort the data according to your clustered index. See the bcp Utility documentation for details. However, as stated here, for the fastest import possible you should take the following steps:

· The "select into/bulkcopy" database option must be set to "true".

· The target table should not have any indexes. [emphasis added]

· The target table must not be published for replication.

· Use the TABLOCK to lock the target table.