bcp Modes

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.

To determine the bcp mode that is best for your copying task, consider:
  • 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

Related concepts
Before You Transfer