SQL Server – How are data pages stored when using a clustered index

Posted on

Question :

I recently heard that the data pages in a clustered index aren’t stored contiguously. Is this true?

Perhaps data pages are normally stored contiguously with some exceptions to the rule? Or perhaps I heard wrong and data page are always stored contiguously.

Many thanks.

Answer :

Data pages are stored contiguously when the index is created and when the index is rebuilt. Otherwise, SQL Server will attempt to keep the pages in physical order. That not being possible, logical order is attempted. You can get gaps in a table because of other writes that are happening in the database. SQL Server uses B+ trees for indexes – the leaf (data) level of the index contains the data but is also a doubly linked list that contains pointers to the next and previous pages. This makes it less important that the table be 100% physically contiguous.

Leave a Reply

Your email address will not be published. Required fields are marked *