Tweeted twitter.com/#!/StackDBAs/status/273547300935200768
3 Because horizontal scrollbars suck.
source | link

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 andAND MD.FolderId <> -4))
            AND AD.ISINLINE = 'FALSE'

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'

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'
2 deleted 12 characters in body; edited title
source | link

how How does sql serverSQL Server order results when joins are used?

howHow does sqlSQL Server figure out the order of the records in the result set of query execution?

I am trying to make heads and talesor tails of it but find myself scratching my head when i. When I change the fields iI am selecting the order also changes. when iWhen I execute the below sqlSQL with a select * iSELECT * I get the same records but in a much different order.

what gives?

 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'

how does sql server order results when joins are used?

how does sql figure out the order of the records in the result set of query execution?

I am trying to make heads and tales 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 much different order.

what gives?

 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'

How does SQL Server order results when joins are used?

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'
1
source | link

how does sql server order results when joins are used?

how does sql figure out the order of the records in the result set of query execution?

I am trying to make heads and tales 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 much different order.

what gives?

 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'