Warning: SQL Server 2005 INSERT Sort Order Broken
In my previous post I demonstrated how to match primary keys from a source table with those generated using an INSERT…SELECT in the target table.
Warning: there is evidence that SQL Server 2005 does not preserve sort order in all cases, contrary to Microsoft’s statements in the Knowledge Base and here.
Although both articles state that the identity generation order of an ordered INSERT…SELECT between two tables in the same database is preserved, we had to learn that there is about 1% of cases (depending on input data) where the sort order is not honored. Apparently it depends on how the optimizer arranges the execution plan.
There has been a similar problem addressed to MS support, with the answer being that this faulty behavior is only to be fixed in SQL Server 2008: link.
I will keep the post for informational purposes. Sadly, due to a rare bug it cannot be used reliably in all cases. As a workaround you might want to perform INSERTs in a loop using SCOPE_IDENTITY(), or (a bit ugly) insert the temporary key into a column in the target table which has the same data type (and capturing the inserted values in the OUTPUT clause). Then update the temporarily used column with the real value. Note that the INSERT and UPDATE should both be performed in a single transaction which prevents a dirty read on the newly inserted records.