FASTTRANSFER_SORTEDINPUT
Forces the sorted input hint for SQL Server bulk copy operations when using the msbulk target connection type.
Overview
| Property | Value |
|---|---|
| Connection Types | msbulk |
| Default Value | false |
| Valid Values | true, false |
| Since Version | 0.17 |
Description
When set to true, FastTransfer instructs SQL Server's bulk copy API to treat incoming data as sorted according to the clustered index. This can significantly improve performance when:
- The source data is already sorted by the target table's clustered index columns
- The target table has a clustered index
- You want to minimize page splits and fragmentation
The hint is applied on the partition column of a partitioned target table.
With DataDriven, DBPartition and Timepartition
With a partitioned SQL Server target, the DataDriven, DBPartition and Timepartition parallel methods send the sorted hint automatically, without this variable. Each parallel stream holds a slice of the data, but the rows are not sorted inside a stream. So, since 0.18, the hint is not sent when the target table has a rowstore clustered index. SQL Server would check the order and stop the load with this error:
Cannot bulk load. The bulk data stream was incorrectly specified as sorted or the data violates a uniqueness constraint imposed by the target table.
Heap and clustered columnstore targets still get the hint. If your source data is really sorted on the target partition column, for example with an ORDER BY in the --query, set FASTTRANSFER_SORTEDINPUT=true to force the hint.
Usage
- Windows
- Linux
Temporary (current session):
# Enable sorted input hint
set FASTTRANSFER_SORTEDINPUT=true
# Run FastTransfer
./FastTransfer --targetconnectiontype msbulk ...
Temporary (current session):
# Enable sorted input hint
export FASTTRANSFER_SORTEDINPUT=true
# Run FastTransfer
./FastTransfer --targetconnectiontype msbulk ...