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, BULK INSERT statement (Transact-SQL), and OPENROWSET(BULK…) function (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.
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 |
OPENROWSET(BULK…) |
ORDER ( { column [ ASC | DESC ] } [ ,...n ] [ UNIQUE ] ) |
Hint |
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