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

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;

up vote
0
down vote

favorite

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.

asked 37 mins ago

Tipx

1031

New contributor

add a commentÂ |Â

up vote
0
down vote

favorite

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.

asked 37 mins ago

Tipx

1031

New contributor

add a commentÂ |Â

up vote
0
down vote

favorite

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.

asked 37 mins ago

Tipx

1031

New contributor

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.

sql-server-2012 index execution-plan

asked 37 mins ago

Tipx

1031

New contributor

asked 37 mins ago

Tipx

1031

New contributor

asked 37 mins ago

Tipx

1031

New contributor

asked 37 mins ago

Tipx

1031

asked 37 mins ago

Tipx

1031

New contributor

Tipx is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a commentÂ |Â

2 Answers
2

active

oldest

votes

up vote
2
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.

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.

answered 20 mins ago

RDFozz

9,45131029

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
8 mins ago

1

@Tipx I don't believe that uniqueness has absolutely anything to do with this case.
â€“Â Aaron Bertrandâ™¦
4 mins ago

@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. 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
17 secs ago

add a commentÂ |Â

up vote
2
down vote

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

answered 23 mins ago

Aaron Bertrandâ™¦

145k19276463

add a commentÂ |Â

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: false,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);

);

Tipx is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f217653%2fselect-is-using-a-non-pk-index-instead-of-the-pk%23new-answer', 'question_page');

);

Post as a guest

Name

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

up vote
2
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.

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

answered 20 mins ago

RDFozz

9,45131029

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
8 mins ago

1

@Tipx I don't believe that uniqueness has absolutely anything to do with this case.
â€“Â Aaron Bertrandâ™¦
4 mins ago

@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. 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
17 secs ago

add a commentÂ |Â

up vote
2
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.

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

answered 20 mins ago

RDFozz

9,45131029

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
8 mins ago

1

@Tipx I don't believe that uniqueness has absolutely anything to do with this case.
â€“Â Aaron Bertrandâ™¦
4 mins ago

@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. 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
17 secs ago

add a commentÂ |Â

up vote
2
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.

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

answered 20 mins ago

RDFozz

9,45131029

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.

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

answered 20 mins ago

RDFozz

9,45131029

answered 20 mins ago

RDFozz

9,45131029

answered 20 mins ago

RDFozz

9,45131029

answered 20 mins ago

RDFozz

9,45131029

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
8 mins ago

1

@Tipx I don't believe that uniqueness has absolutely anything to do with this case.
â€“Â Aaron Bertrandâ™¦
4 mins ago

@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. 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
17 secs ago

add a commentÂ |Â

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
8 mins ago

1

@Tipx I don't believe that uniqueness has absolutely anything to do with this case.
â€“Â Aaron Bertrandâ™¦
4 mins ago

@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. 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
17 secs ago

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
8 mins ago

@Tipx I don't believe that uniqueness has absolutely anything to do with this case.
â€“Â Aaron Bertrandâ™¦
4 mins ago

@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. 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
17 secs ago

add a commentÂ |Â

up vote
2
down vote

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

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

answered 23 mins ago

Aaron Bertrandâ™¦

145k19276463

add a commentÂ |Â

up vote
2
down vote

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

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

answered 23 mins ago

Aaron Bertrandâ™¦

145k19276463

add a commentÂ |Â

up vote
2
down vote

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

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

answered 23 mins ago

Aaron Bertrandâ™¦

145k19276463

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

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

answered 23 mins ago

Aaron Bertrandâ™¦

145k19276463

answered 23 mins ago

Aaron Bertrandâ™¦

145k19276463

answered 23 mins ago

Aaron Bertrandâ™¦

145k19276463

answered 23 mins ago

Aaron Bertrandâ™¦

145k19276463

add a commentÂ |Â

Tipx is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Tipx is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Post as a guest

Name

Search This Blog

Iyfjky