Adding a non-clustered, covering index, on a primary key, when clustered index exists

Question

I went through the related articles, suggested by SO, but didn't really find all the answers, so I'll try to be specific here.

Presume we have a (MSSQL) table, with a simple ID (int) primary key, with a clustered index only on it. Let's presume that this table has 20 columns.

Now, I have a query, that queries the table only by that ID and needs always only the 2 of those 20 columns.

Is it beneficial to create a new, non-clustered index, on the same primary key column, and INCLUDE the 2 needed columns as non-keys?

Something like this (example stolen from Clustered vs covering index):

 CREATE NONCLUSTERED INDEX MyIndex
 ON MyTable(ID)
 INCLUDE (Name, Address)

I think it is, and I believe it is only due to the fact that this new index would be smaller in size (because its leaves are much smaller (having 2 instead of 20 columns)).

But, somehow, to me is illogical that the DB indexes are organized in such "poor" way that when the DB wants to read the index and its data, it actually reads both the indexes and the data together, so then it makes a difference how big the leaves are.

Or, maybe the the indexes are actually kept "separated" so the search of the key is always equally fast, but the slowdown comes from reading out the proper leaf, after we know the actual key? If it is so, are the benefits of such NC indexes then exaggerated, since it's about 1 page read?

Thanks for clarifying this topic to me (and hopefully some others as well).

• Paul White: Closely related Q & A: dba.stackexchange.com/q/147011/1192
• Charlieface: Could do, but if you do it's probably sensible to declare it as UNIQUE

Answer 1

Like nearly all things SQL Server, "it depends". One should consider the entire workload and, if performance is particularly important, race your horses.

A redundant non-clustered index with the same key column(s) as the clustered index is rarely appropriate. An exception might be a big table where the non-clustered index covers nearly all the queries in the workload and the non-clustered index is much smaller than the in-row data pages of the clustered index. The redundant index might improve buffer efficiency in this scenario by avoiding keeping the unneeded data in memory.