The bulk insert functionality of SQL Server supports an ORDER hint to speed up inserting of already ordered data into a table with a clustered index. Without this hint, SQL Server forces an intermediate sort of the incoming data before inserting, which can add significant overhead (think tempdb spills). Existing unmanaged ways to bulk copy (bcp.exe, BULK INSERT, OPENROWSET(BULK, ...)) all offer ways to specify this hint, but SqlBulkCopy does not, leaving managed code out in the cold.
Proposed API
public class SqlBulkCopy
{
// Existing members left out
public SqlBulkCopyOrderHintColumnCollection OrderHintColumns { get; }
}
// New class
public sealed class SqlBulkCopyOrderHintColumn {
public SqlBulkCopyOrderHintColumn(string column, System.Data.SqlClient.SortOrder sortOrder);
public string Column { get; set; }
public SortOrder SortOrder { get; set; } // throws ArgumentException on SortOrder.Unspecified
}
// New class, same shape as SqlBulkCopyColumnMappingCollection
public class SqlBulkCopyOrderHintColumnCollection : System.Collections.CollectionBase {
public SqlBulkCopyOrderHintColumn Add(SqlBulkCopyOrderHintColumn bulkCopyOrderHintColumn);
// throws ArgumentException on SortOrder.Unspecified
public SqlBulkCopyOrderHintColumn Add(string column, SortOrder sortOrder);
public void Clear();
public bool Contains(SqlBulkCopyOrderHintColumn value);
public void CopyTo(SqlBulkCopyOrderHintColumn[] array, int index);
public System.Collections.IEnumerator GetEnumerator();
public int IndexOf(SqlBulkCopyOrderHintColumn value);
public void Insert(int index, SqlBulkCopyOrderHintColumn value);
public void Remove(SqlBulkCopyOrderHintColumn value);
public void RemoveAt(int index);
}Sample use
using (var sqlBulkCopy = new SqlBulkCopy(connection)) {
// leaving out other options
sqlBulkCopy.OrderHintColumns.Add("Column1", SortOrder.Descending);
sqlBulkCopy.OrderHintColumns.Add("Column2", SortOrder.Ascending);
sqlBulkCopy.OrderHintColumns.Add("Column3", SortOrder.Descending);
sqlBulkCopy.DestinationTableName = "Table1";
sqlBulkCopy.WriteToServer();
}This should result in SqlBulkCopy issuing a statement of the form
insert bulk Table1 (...) with (ORDER ([Column1] DESC, [Column2] ASC, [Column3] DESC))
Ensuring the input rows are actually ordered according to the hint is the programmer's responsibility; SQL Server will either ignore the hint (if the table is a heap) or produce an error (if the table is clustered) and SqlBulkCopy need not further verify this.
Open questions
- Should we make
SqlBulkCopyOrderHintColumnCollection a strongly typed collection to get with the times, or maintain consistency with the existing ColumnMapping property and use a non-generic collection?
- Should
SqlBulkCopyOrderHintColumn be immutable, again, mostly getting with the times? SqlBulkCopyColumnMapping is very much the opposite; it even has a parameterless constructor, which seems quite undesirable.
Background
See #5114 for previous discussion.
The bulk insert functionality of SQL Server supports an
ORDERhint to speed up inserting of already ordered data into a table with a clustered index. Without this hint, SQL Server forces an intermediate sort of the incoming data before inserting, which can add significant overhead (thinktempdbspills). Existing unmanaged ways to bulk copy (bcp.exe,BULK INSERT,OPENROWSET(BULK, ...)) all offer ways to specify this hint, butSqlBulkCopydoes not, leaving managed code out in the cold.Proposed API
Sample use
This should result in
SqlBulkCopyissuing a statement of the formEnsuring the input rows are actually ordered according to the hint is the programmer's responsibility; SQL Server will either ignore the hint (if the table is a heap) or produce an error (if the table is clustered) and
SqlBulkCopyneed not further verify this.Open questions
SqlBulkCopyOrderHintColumnCollectiona strongly typed collection to get with the times, or maintain consistency with the existingColumnMappingproperty and use a non-generic collection?SqlBulkCopyOrderHintColumnbe immutable, again, mostly getting with the times?SqlBulkCopyColumnMappingis very much the opposite; it even has a parameterless constructor, which seems quite undesirable.Background
See #5114 for previous discussion.