Jeff Moden wrote:
Although the PK is frequently also the clustered index, it's not necessarily so. The keys of non-clustered indexes include the keys of the clustered index, which isn't necessarily the column(s) with the PK constraint on it/them.
I believe Jeff misspoke there. Actually clustered keys are only INCLUDEd in the non-clustered index, they are not part of the non-clus key unless you explicitly include them in the list of keys.
So, why are the clus keys added to all non-clus indexes? Because SQL Server needs a way to go from any non-clus index back to the matching row in the clus index. To insure it can do this, SQL adds all clus keys to the non-clus index if you haven't already added that column yourself. This insures that SQL can do a lookup from the non-clus index back to the clus index.
If a clus key column is needed in a non-clus index, I suggest you explicitly INCLUDE it. That way, if that column is removed from the clus index key later, that column will appear in the non-clus index. That is, you won't have the column just disappear from the index because that column is no longer part of the clus key and thus no longer automatically included in the index.
SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."