Share via


Controlling the Sort Order When Bulk Importing Data

By default, a bulk-import operation assumes that a data file is unordered. If the table has a clustered index, the bcp command and BULK INSERT statement (Transact-SQL) let you specify how data in the data file is sorted during a bulk-import operation. Sorting data in the data file in the same order as the table is optional. However, specifying the same sort order can increase the speed of the bulk-import operation.

Note

The INSERT ... SELECT * FROM OPENROWSET(BULK...) does not take an ORDER hint.

Command Qualifiers

The following table summarizes the command qualifiers for specifying the sort order for a bulk-import operation.

Command Qualifier Qualifier type

bcp

-h"ORDER(column [ASC

DESC] [,...n] )"

Hint

BULK INSERT

ORDER ( { column [ ASC

DESC ] } [ ,...n ] ) ]

Argument

Best Practice   The order of data in the table is determined by the clustered index. To improve the bulk-import performance when a table has a clustered index, the columns listed in the ORDER hint or ORDER clause should match the columns and be in the same order as in the clustered index.

See Also

Concepts

Optimizing Bulk Import Performance

Other Resources

bcp Utility
BULK INSERT (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance