Is the Entire Clustering Key Duplicated in the Nonclustered Indexes?

Q: Is the entire clustering key duplicated in the nonclustered indexes? What if I have a wide clustering key?

A: Every column of the clustering key must be included in each and every nonclustered – but only once. And, the order of the columns in the nonclustered indexes does not have to match those of the clustered. So, the simple answer is yes. If you have a wide key – then you may make your nonclustered indexes unnecessarily wide. However, if a column that’s in the clustering key is already defined as a column of the nonclustered index then it is not duplicated twice. Take these examples:

Clustered index definition:

CREATE UNIQUE CLUSTERED INDEX CLInd ON T1 (col6, co4, col8)

Nonclustered index definition:

CREATE UNIQUE NONCLUSTERED INDEX NC1 ON T1 (col5, col4, col12)

For this nonclustered index, SQL Server will append ONLY columns col6 and col8 as col4 is already a member. And, because NC1 is unique, these columns will only need to be appended in the leaf level of the nonclustered index. To be more specific, the leaf and the non-leaf levels (the B+ tree that’s used for navigation) can be different. SQL Server only needs what is necessary for navigation in the tree. If the nonclustered index is unique, then only the columns defined by the nonclustered index must be in the tree. However, if NC1 had been non-unique then the clustering key would have had to have been added to the tree for navigation. To be more clear, I’ll break this down by “leaf level” and non-leaf levels” here:

Nonclustered index definition:

CREATE UNIQUE NONCLUSTERED INDEX NC1 ON T1 (col5, col4, col12)Non-leaf level(s): col5, col4, col12  Leaf level: col5, col4, col12, col6, col8CREATE NONCLUSTERED INDEX NC2 ON T1 (col5, col4, col12)Non-leaf level(s): col5, col4, col12, col6, col8  Leaf level: col5, col4, col12, col6, col8

To help you see the exact structure of your indexes (for all levels), I have created a version of sp_helpindex that does this here: http://www.sqlskills.com/BLOGS/KIMBERLY/post/A-new-and-improved-sp_helpindex-(jokingly-sp_helpindex8).aspx. In addition to giving the information usually produced by sp_helpindex it also produces the index_id, the included columns, the filter and finally the exact columns in the leaf level as well as those in the non-leaf levels.