Hugo Kornelis (2/17/2012)
Result of order depend on method to get result
1. Index seek
in this case relation engine use ROOT PAGE to start search. And result will be sort
Little trick for force seek use hint (ForceSEEK) and add search predicate in WHERE clause
This is only partially true.
Since this is undocumented, there is no way to guarantee this even if it is true today. And while it is true today under most circumstances, it isn't always true. I don't have the time to repro this now, but I think that if the table is sufficiently large, then both partitioning the table and getting a parallel execution plan could change the order of rows.
2. Index scan
If index scan is used then relation engine get leaf level pages in order that is stored in Index Allocation Map
This order may be any
and depend on free pages allocation at the moment of creation index
And this is definitely not correct. An IAM scan will only be used if the following conditions are met:
1) The query optimizer must specify an unordered scan - that is, the query optimizer is not interested in the order of the rows after the scan operator. This will not be the case if the optimizer has to guarantee some order (for an ORDER BY, GROUP BY, merge join operator, or other operator or query ingredient that requires ordered rows) and can spare a sort operator by scanning rows in order.
2) The query must be processed with either no locks at all or locks at the table level.
Since SQL Server defaults to row-level locks, IAM scans are very rare in practice; most index scans are still in index order - though, again, not guaranteed!
And also, on Enterprise Edition, Data Center Edition, and Developer Edition, the engine has the ability to do "piggy back" scans - if a scan is already in progress when a new (unordered) scan is required, it will start consuming rows from that scan already in process and then restart from the start until where it started piggy-backing.
Finally, please remember that none of the above behaviours are documented as "future proof". All of this may change - not only in a future version, but also in a service pack, CU or even in a hotfix. The only way to get results in an order you can depend upon is to use that ORDER BY clause. (And the extra benefit is that it also documents your code better). In cases where the normal access method would result in that order anyway, it won't even cost you anything extra, as the optimizer will not add an unneccessary sort step.
Thank you for comment!
First of all i agree, the index scan trough IAM page(s) occurs when table lock level is applied. But I want to add NOLOCK/Dirty read also cause the same situation
It force read leaf level page not in order that they are linked. I just check it via debug each page and links - it is still truth.
But if I use fake predicate in where clause on any column that is in index, it forces engine use scan via root page and record appear in sorted order. It still valid when table is partitioned and plan is parallel. Just check on table with 6 section and six million of rows.
I did not find any examples refute this.
I know and remember - it is not documented, and there is not guaranties, but i think it very important to understand why records my appear in another order and how it order does. The key concept - SQL uses IAM pages to begin scan, not root page.
PS Sorry for my English level
MS SQL 2008 MCITP x 3
MS SQL 2012 MCSE x 2