bcp in works in one of three modes.
Slow bcp – logs each row insert that it makes, used for tables that have one or more indexes.
Fast bcp – logs only page allocations, copying data into tables without indexes or at the fastest speed possible. Use fast bcp on tables with nonclustered indexes.
Fully logged fast bcp – provides a full log for each row. Allows you to use fast bcp on indexed and replicated tables.
Although fast bcp might enhance performance, slow bcp gives you greater data recoverability. Fully-logged fast bcp provides a combination of both.
Size of the table into which you are copying data
Amount of data that you are copying in
Number of indexes on the table
Whether the table is replicated
Amount of spare database device space that you have for re-creating indexes
The modes bcp uses depending on index type:
|
Table Properties |
bcp Mode for bulkcopy on, With Logging |
bcp Mode for bulkcopy on Without Logging |
|---|---|---|
|
Clustered index |
Slow mode |
Slow mode |
|
Replicated table with indexes or triggers, but no clustered index |
Fast mode |
Slow mode |
|
Nonclustered index and no triggers |
Fast mode |
Slow mode |
|
Triggers, and no indexes |
Fast mode |
Fast mode |
|
Nonclustered index with triggers |
Fast mode |
Fast mode |
|
No indexes, no triggers, and no replication |
Fast mode |
Fast mode |