John Mitchell-245523 (8/10/2010)
Gail, reading between the lines, I think what the senior DBA is saying is that if you insert data that is already sorted in the order of the clustered index of the destination table, then SQL Server has less work to do when the data is inserted.
The question is, how would SQL know that the data is in order if it doesn't put a sort in?
In the initial post, neither of the order by statements matched the clustered index, so if SQL honoured those order bys, it would have to order twice during the insert.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability