BULK INSERT ORDER Hint: Loading Pre-Sorted Files Without a Sort

BULK INSERT ORDER does not sort your file, it tells SQL Server the file is already sorted. If the file breaks that promise, the load fails. So check the file before you add the hint.

What the hint really says

Many people read ORDER as “please sort this on the way in”. It is the opposite. You are saying, “I swear these rows already arrive in the order of the clustered key.” SQL Server believes you, and it can skip its own sorting work.

That belief is checked. If the file is out of order, the load stops with an error. Think of it as a smoke detector, not a fire sprinkler.

Create the two small files

BULK INSERT reads files from the SQL Server machine, so the path must make sense to the server. Create a folder named C:\Temp, open Notepad, and save these four lines as C:\Temp\32160-ordered.csv (UTF-8).

ItemId,ValueText
1,one
2,two
3,three

Save the second file as C:\Temp\32160-unordered.csv (UTF-8). It has the same header, but the rows come in the order 3, 1, 2. The SQL Server service account needs permission to read the folder.

ItemId,ValueText
3,three
1,one
2,two

Load the ordered file

The target is a temp table with a clustered primary key on ItemId. The hint names that key and its direction. The file honors the promise, so all three rows arrive.

CREATE TABLE #OrderedLoad
(
    ItemId    int PRIMARY KEY CLUSTERED,
    ValueText varchar(20)
);

BULK INSERT #OrderedLoad
FROM 'C:\Temp\32160-ordered.csv'
WITH (FORMAT = 'CSV', FIRSTROW = 2, ORDER (ItemId ASC));

SELECT ItemId, ValueText FROM #OrderedLoad ORDER BY ItemId;

You see 1 one, 2 two and 3 three. Notice the ORDER BY on the final SELECT. The hint says nothing about how SELECT returns rows, so always ask for the order you need.

Break the promise on purpose

Now load the unordered file with the same hint. I wrap the load in TRY and CATCH so you can see the error as a result set.

TRUNCATE TABLE #OrderedLoad;

BEGIN TRY
    BULK INSERT #OrderedLoad
    FROM 'C:\Temp\32160-unordered.csv'
    WITH (FORMAT = 'CSV', FIRSTROW = 2, ORDER (ItemId ASC));
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS error_number, ERROR_MESSAGE() AS error_message;
END CATCH;

SELECT COUNT_BIG(*) AS rows_after_failed_load FROM #OrderedLoad;

The error is 4819. The message names the two rows that are out of order: the first has key 3 and the one after it has key 1. The table holds zero rows afterward, so a failed load leaves nothing half done.

Picture a vendor who sends a nightly file “sorted by ItemId”. One night a new export tool changes the sort. With the hint in place, your job fails loudly at the first bad pair. Without it, SQL Server quietly sorts and you never learn the file changed.

The same file without the hint

Here is the part that surprises people. Load the very same unordered file without the ORDER hint, and it works. SQL Server simply sorts the rows itself, because the table has a clustered key.

BULK INSERT #OrderedLoad
FROM 'C:\Temp\32160-unordered.csv'
WITH (FORMAT = 'CSV', FIRSTROW = 2);

SELECT ItemId, ValueText FROM #OrderedLoad ORDER BY ItemId;

All three rows load. So the hint is never needed for correctness. Its only job is to save work when you can truly guarantee the order.

Is it worth adding?

This demo has three rows, so it cannot show any speed gain. I will not pretend it does. On a big file, the saved sort might matter. Test with a realistic file and the same table design before you promise anyone a speedup.

Also remember the key can have several columns. Then the file must be sorted by all of them, in the same direction as the hint. Run the negative test again whenever the source of the file changes. When you finish, delete the two files from C:\Temp.

DROP TABLE IF EXISTS #OrderedLoad;

Try the same checks with your own files before you trust the hint.

An ORDER hint is not a sorting command, it is a promise about the input.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.