September 23, 2014 at 6:57 am
Folks:
We recently upgraded our SQL server from 2008 R2 to SQL 2014 and I see a difference in how the sort is handled in 2014. We have a table 'tblemp' with index on SDate (Date column).
create table tblemp
(empid int,
empname varchar(10),
SDate datetime)
insert into tblemp values (1,'Paul','05/04/2014')
insert into tblemp values (2,'John','06/05/2014')
insert into tblemp values (3,'Chris','04/15/2014')
insert into tblemp values (4,'James','03/20/2014')
insert into tblemp values (5,'David','02/17/2014')
create clustered index idxtmpemp on tblemp(SDate)
In SQL 2008 R2 when we executed the below query we got the output in DESCENDING order of SDate (Date) based on the order by to the insert into @tblOutput, but in SQL 2014 we are getting the output in ASCENDING order. Any Idea what might be the problem?
DECLARE @tblOutput TABLE
(empid int,
ename varchar(10),
AsOfDate datetime)
insert into @tblOutput
select * from tblemp
order by SDate desc ------ DESCENDING
select * from @tblOutput
Thanks !
September 23, 2014 at 7:00 am
No ORDER BY, no guarantee of order. End of story.
If you want a particular order, you put an ORDER BY. If you don't, you're telling SQL that it can return the data in any order it feels like. And that's what's happening.
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
September 23, 2014 at 7:26 am
I agree that SQL can return the data in any order it feels like and there has be a ORDER BY, but how come it returns the data in descending order in SQL 2008 R2 version? I checked almost 5 SQL servers with 2008 r2 and it returned the data in descending order and only on 2014 it is not.
September 23, 2014 at 7:35 am
Because the query plan that the optimiser generated there resulted in the data coming out in that order once the last query operators had finished with it.
There is NO guarantee of order unless there's an order by. Any change in the query optimiser and/or query processor (as happens in new versions) can and will change the order the rows display in.
If you need a specific order, you must specify an order by. If you don't, anything goes and you should not make any assumptions or conclusions about how the data will come out.
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
September 23, 2014 at 7:44 am
Here is a great article which explains this. http://blogs.msdn.com/b/conor_cunningham_msft/archive/2008/08/27/no-seatbelt-expecting-order-without-order-by.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply