Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
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

1 Answer 1

up vote 9 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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