Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

How does SQL Server figure out the order of the records in the result set of query execution?

I am trying to make heads or tails of it but find myself scratching my head. When I change the fields I am selecting the order also changes. When I execute the below SQL with a SELECT * I get the same records but in a much different order.

 SELECT TOP (900)
    AD.ATTACHMENTID,
    AD.NAME,
    AD.ISINLINE,
    AD.INSERTEDDATETIME,
    ATMT.ATTACHMENTBLOB,
    U.UFID
  FROM ATTACHMENTDETAIL AD WITH (NOLOCK)
  INNER JOIN MESSAGEATTACHMENT MA ON MA.ATTACHMENTID = AD.ATTACHMENTID
  INNER JOIN ATTACHMENT ATMT ON ATMT.ATTACHMENTID = AD.ATTACHMENTID
  INNER JOIN MESSAGE MSG ON MSG.ID = MA.MESSAGEID
  INNER JOIN MESSAGEDETAIL MD ON MD.MESSAGEID = MA.MESSAGEID
  INNER JOIN [USER] U ON U.ID = MD.USERID
  LEFT OUTER JOIN XmlExtractionMapping XM ON MA.MESSAGEID = XM.MessageId
    WHERE AD.FILEBOXTOKEN IS NULL 
    AND (XM.XMLEXTRACTIONDATE IS NOT NULL OR 
         (MSG.MESSAGESOURCEID = 1 AND MD.FolderId <> -4))
    AND AD.ISINLINE = 'FALSE'
share|improve this question
6  
A couple of comments/questions: (1) why use magic pixie-dust turbo button NOLOCK on only one table? Have you considered using RCSI instead of voodoo? (2) why are you not using proper schema prefixes? For the former, search the web for NOLOCK - you will see all kinds of reasons why you shouldn't be using it. For the latter, see sqlblog.com/blogs/aaron_bertrand/archive/2009/10/11/… –  Aaron Bertrand Nov 27 '12 at 20:56
    
RCSI? please explain... –  kacalapy Nov 27 '12 at 21:06
2  
RCSI = Read Committed Snapshot Isolation. See stackoverflow.com/questions/816650/… –  Aaron Bertrand Nov 27 '12 at 21:07
    
Without an ORDER BY clause, SQL Server (and any other RDBMS) does whatever it feels like. –  Nick Chammas Nov 28 '12 at 22:28
add comment

1 Answer

up vote 15 down vote accepted

Since you haven't told SQL Server how to order the results, it is free to do so in whatever is the most efficient. In this way it will depend on what is the cheapest to sort, and the columns you select will drive that because it in turn depends on the cheapest index(es) to use to get at the information requested by the query. This can change from execution to execution not just by changing the query text but also by things like data changes, statistics updates, freeproccache, service packs, hotfixes, etc. This is especially volatile with so many tables involved if the underlying data is changing rapidly, as it tends to do in OLTP applications.

If you want a predictable order, why don't you use ORDER BY on the query? As written, the SQL specifies that any 900 rows are acceptable, in any order.

Even though, in a lot of cases, you see the same order over and over again for the same query, there is no guarantee unless you say ORDER BY <something>.

share|improve this answer
    
@SQLKiwi thanks for the edit. –  Aaron Bertrand Nov 28 '12 at 15:05
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.