Is it bad to have a non-clustered index that contains the primary key from the clustered index?

Question

If you have a table with a clustered index on the Primary Key (int), is it redundant and bad to have one (ore more) non-clustered indexes that include that primary key column as one of the columns in the non-clustered index?

Answer 1

Actually there could be valid reasons to create a non-clustered index identical with the clustered one. The reason is that clustered indexes carry the baggage of the row data and this can make very poor row density. Ie. you can have 2-3 rows per page due to wide fields that are not in the clustered key, but the clustered index key is only, say, 20 bytes. Having a non-clustered index on exactly the same key(s) and order as the clustered index would give a density of 2-3 hundreds of keys per page. A lot of aggregate queries typical for an OLAP/BI workload can be answered more efficiently by the non-clustered index, simply because it reduces the I/O by hundreds of times.

As for non-clustered indexes that contain parts of the clustered key, or even the same keys but in different order, then all bets are off as they obviously could be used for a multitude of queries.

So the answer to your question is: It Depends.

For a more precise answer you'll have to share the exact schema of your table(s) and the exact queries involved.

Answer 2

Yes, it is typically not necessary, because the columns of the clustered index are already added to each index entry in the non-clustered index.

Why? The value of the clustered key is what really allow SQL Server to "find" a row of data - it's the "pointer" to the actual data - so obviuosly, it has to be stored in the non-clustered index. If you have looked up "Smith, John" and you need to know more about this person, you need to go to the actual data --> and that is done by including the value of the clustering key in the index node of the non-clustered index.

That clustered key value is already there, and thus typically it's redundant and unnecessary to add that value again, explicitly, to your non-clustered index. It's bad in that it just simply wastes space without giving you any benefit.