• Whether you think SQL Server should be preserving physical order of the data or not doesn't matter. The facts of the case is that it only preserves logical ordering of the data. If a new record is added to an existing page, SQL Server puts it in the first spot with enough space whether that is in the correct physical order or not.

    Additionally, if SQL Server needs to split the page to insert a record, it does not split the page at the point where it needs to insert the record. It splits it approximately in half and then inserts the record into the page in which it logically fits. Again, this would be at the first slot big enough for it fit the record in and may even be the last record in the page physically even though logicially is in the middle or beginning or anywhere else.

    Also, SQL Server NEVER reads in records from disk. It reads in pages, the location of the record on the page does NOT affect the number disk IO's required to read in the page. Reading in a single page of data is 1 IO whether it is physically sorted by the data or not. So maintaining the physical order of the data in memory would increase the memory workload for the server wihtout getting any benefit.


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]