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.

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

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.

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.

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

• wheel over the entire fridge

• bring over the case

• bring over a single beer

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.

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

StackExchange Database Administrators Q#217653, answer score: 10