TechNet
Expand All
This documentation is archived and is not being maintained.

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.

ms177468.note(en-US,SQL.90).gifNote:
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

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.

Show:
© 2016 Microsoft