Why is read performance using a covering index better than using a primary key?

Question

I have this table with just Id and Value columns, and this is joined to to get the value in a common query. The Id is the (clustered) primary key and the value column is read in the select. If I add a new non-clustered index on Id and include the Value column, SQL sentry gives the index a better score and that is reflected in a decrease in reads and total time when rerunning the query.

As I understand it, the primary key is essentially just an index which includes all of the data from the other columns but doesn't index by them. Could someone please explain why this has a performance difference compared to creating a non-clustered index, including the other fields? If anything, I'd expect it to perform better as it's clustered so it is ordered by the Id. I must be misunderstanding something here so any input would be appreciated.

SQL Sentry index analysis

---

Edit: When trying to find the minimum reproducable example I couldn't replicate this, so I went back to the original query and it aligns with what a lot of the responses are saying. On one hand SQL Sentry's scoring algorithm prioritises the non-clustered index and on the other, with further time and use they produce the same performance output.

Answer 1

I have this table with just Id and Value columns . . . The Id is the (clustered) primary key and the value column is read in the select. If I add a new non-clustered index on Id and include the Value column,

Those two indexes will be identical: a BTree on Id, with all (Id,Value) tuples in Id-order on the leaf pages. IE the non-clustered index will be an exact copy of the clustered index.

The new nonclustered index will, however, initially be defragmented and might occupy fewer pages. Over time they will be the same.

• ypercubeᵀᴹ: Will it make any difference in terms of size if the non-clustered key is not UNIQUE (while the clustered is, since it is the PK)?
• David Browne - Microsoft: All non-clustered indexes in SQL Server are actully stored as unique BTree indexes. If you don't declare it as unique, the row identifier (here the clustered index key) will be added to the index key columns. Since this NC index already has all the clustered index key columns, it will be physically identical to the clustered index.
• ypercubeᵀᴹ: Ah yes, forgot about that! thnx.