April 3, 2013 at 11:50 am
Is there a way to change the order that "select * from #table" returns data? It doesn't appear to return data in the same order that it was inserted into the table. This wasn't a problem with the same code in SQL Server 2005.
April 3, 2013 at 11:54 am
There is no guarantee of order if you do not specify an ORDER BY in your select query.
April 3, 2013 at 1:24 pm
There has never been a guarantee that a select without an order by will return the data in any particular order, whether it be insert order or anything else. It depends on the query plan picked and can and will change from time to time.
The only way to guarantee an order on a resultset is to put an ORDER BY on the outermost select.
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
October 16, 2014 at 6:08 pm
Is there any guarantee to the sort order of data from a function if you use a primary key or identity column of the temp table or does the same hold true to needing to put the order by on the select of the function?
October 16, 2014 at 8:28 pm
I'm still new but from what I understand, if it is relational there is never a guaranteed order. There however is a illusion of order based on the optimizer choosing the same plan over and over during execution. Change something in the query and it could effect the execution plan thus effecting the resulting order. The only guarantee for row order is the Order by clause at which point it is no longer relational.
***SQL born on date Spring 2013:-)
October 17, 2014 at 1:30 am
No ORDER BY, no guarantee of order. End of story.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply