• PRR.DB (7/22/2013)


    If table doesn't have clustered index , there is no guarantee for order , if table having clustered index then result will display clustered index column order.

    Is my answer correct or not? Can any one explain on it?

    A very popular misconception. For simple, small queries on small tables, tests will suggest that it's true. But it isn't.

    First - order is not guaranteed. This is documented (as far as I know). That alone makes the answer to this question unambiguous.

    Second, on large tables, you can have other things influencing the order. For instance, parallel execution. I have seen results that were obviously caused by this - e.g. first rows 6001-8000 (in order), then 10001-12000 (in order), then 1-6000 (in order), etc - obviously, several parallel tasks each processing 2000 rows but finishing out of order. If you add ORDER BY, SQL Server will ensure that they are returned in order. Without ORDER BY, SQL Server won't bother - why include the overhead if you don;t care about order?

    Third, on enterprise edition SQL Server can perform an "advanced scan" (see http://msdn.microsoft.com/en-us/library/ms191475%28v=sql.105%29.aspx), where a scan first starts fetching rows from another already in-progress, then restarts the scan from the start to retrieve the rows it missed.

    And fourth - Google "sqlblog beatles vs stones" for a blog post I wrote many years ago that shows another very common cause of order being different from clustered index in a (hopefully) funny way.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/