COUNT(ID COLUMN) not using Clustered Index

Question

COUNT(ID COLUMN) not using Clustered Index

Lord Slaagh

SSCertifiable

Points: 5724
More actions
April 8, 2021 at 12:38 am
Go to Answer
#3866596

Hello SSC,
Ok, so I have a table that has a 1 Clustered and 1 Non-Clustered Index. PK Clustered Index is simple, just CustomerID, unique, straight forward. Non-Clustered is a combination of 3 columns that do not include CustomerID. When I run... SELECT COUNT(CustomerID), the execution plan shows that it is hitting the Non Clustered Index. When I run... SELECT CustomerID, execution plan shows that it is hitting the correct Clustered Index.
Why would this happen? Shouldn't a simple SELECT COUNT(CustomerID) hit the Clustered Index and not the Non Clustered? Plus the Non Clustered doesn't even have CustomerID as a part of the Index. Very strange behavior.
Thank you all in advance and I hope you are all staying safe and healthy!
Dave
Everyone has a plan until they get punched in the mouth. --Mike Tyson
Jeff Moden

SSC Guru

Points: 1003863
More actions
April 8, 2021 at 1:57 am
Answer
#3866619

Although the PK is frequently also the clustered index, it's not necessarily so. The keys of non-clustered indexes include the keys of the clustered index, which isn't necessarily the column(s) with the PK constraint on it/them.
Same effect that Sergiy pointed out, though, especially if the CustomerID column in your table is both the PK and the clustered index key.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110206 More actions · Answer 1

Clustered index contains all the data pages on its leaf level. Therefore scanning a clustered index on any table will be more expensive operation than scanning any of non-clustered indexes on the same table.

P.S. Any index contains a PK reference on its leaf pages. Therefore the CustomerId as a PK (clustered or non-clustered) is present in any other index created on this table. Even if it's not included into the index definition.

_____________
Code for TallyGenerator

ScottPletcher SSC Guru Points: 100947 More actions · Answer 2

Jeff Moden wrote:

Although the PK is frequently also the clustered index, it's not necessarily so. The keys of non-clustered indexes include the keys of the clustered index, which isn't necessarily the column(s) with the PK constraint on it/them.

[Emphasis added]

I believe Jeff misspoke there. Actually clustered keys are only INCLUDEd in the non-clustered index, they are not part of the non-clus key unless you explicitly include them in the list of keys.

So, why are the clus keys added to all non-clus indexes? Because SQL Server needs a way to go from any non-clus index back to the matching row in the clus index. To insure it can do this, SQL adds all clus keys to the non-clus index if you haven't already added that column yourself. This insures that SQL can do a lookup from the non-clus index back to the clus index.

If a clus key column is needed in a non-clus index, I suggest you explicitly INCLUDE it. That way, if that column is removed from the clus index key later, that column will appear in the non-clus index. That is, you won't have the column just disappear from the index because that column is no longer part of the clus key and thus no longer automatically included in the index.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

ScottPletcher SSC Guru Points: 100947 More actions · Answer 3

Lord Slaagh wrote:

Hello SSC,
... When I run... SELECT CustomerID, execution plan shows that it is hitting the correct Clustered Index.
Dave

Hmm, that doesn't seem right. You must have added other columns to the SELECT or perhaps specified ORDER BY CustomerID. Otherwise, SQL would use the non-clus index as long as it covers the query (contains all columns referenced in the query).

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

ScottPletcher wrote:

Jeff Moden wrote:
Although the PK is frequently also the clustered index, it's not necessarily so. The keys of non-clustered indexes include the keys of the clustered index, which isn't necessarily the column(s) with the PK constraint on it/them.
[Emphasis added]
I believe Jeff misspoke there. Actually clustered keys are only INCLUDEd in the non-clustered index, they are not part of the non-clus key unless you explicitly include them in the list of keys.

I indeed mispoke. Thanks for the correction, Scott. You're correct. They're only added to the leaf level unless explicitly add as a key column in the non-clustered index.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lord Slaagh SSCertifiable Points: 5724 More actions · Answer 5

Thank you all very much for your responses! Mystery solved 🙂

Stay safe folks!

Everyone has a plan until they get punched in the mouth. --Mike Tyson

ScottPletcher SSC Guru Points: 100947 More actions · Answer 6

Hmm, the first part of the Answer you chose is correct. But keep in mind that the second part, the "P.S.", is not.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Lord Slaagh SSCertifiable Points: 5724 More actions · Answer 7

Ok, good point, we want to make sure that other developers have correct info. I gave the answer to Jeff. Please let me know if it's accurate.

This answers my question. Thank you.

Stay safe!

Everyone has a plan until they get punched in the mouth. --Mike Tyson