TOP N Sort and Sort operator returning different order when ORDER BY column has same value - by Debdutta

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


1
3
Sign in
to vote
ID 778807 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 2/7/2013 11:46:26 PM
Access Restriction Public

Description

The TOP 1  EmpId is not returning the same record what I can see the First record without using TOP 1. As a example, the table #Employee has three columns, EmpId, EmpName and DOJ (Date of Joining). I wanted to pick up the First Record in descending order of Date of Joining. I Observed, if DOJ is same for first few records then TOP 1 is not returning the first record what it's returning without TOP 1 sort operator with same ORDER BY. 
Now, I write two queries. One with TOP all and another with without TOP with same ORDER BY to return all the records and check the output of record order. I can see the record order of the output result is different. When I see the execution plan, I can see to sort there are two different operator one is Top N Sort and another is Sort. Does this two different soft operator work differently? Finally I wrote the query with ROW_NULBER function and getting the desire output.  Is it a bug that Top N Sort operator change the order  what it returns by the order by? OR please let me know what algorithm follow by the Top N sort and sort when there is a tie in Order by column value.
Sign in to post a comment.
Posted by Microsoft on 7/26/2013 at 2:07 PM
Hello Debdutta. Thanks for sharing your feedback with us. As Martin has explained, the behavior in question is by design.

-Walter A Jokiel, Program Manager, SQL Server (wajokiel@microsoft.com)
Posted by Microsoft on 7/26/2013 at 2:06 PM
Hello Debdutta. Thanks for sharing your feedback with us. As Martin has explained, the behavior in question is by design.

-Walter A Jokiel, Program Manager, SQL Server (wajokiel@microsoft.com)
Posted by Martin Smith on 2/9/2013 at 5:23 AM
This is not a bug. If you want deterministic results you need to add the column you want to use as the tie breaker into the ORDER BY clause.