13

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.

  • 1
    Books Online has come pretty good diagrams that show how the pages are physically stored and how the B tree is laid out. – mrdenny Jan 5 '11 at 21:56
11

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.