Clustered index additional storage

Posted on

Question :

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)?

Answer :

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.

Leave a Reply

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