amenjonathan (6/22/2011)
I have heard from more than one person that all columns in the clustered index also show up in each non-clustered index on that same table, which is why you should keep your clustered index columns to the bare minimum you can get away with.Is this true?
Yes and no*.
The clustered index (plus a uniqueifier if necessary) is the row pointer for non-clustered indexes (See http://msdn.microsoft.com/en-us/library/ms177484.aspx) So a smaller clustered index will mean smaller non-clustered indexes, all else being equal.
BUTif you have few non-clustered indexes, and you have a column that is almost always included in criteria for queries, you can gain performance if that's part of the clustered index at a small cost of space in the non-clustered indexes (indeed, you'd likely be moving that column from its own non-clustered index to the clustered index, so you could actually save space.)
Note: When I speak about changing the clustered index, I'm speaking of conceptually changing it preferably before any data is in the table, and definitely before moving it to production. Changing a clustered index in production can be difficult, to say the least. This is one of those areas where planning is key.
* This is ALWAYS the answer to any Yes or No question. Always.