| title | description | author | ms.author | ms.reviewer | ms.date | ms.service | ms.subservice | ms.topic | dev_langs | |
|---|---|---|---|---|---|---|---|---|---|---|
Order hints for bulk copy operations |
Describes how to use order hints in bulk copy operations. |
David-Engel |
v-davidengel |
v-davidengel |
06/01/2022 |
sql |
connectivity |
conceptual |
|
[!INCLUDEappliesto-netfx-netcore-netst-md]
[!INCLUDEDriver_ADONET_Download]
Bulk copy operations offer significant performance advantages over other methods for loading data into a SQL Server table. Performance can be further enhanced by using order hints. Specifying order hints for your bulk copy operations can lower the insertion time of sorted data into tables with clustered indexes.
By default, the bulk insert operation assumes the incoming data is unordered. SQL Server forces an intermediate sort of this data before bulk loading it. If you know your incoming data is already sorted, you can use order hints to tell the bulk copy operation about the sort order of any destination columns that are part of a clustered index.
The following example bulk copies data from a source table in the AdventureWorks sample database to a destination table in the same database.
A SqlBulkCopyColumnOrderHint object is created to define the sort order for the ProductNumber column in the destination table. The order hint is then added to the SqlBulkCopy instance, which will append the appropriate order hint argument to the resulting INSERT BULK query.
[!code-csharp SqlBulkCopy.ColumnOrderHint#1]