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
The are no problems, only solutions. --John Lennon
April 8, 2021 at 12:55 am
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
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
Change is inevitable... Change for the better is not.
April 8, 2021 at 2:24 am
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 8, 2021 at 2:26 am
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 8, 2021 at 2:39 am
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
Change is inevitable... Change for the better is not.
April 8, 2021 at 12:54 pm
Thank you all very much for your responses! Mystery solved 🙂
Stay safe folks!
The are no problems, only solutions. --John Lennon
April 8, 2021 at 2:39 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 8, 2021 at 2:44 pm
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!
The are no problems, only solutions. --John Lennon
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply