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.

Reading some books I found that clustered index not require additional disk space for storing index data, because leaf level of clustered index are data itself.

But, as clustered index is organized as B-tree structure, it require intermediate levels and root level. Where are stored those pages? are they additional index structure? How internal structure on physical level looks like (when speaking about clustered indexes)?

share|improve this question
    
I have a video here that visually explains these concepts. –  Jon Seigel Mar 25 at 15:00
    
Still isn't 100% clear for me. Did intermediate and root level use some parts of data pages or completely new pages? If they uses new pages, then that is new additionally structure, didn't? that is the part that confusing me, because in all books, main difference between clustered and non-clustered indexes is separate additional space. –  veljasije Mar 25 at 15:08
1  
The navigation levels of the indexes are on different pages than the data. I believe there's one special case where the index is small enough to fit everything on one page, but that's pretty rare. A nonclustered index is completely separate from the clustered index, and so it uses completely separate pages. –  Jon Seigel Mar 25 at 15:09

1 Answer 1

There is some space used for the upper levels of the B-tree (how much depends on how large the table is). However if you look at a Clustered index and a Non Clustered index of the same data (exact same keys and including all columns of the table in the non clustered index) the upper levels of the B-tree would look very similar (if not the same). The difference comes when storing the data. A clustered index is the data in the table where as a non clustered index makes a copy of the data in the table (resulting in double the space taken if you include every column in the table in the index, either in the keys or as included columns).

Because a non clustered index makes a copy of the data it is said to take more space than a clustered index when it comes to the data level.

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.