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.