Is there any reason to create a non clustered index that includes as a key column the PK key column c1 like this:
create nonclustered index x2 on t1 (c2, c1)
Or is this index entirely equivalent, given that c1 is already stored in the leaf pages?
create nonclustered index x3 on t1 (c2)
They are equivalent at the moment. Should someone at a later time change the clustered index key(s) or add another column to that index, they will no longer be equivalent.
In the x3 index, is c1 also in the root and non-leaf pages? Is it considered with respect to sequencing the non-leaf pages?
Leaf only if the nonclustered is unique, all pages if the nonclustered is not unique
Can you generalize and say the clustered key columns should never be part of the non-clustered index keys?
No, absolutely not. If the query that you're tuning is such that it needs the clustered key in the index (key or include), put it there. SQL's smart enough not to add it a second time.
If you don't explicitly specify the clustered key column, it's the last column of the index, which may not be what you want, you may need it elsewhere. It may be fine as the last column now, but later someone adds another column to the index and now the required order is wrong. Someone may at a later date change the clustered index, and now the nonclustered index is not as useful as it was.
If you need the clustered key within an index because of the query, explicitly add it. If you don't need it for the query, leave it out. Same as any other column in the table. Let SQL worry about what it's doing behind the scenes itself.
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass