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'
NOLOCK
- you will see all kinds of reasons why you shouldn't be using it. For the latter, see sqlblog.org/2019/09/12/…ORDER BY
clause, SQL Server (and any other RDBMS) does whatever it feels like.