hakan.winther (8/8/2011)
The correct answer should be "it depends". As already stated, there are no guarantees even it seem to work in some cases, but with larger tables, parallell plans, many users and enterprise edition (advanced read ahead) you can't rely on the order of the result.ALWAYS use the ORDER BY clause if you depend on the order.
I think your opinion is little wrong.
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
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
Please get DBCC IND, DBCC PAGE into your hands and verify this.
MS SQL 2008 MCITP x 3
MS SQL 2012 MCSE x 2