GilaMonster (7/22/2013)
wolfkillj (7/22/2013)
So strictly speaking, the data for rows in a clustered index will not always be physically stored in key order, although the data can only be out of order within a page.The pages can be out of physical order too, it's called fragmentation (logical order of pages doesn't match physical order of pages)
Indeed. The leaf node pages of a clustered index are linked in a doubly-linked list according to the clustering key, but the physical location of the pages allocated to the index may not follow that order.
Would it be fair to say that this discussion yields at least two general principles?
1. The organization of rows in pages, leaf node pages in doubly-linked lists, etc., and the allocation and management of pages for such purposes are functions of SQL Server's storage engine.
2. A developer should write queries that return the desired results using the documented features of the T-SQL language and SQL Server's relational engine, not counting on the workings of the storage engine to affect the results in any way.
Jason Wolfkill