Christian Buettner-167247 (10/5/2010)
What you have left out here, is the fact that rows are indeed sorted in physical order accross pages.So if you have 2 records on one page, and a new record that logically belongs between them does not fit on the same page anymore, the logically last row of the existing records will be moved to a new page, and the new record will be stored on the existing page to preserve physical order.
True, but that new page is not necessarily physically located after the existing page. There is no problem whatsoever with a page setup like this (assume ID is an int clustered index)
File 1 page 200 ID 5-20
File 1 page 201 ID 86-140
File 1 page 208 ID 51-85
File 1 page 210 ID 21-50
It's fragmented (50% logical fragmentation) but it's perfectly acceptable. The pages have next and previous pointers that point to the page that's logically next or previous to maintain the index order. The pages are not necessarily physically contiguous or in order.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability