Optimizing Bulk Import Performance

This topic describes options for optimizing the bulk import of data into a table in Microsoft SQL Server by using a bcp command, BULK INSERT statement, or OPENROWSET(BULK...) function (Transact-SQL). To bulk import or export data as rapidly as possible, it is important to understand the factors that affect performance and the command qualifiers that are available to manage performance. Where possible, use a Transact-SQL statement to bulk import data into SQL Server because Transact-SQL is faster than bcp.

Whether the data is being copied from a single client or copied in parallel from multiple clients.

Whether the data is to be copied between two computers on which SQL Server is running.

Important

In SQL Server 2005 and later versions, bulk-import optimizations are available when triggers are enabled. Row versioning is used for triggers and stores the row versions in the version store in tempdb. Before you can bulk import a large batch of data records using triggers, you may have to expand the size of tempdb to accommodate the impact of the triggers on the version store.

To speed up the bulk import of data, SQL Server provides you with the following methods:

Using minimal logging

The simple recovery model minimally logs most bulk operations.

For a database under the full recovery model, all row-insert operations that are performed during bulk import are fully logged in the transaction log. For large data imports, this can cause the transaction log to fill rapidly. For bulk-import operations, minimal logging is more efficient than full logging and reduces the possibility that a bulk-import operation will fill the log space. To minimally log a bulk-import operation on a database that normally uses the full recovery model, you can first switch the database to the bulk-logged recovery model. After bulk importing the data, switch the recovery model back to the full recovery model. For more information, see Considerations for Switching from the Full or Bulk-Logged Recovery Model.

SQL Server allows data to be bulk imported into a single table from multiple clients in parallel. All three bulk import mechanisms support the parallel import of data. This can improve the performance of data import operations.

By default, a bulk-import operation assumes that a data file is unordered. If the table has a clustered index, the bcp utility, BULK INSERT statement, and OPENROWSET(BULK…) function (Transact-SQL) enable you to specify how data in the data file is sorted during a bulk-import operation. It is optional for data in the data file to be sorted in the same order as the table. However, you can improve performance of the bulk-import operation if you specify the same ordering for the data file as the table.