• sjimmo (6/25/2010)


    Not on a simple select *

    Are you saying that the IAM page chain will not be used to do an unordered page traversal if you specify a "simple select *" but it will be if you specify some subset of columns? I never heard that before and it sounds a bit unlikely. Perhaps it is true but I don't think that behaviour is documented. Or have you seen it documented somewhere?

    What is for sure is that even for your SELECT * query the clustered index scan will be identified as unordered in the execution plan ("unordered = True"), therefore in principle I understand the "official" line to be that SQL Server is free to perform an unordered scan. Even if it does not do so today, it could do if there is an engine change in some hotfix or service pack. Therefore to say that the scan will be ordered without giving any warning or qualification is perhaps a bit reckless, especially if we are talking about undocumented behaviour. I just think it's worth qualifying your claim a bit more so that anyone reading this can make up his own mind on whether he wants to take that risk.