COUNT(ID COLUMN) not using Clustered Index

  • 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

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

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • 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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • 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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • 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

  • 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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • 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

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

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