Isn’t the Clustering Key Redundant?

Q: When you use the Database Tuning Advisor (DTA) or the missing index dynamic management views (DMVs), these tools sometimes suggest nonclustered indexes that include the clustering key columns in the nonclustered index definition. I’ve heard that SQL Server includes the clustering key automatically— so isn’t it unnecessary for me to include it as well, and if I do, will SQL Server add the clustering key columns twice?

A: When tools such as DTA or the missing index DMVs come up with index recommendations, they only look at what’s required of the index to support the query’s execution. If a query requests the column (if it’s a single column) that is the clustering key, the tool is likely to show the clustering key somewhere in the nonclustered index. It’s not a problem to explicitly list the clustering key column in the definition of a nonclustered index. In fact, depending on the query being tuned, the clustering key might be required as a second or third column in a much wider index, and performance might be improved because of the clustering key’s position in the nonclustered index. So, there can be a benefit in terms of ordering.

However, if the clustering key is at the end of the index key definition or in an INCLUDE clause, then it might not matter because SQL Server adds it automatically. However, exactly how SQL Server adds it, whether it’s part of the ordered structure of the index (the key) or it’s only in the leaf level of the index (using INCLUDE), depends on whether the nonclustered index is defined as a unique index.

When a nonclustered index is unique, the clustering key is effectively the same as an included column. However, when the nonclustered index is nonunique, the clustering key is actually added to the key portion of the index for navigation.

For example, if you created a clustered index on EmployeeID and then created a unique nonclustered index on SocialSecurityNumber, the nonclustered index would look like the following in the nonclustered tree (used for navigation):

SocialSecurityNumber

In the nonclustered leaf level (used for data retrieval), the index would appear as

SocialSecurityNumber, EmployeeID

If the SocialSecurityNumber index wasn’t created as a unique nonclustered index, then the index would look like the following in the nonclustered tree (used for navigation):

SocialSecurityNumber, EmployeeID

In the nonclustered leaf level (used for data retrieval), the index would appear as

SocialSecurityNumber, EmployeeID

      What’s important is that you’re better able to determine when indexes are redundant and when there are differences. Using only sp_helpindex or the UI to examine the list of table columns in an index can make it difficult because they don’t directly call out these differences. If you want to see exactly what’s in each level of the index, check out my rewritten version of sp_helpindex in my blog post "A new and improved sp_helpindex." For more information about the clustering key, check out "Where Does that Clustering Key Go?"