When To Define Clustered Index Columns On Non-Clustered Indexes

Kim Tripp explains when to include a clustered index column on a non-clustered index column’s definition:

So, when SHOULD you explictly define the clustering key columns in a nonclustered index? When they ARE needed by the query.

This sounds rather simple but if the column is USED by the query then the index MUST have the column explicitly defined. Yes, I realize that SQL Server will add it… so it’s not necessary NOW but what if things change? (this is the main point!)

One of the more common cases I could think of is multi-part clustered indexes, like on a junction table.

Related Posts

Parameter Sniffing and Multiple Indexes

Erik Darling looks at how available indexes may contribute to parameter sniffing problems: When you’re troubleshooting parameter sniffing, the plans might not be totally different. Sometimes a subtle change of index usage can really throw gas on things. It’s also a good example of how Key Lookups aren’t always a huge problem. Both plans had […]

Read More

Statistics and Multiple Single-Column Indexes

Erik Darling is fusing together queries like Dr. Frankenstein in his lab: You may have noticed that both queries get pretty bad estimates. You might even be thinking about leaving me a comment to update stats. The thing is that I created these indexes, which means they get stats built with a full scan, and […]

Read More

Categories

February 2017
MTWTFSS
« Jan Mar »
 12345
6789101112
13141516171819
20212223242526
2728