Why is BCP so fast?

Question

So BCP for inserting data into a SQL Server DB is very very fast. What is is doing that makes it so fast?

Answer 1

In SQL Server, BCP input is logged very differently than traditional insert statements. How SQL decides to handle things depends on a number of factors and some are things most developers never even consider like what recovery model the database is set to use.

bcp uses the same facility as BULK INSERT and the SqlBulkCopy classes.

More details here http://msdn.microsoft.com/en-us/library/ms188365.aspx

The bottom line is this, these bulk operations log less data than normal operations and have the ability to instruct SQL Server to ignore its traditional checks and balances on the data coming in. All those things together serve to make it faster.

Answer 2

It cheats.

It has intimate knowledge of the internals and is able to map your input data more directly to those internals. It can skip other heavyweight operations (like parsing, optimization, transactions, logging, deferring indexes, isolation). It can make assumptions that apply to every row of data that a normal insert sql statement can not.

Basically, it's able to skip a bulk of the functionality that makes a database a database, and then clean up after itself en masse at the end.