• Ok Robert, you made me dig into this one deeper. We see the misconceptions about physical vs. logical ordering of pages in a clustered index, but there also seems to be misconceptions about physical ordering of rows within pages.

    According to BOL

    http://msdn.microsoft.com/en-us/library/ms190639.aspx

    'A clustered index is particularly efficient on columns that are often searched for ranges of values. After the row with the first value is found using the clustered index, rows with subsequent indexed values are guaranteed to be physically adjacent.'

    Digging deeper, though, there is the concept of the row offset table _aka_ slot array at the end of each page. Using the indirection of this table, index order of rows within the page is maintained by the order of the row entries in this table. Entry 0 has the offset of the 1st ordered row, entry 1->2nd ordered row. The values of the offsets in this table don't have to be in order, they just have to point to the correct row start location in the page. As rows are inserted/deleted/updated, the offsets in this table are updated as needed to maintain the sort order.

    Ordered Row - Offset

    4 (0x4) - 177 (0xb1)

    3 (0x3) - 150 (0x96)

    2 (0x2) - 123 (0x7b)

    1 (0x1) - 96 (0x60) offset of 2nd row in sort order

    0 (0x0) - 204 (0xcc) offset of 1st row in sort order (a newly inserted record, 1st in the offset table, but physically at end)

    Paul S. Randal explains this in depth on his site http://www.sqlskills.com

    http://www.sqlskills.com/BLOGS/PAUL/post/Inside-the-Storage-Engine-Proof-that-records-are-not-always-physically-stored-in-index-key-order.aspx