How does SQL Server order results when joins are used?

Question

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'

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 https://sqlblog.org/2019/09/12/bad-habits-to-kick-avoiding-the-schema-prefix — Aaron Bertrand, Nov 27 '12 at 20:56
RCSI = Read Committed Snapshot Isolation. See http://stackoverflow.com/questions/816650/when-is-it-appropriate-to-use-nolock — 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

score 21 · Accepted Answer · edited Nov 28 '12 at 06:31

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>.

How does SQL Server order results when joins are used?

1 Answers1

Linked

Related