THE SITUATION
I Have a table with only one index, a Clustered index (two columns). I do a 'SELECT * FROM TABLE' and the optimizer decides a Table scan.
I get the rows kinda sorted by clustered index. I say kinda because it doesn't look randomly sorted, but it has a lot of glitches.
If I force Using the clustered index SELECT * FROM TABLE (index 1 MRU) I get exact the clustered table order.
QUESTIONS
how can the table scan result be different in order than clustered index scan if the data in a clustered table is sorted by its index?
Is the table scan in a clustered index a scan to the leaf level of the table, aren't those sorted?
Is the clustered index scan a scan to all the possible paths of the b-tree in an ordered manner?
excuse my possible lack of knowledge, I'm trying my best to undestand the underlying concepts.
HOW DID I TESTED THIS
I achived this inconsistent ordering results by testing two different clustered indexes (one with two columns and other with one column). creating and dropping the constraint and check the select statement.
after truncating the table and creating the index, the data is correctly sorted, but after dropping the index and creating a different one, that data is not perfectly sorted with a table scan. I need to force index use.
WHY IS THIS IMPORTANT
Because I want to garantee order without using an order by clause in a clustered table.
THE EXPLANATION
Clustered indexes are logically ordered but not physically ordered.
This means that a table scan if it's done in physical order will return different results than clustered index scan, which is sorted logically.
This logical-physical mapping is controlled by OAM (Object Allocation Map)