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)