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:

ExecutionPlan

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.

up vote 8 down vote accepted

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.

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

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

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.

  • Oh, so because [SupplyProgramId] is not nullable, and unique, it know it will contain every [Id]strictly once... damn he's wise! I tried using the sort on [Id], and it does indeed use the PK Index at that point. So when it reads the Clustered PK Index, since it contains the data too, it's heavier to read hence why it's not doing it... nice! Thanks sir! – Tipx Sep 14 at 18:56
  • 2
    @Tipx I don't believe that uniqueness has absolutely anything to do with this case. – Aaron Bertrand Sep 14 at 19:00
  • @AaronBertrand If it wasn't "Non-Nullable and Unique", it couldn't use the other IX because it wouldn't guarantee that every [Id] would be referenced strictly one. Some [Id] could have no references to them, making the result set incomplete. Some [Id]s could be referenced multiple times, adding the need to do a distinct on the result. – Tipx Sep 14 at 19:04
  • 2
    @Tipx - Aaron is correct - even if there were 20-30 rows with each unique value of SupplyProgramId, each row would have to appear in the index. Since each row appears in the index, each Id appears in the index. And, note that Id is unique (as the primary key) - repeats of the SupplyProgramId don't mean repeats of the Id. – RDFozz Sep 14 at 19:06
  • @RDFozz Alright, I guess I don't understand something then. I don't want to spill too much in comments (as some would direct us to the chat), so I'll let it go! – Tipx Sep 14 at 19:11

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:

  1. wheel over the entire fridge
  2. bring over the case
  3. 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).

Your Answer

 
discard

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.