Compatibility level changed from SQL server 2005 90 to SQL server 2012 110. The select into query missing the SORT operation in insert execution plan
Not sure whether this is a question but the query execution was "corrected" at one point - thought it was before 2005.
Previously some queries were executed including a sort (e.g. it followed the group by). The optimiser was changed so that it didn't perform the sort unless it was explicit - so giving more options on how to execute (made more use of parallelism). Should only make a difference to output queries (things that create a cursor) and people who were interested in relational theory wouldn't have relied on that anyway.
For a select into statement it would ignore any sort statements anyway as table data is unordered.
A way to get round that - and this isn't guaranteed - is to add to a temp table, add a clustered index then insert from that. The better way is to recognise that anything that relies on the execution plan or ordering of relational data is likely to fail at some point so find some other method.
At one point you could get round it by wrapping in 2 derived tables and it would sort before running the rest of the query but that was only for one version - now it is smart enough to realise what you are trying to do and removes the derived tables.
1 Like
Thanks for the detail reply. As you told lot of performance improvement happened in 2012 hence it may removed the SORT operation from select into option. I try to know is there any document related with this information because lot of site says generic about the order by clause but however the SORT is happening(on select into) in 90,100 but not works in 110 I need to satisfy my co-workers and management this issue is not happened because of developer. This behavior from SQL server 110 change.
I have attached the sample query plan as well.
sample query :
drop table #temp
drop table #temp1
SELECT TOP 10 * into #temp FROM [yourtable] order by 1
select * into #temp1 from #temp order by 1 desc
select * from #temp1
compatibility level 110:
sample query :
drop table #temp
drop table #temp1
SELECT TOP 10 * into #temp FROM [yourtable] order by 1
select * into #temp1 from #temp order by 1 desc
select * from #temp1
compatibility level 90:
In 2005 and 2008 - an insert into (or select into) with an order by would 'generally' insert the data into the table in that order and would allow for a select from that table to be ordered the same way.
So - in your example (Select * From #temp1) would return the data in the order 'expected'. However - this was actually a bug in SQL Server that was corrected in SQL Server 2012. As of that version, inserting into a table with an ORDER BY only affects the IDENTITY property of a column or which rows are selected by the TOP operator.
If the expectation is that inserting into (or select into) a table 'preserves' the order and a later select from that table without an ORDER BY is sorted - that expectation is incorrect.
The only way to guarantee the order of a select is with an ORDER BY on that select statement.
I don't remember anything being documented. I put something about it in articles about migration but they've probably been lost now.
Using the old behaviour was using a feature that wasn't part of the SQL standard so could be considered incorrect code which happened to work temporarily.
It's not difficult to create a test to demonstrate the difference and that should be enough to convince anyone that what you say is correct (even management).
Ask them whether they believe documents or demonstrations.