Why BULK INSERT/BCP includes Sort operator although I have done everything to avoid that? (SQL Server 2012)

Question

I have a problem with BULK INSERT/BCP process that includes sort operator although I have done everything to avoid that. Firstly, I have exported the data to file with BCP (native format) using queryout and with "ORDER BY Id ASC, Created ASC" clause. This gave me a file with sorted data. Secondly, I used the following T-SQL to import the data:

BULK INSERT dbo.LSZ_Table
FROM
    'T:\LSZ\data_file_native.dat'
WITH
    (
        FORMATFILE='T:\LSZ\format_n.xml'
        ,KEEPNULLS
        ,KEEPIDENTITY
        ,TABLOCK 
        ,ORDER (Id ASC, Created ASC)
        ,ROWS_PER_BATCH = 57380362
    )

ROWS_PER_BATCH = 57380362 - I know this because I previously exported the data. Column Id is of type int with Identity defined, not null. Column Created is of type datetime, not null.

Digression: Some may ask why such a sort order "Id ASC, Created ASC", it's because the table that will be populated with data is a staging table that will be switched in as one of the partitions (partitioning key is Created) and since unique indexes on partitioned tables have to have partitioning column as part of the key my "clustered, unique, primary key" index has key definition on (Id ASC, Created ASC).

The staging table with "clustered, unique, primary key" index on (Id ASC, Created ASC) is empty before import, I want to take advantage of minimal logging and importing into clustered index at the same time.

The format file contains such information regarding key columns:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="NativeFixed" LENGTH="4"/>
  ...
  <FIELD ID="31" xsi:type="NativeFixed" LENGTH="8"/>
  ...
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="Id" xsi:type="SQLINT"/>
  ...
  <COLUMN SOURCE="31" NAME="Created" xsi:type="SQLDATETIME"/>
  ...
 </ROW>
</BCPFORMAT>

Now what am I missing in my procedure or what am I doing wrong here that forces SQL Server to insert Sort Operator into the execution plan ?? I think I don't have to explain why I want that Sort operator out of the plan :) Execution Plan Link

Versions: Microsoft SQL Server 2012 - 11.0.5548.0 (X64) Developer Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

score 1 · Answer 1 · edited May 23 '17 at 10:26

OK, so I managed to resolve the problem. In fact most probably I encountered a bug in the product. When I tried to do the same BULK INSERT, but to a table without the Identity property, the nasty Sort was gone. I found some trick to load the table without Identity and then to use ALTER TABLE SWITCH to switch in (metadata change and no physical IOs when done carefully) into the table with the Identity property. It works for non-partitioned tables too (because every table has at least one partition). I found a connect item opened in 2008 that reported this problem, and to my surprise it's still active and the problem is still present in SQL Server 2012.

If you encountered the same problem please vote on the connect item: https://connect.microsoft.com/SQLServer/feedback/details/348970/bulk-insert-with-identity-column-creates-query-plan-with-sort

Why BULK INSERT/BCP includes Sort operator although I have done everything to avoid that? (SQL Server 2012)

1 Answers1