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, 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.