• I am not satisfied with the answer, indeed I find it very misleading.

    As data is inserted or the clustering key values are updated, SQL Server only preserves logical ordering of the data. It would require far too many resources to preserve physical ordering of the data.

    What you are referring to is the physical order within a page.

    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.

    Here is some more background information which I think is necessary to really understand the discussed concepts:

    Clustered indexes are not a good choice for:

    Columns that undergo frequent changes

    This results in the entire row moving (because SQL Server must keep the data values of a row in physical order). This is an important consideration in high-volume transaction processing systems where data tends to be volatile.

    http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/26/647005.aspx

    It's a very common misconception that records within a page are always stored in logical order.

    http://www.sqlskills.com/blogs/paul/2007/10/03/InsideTheStorageEngineAnatomyOfAPage.aspx

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

    (Again - note that this is referring to order within a page only)

    Best Regards,

    Chris Büttner