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
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).