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.

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

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

Command Qualifier Qualifier type


-h "ORDER( column [ASC

DESC] [,...n] )"



ORDER ( { column [ ASC

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


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.

Community Additions