clipped from www.sql-server-performance.com

Performance Tuning Tips for SQL Server BCP

By : Brad McGehee Jan 18, 2007

If you need to import or export very large quantities of data, it is often much faster to use bcp that it is to use DTS or SSIS. Of course, bcp lacks many features and options available for DTS and SSIS, and it a little more time-consuming to set up sometimes. But if you don't need to manipulate data, or if you deal with large amounts of data, or if you need a simple way to transfer data using a command line option, then you should investigate using bcp. [7.0, 2000, 2005] Updated 6-12-2006

*****

If appropriate, use the BCP native format instead of the character format to import and export data. It is much faster. Of course, this will limit you to BCP data between SQL Server databases with the same Character Sets and Dictionary Sort order. [6.5, 7.0, 2000, 2005] Updated 6-12-2006

*****

If appropriate, use the BCP fast mode to import data. The fast mode can save you substantial time for your data loads. Because there is no "fast mode" option to turn on, you must take all of the following steps to use the BCP fast mode:

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

·         The target table should not have any indexes.

·         The target table must not be published for replication.

·         Use the TABLOCK to lock the target table.

Get Clipmarks - The easiest way to email text, images and videos you find on the web.