SELECT is using a non-PK Index instead of the PK

In the [dbo].[Programs] table, the column [Id] is the PrimaryKey (not par of a composite key). There are also quite a few other indexes on that table.

[dbo].[Programs]

[Id]

When I'm running this simple query, SELECT [Id] FROM [dbo].[Programs], here is the execution plan:

SELECT [Id] FROM [dbo].[Programs]

My question is: Why is it not just using the PK index instead?

Performance is not an issue as that table has 23 rows, but I just find it odd, and I want to understand why SqlServer is right, and why I'm wrong to assumed it would be better.

2 Answers
2

The query optimizer will look to see what's the quickest way (as far as it can tell) to get you your data.

It might be interesting to check the execution plan of the same query, with ORDER BY [Id] added.

ORDER BY [Id]

I'll assume that Id is not only the primary key, but that its index is the clustered index on the table. What that means is that, to go through the index and pick out the Id values, it'll have to read the entire table (because the clustered index is really the full table, sorted by the index key).

Id

Id

I'll also assume that IX_SupplyProgramId has a limited number of other columns included. Note that all indexes will have the value of the clustered index, as that's how they connect the index back to the actual row. And, every value of Id will be included

IX_SupplyProgramId

Id

So, the amount of data that has to be read if it reads the clustered index is probably larger than the amount to read in the other index. Read are one of the most expensive operations the query engine does, so reducing reads is a good thing.

So, it uses the smaller index to get the Id values, instead of the clustered index.

Id

[SupplyProgramId]

[Id]

[Id]

SupplyProgramId

Id

Id

SupplyProgramId

Id

It has nothing to do with primary key, except that the primary key is typically clustered.

In your case SQL Server is not using the clustered index because, by definition, the clustered index includes all of the columns in the table. Since you only want Id, it is using a skinnier index that satisfies your query simply because it's less work to do so, and even if your table only has a single column, it's still going to choose the non-clustered index.

Id

If I ask you to get me a beer from the fridge, your choices are to:

In your case, 1. is using the clustered index, 2. is using some wide index, and 3. is using an index that only contains Id.

Id

A clustered index is simply not always the best choice for an operation, much like a Ferrari is not the car you always want to use for a task (racing someone vs. towing a yacht, for example).

Thanks for contributing an answer to Database Administrators Stack Exchange!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.