Is there any advantage of using a clustered key in NC index?

Question

Is there any advantage of using a clustered key in NC index?

S_Kumar_S

SSCrazy Eights

Points: 9037
More actions
December 27, 2010 at 11:57 pm

#148030

Hi
I got a table here which have a clusterd index idxC_1 on Col1. Now there is one non clustered index idxNC_1 which is on Columns (Col2,Col3,Col1). Now My understanding is that since NC index has clustring key as well in it, so the Col1 in NC is not serving a good purpose and can be removed from index definition. My Col2 and Col3 doesn't have very good granuality (Col2 is on lastname and Col3 is on Clientid which are repetitive in nature). Mostly my predicates are like:
Where Col2='some value' and Col3='Some value' and Col1='Some value' --Here clustered index is happening as cost for that is less than NC index seek
Where Col2='some value' and Col3='Some value' -- NC index seek is happening
The select clause has either Col1,Col2 or Col3 in it, so all these cols are already there in these 2 indexes.
So is it okay to drop Col1 from non clustered index? Or will it be okay to alter the NC with (Col1,Col2,Col3) so that column with high granuality (Col1) comes first which makes NC index to be seeked instead of clusterd index seek?
I wont go for second case because I dont mind if it is a clustered seek or NC seek as long as performance is same.
Any other things I should look into before dropping the column?
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

S_Kumar_S (12/27/2010)
So is it okay to drop Col1 from non clustered index?

You can, but bear in mind that should the cluster be changed at some point in the future, or another key added to that NC index, the index will no longer be as useful.

Basically, if you need the clustered index key present for a query, specify it, don't leave it to be implicitly added.

Or will it be okay to alter the NC with (Col1,Col2,Col3) so that column with high granuality (Col1) comes first which makes NC index to be seeked instead of clusterd index seek?

Do that and the second query that you specified will no longer be able to seek on that index.

Any other things I should look into before dropping the column?

Yes, why are you considering dropping it at all?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 2

Many thanks Gail..My thinking was that if the NC index is able to implicitly use the Clustered key, what is the point in adding it to the NC? Won't it be making the index unecessarilly wide?(Will adding it make wide when it is already part of it? hmmm need to see it)

Any reason why should I explicitly specify the cluster key in NC index?

I wanted to drop it to make my index leaner, so that less data comes in buffer when this index in used.

But now I can think that that should not be the case here. The clusterd key is already part of it. So specifying it wont make it wider and it wont cause more data to come in my buffer, right??

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

S_Kumar_S (12/28/2010)
My thinking was that if the NC index is able to implicitly use the Clustered key, what is the point in adding it to the NC? Won't it be making the index unecessarilly wide?

No. It's already in the key, because it's the clustered index key. SQL's not stupid enough to put the column in twice just because you've specified it explicitly.

Any reason why should I explicitly specify the cluster key in NC index?

What if the clustered index changes in the future?

What if you add another column to this index at a later date? By doing so, you'll have changed the order of columns if you're relying on implicitly adding the clustering key.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 4

Thanks for your answers Gail....Now I don't see any point in dropping the name of clustered key from my NC index definition.

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

WayneS SSC Guru Points: 95460 More actions · Answer 5

Just to ensure that I understand this correctly: if you explicitly add the CI column(s), they won't also be implicitly added?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 6

Yes, they won't be added again. Just to elaborate it more:

I have a clutered index idx1 on Col1 and a non Clustered index on Col2,Col3. if my predicate is like WHERE Col2='a' and Col3='b' And Col1='b', there still might be a NC index seek although Col1 is not part of NC index definition. The NC index will implicitly use the clustered key as it is already part of NC index.

But if I change my NC index definition to use Col2,Col3, Col1, then it wont make the index wider. As Gail mentioned, it is in fact good to add clustering key explicitly. The reson for this is:

Suppose my clusterd key is changed from Col1 to Col5 and my predicate is WHERE Col2='a' and Col3='b' And Col1='b' and My NC definition is on Col2,Col3, then the index wont be seeked as there is no index on Col1 now. There are pretty good chances that it will skip my eye because Col1 is not part of my NC index definition. had it been part of my definition, I could easly see that I am changing the clusterd key, So I have to change the NC accordingly.

Hope I didn't over explained

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

WayneS (12/28/2010)
Just to ensure that I understand this correctly: if you explicitly add the CI column(s), they won't also be implicitly added?

The columns just need to be there, it doesn't matter whether it's due to you adding them, or SQL adding them because you didn't. SQL's not stupid enough to add them a second time and widen the index unnecessarily.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

WayneS SSC Guru Points: 95460 More actions · Answer 8

GilaMonster (12/29/2010)
WayneS (12/28/2010)
Just to ensure that I understand this correctly: if you explicitly add the CI column(s), they won't also be implicitly added?
The columns just need to be there, it doesn't matter whether it's due to you adding them, or SQL adding them because you didn't. SQL's not stupid enough to add them a second time and widen the index unnecessarily.

Thanks Gail. (And I see you answered this yesterday also... thanks again! (Note to self - read (don't skim) the whole thread!))

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

WayneS (12/29/2010)
(And I see you answered this yesterday also... thanks again! (Note to self - read (don't skim) the whole thread!))

And a couple weeks ago, and last month. Got to the point that I'm writing a blog post on the topic.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass