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, col8

CREATE 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.

Discuss this Blog Entry 2

on Sep 10, 2010
Your article happended to answer a question I wondered for a long time. Many thanks.
Kimberly L. Tripp (not verified)
on Oct 12, 2010
Ha! You're not the only one! And, the script will help you to better recognize when indexes might be redundant.

Enjoy!
Kimberly


Please or Register to post comments.

What's SQL Server Questions Answered?

Practical tips and answers to many of your questions about SQL Server including database management and performance issues.

Contributors

Paul S. Randal

Paul Randal worked on Microsoft's SQL Server team for nine years in development and management roles, writing many of the DBCC commands. Randal was ultimately responsible for SQL Server 2008'...

Kimberly L. Tripp

Kimberly L. Tripp has been working with SQL Server since 1990, and she’s worked as a consultant, trainer, speaker, and writer specializing in core SQL Server performance tuning and availability...
Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×