included Column & Clustering Key

Question

included Column & Clustering Key

Rechana Rajan

SSCertifiable

Points: 7717
More actions
January 10, 2017 at 3:16 am

#333391

I have observed that for a query when the PK is in select list and if we create a NC index with column in where clause and add PK as include column this improves performance from NC index seek and keyloopup to new non clustered index seek .
My doubt is when we create a Non clustered index the clustering key is already a part of it and then why adding PK column as included help change the execution plan to a better performance. Please correct me if I am wrong.
January 18, 2017 at 10:15 pm

This was removed by the editor as SPAM

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

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

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

Rechana Rajan (1/10/2017)
My doubt is when we create a Non clustered index the clustering key is already a part of it and then why adding PK column as included help change the execution plan to a better performance.

It won't. As you note, the clustered index key is already part of the index.

That said, you should explicitly include it if you need it, as there's no guarantee that someone won't move the clustered index somewhere at a later time.

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

GilaMonster (1/10/2017)
Rechana Rajan (1/10/2017)
My doubt is when we create a Non clustered index the clustering key is already a part of it and then why adding PK column as included help change the execution plan to a better performance.
It won't. As you note, the clustered index key is already part of the index.
That said, you should explicitly include it if you need it, as there's no guarantee that someone won't move the clustered index somewhere at a later time.

Good advice Gail. Adding that to my list of things to look out for and recommend!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Rechana Rajan SSCertifiable Points: 7717 More actions · Answer 3

GilaMonster (1/10/2017)
Rechana Rajan (1/10/2017)
My doubt is when we create a Non clustered index the clustering key is already a part of it and then why adding PK column as included help change the execution plan to a better performance.
It won't. As you note, the clustered index key is already part of the index.
That said, you should explicitly include it if you need it, as there's no guarantee that someone won't move the clustered index somewhere at a later time.

Thanks Gail.

You meant to say that someone might move the clustered index column to a different column ? If that is the case the lookup for noncluster index will change to new cluster key right?

Also if i explicitly specify the cluster key in a nonclustered index will it create any over head?

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

Rechana Rajan (1/11/2017)
If that is the case the lookup for noncluster index will change to new cluster key right?

???

Also if i explicitly specify the cluster key in a nonclustered index will it create any over head?

No.

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

Rechana Rajan SSCertifiable Points: 7717 More actions · Answer 5

GilaMonster (1/11/2017)
Rechana Rajan (1/11/2017)
If that is the case the lookup for noncluster index will change to new cluster key right?
???
Also if i explicitly specify the cluster key in a nonclustered index will it create any over head?
No.

Thanks Gail.

What i mean is for NC index the leaf page will have pointers to Cluster key and when the cluster key changes the pointers will be on new clusterkey column right.

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

Yes, if the clustered index is changed, all nonclustered indexes are rebuilt with the new clustered index key in them.

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

Sergiy SSC Guru Points: 110208 More actions · Answer 7

Rechana Rajan - Tuesday, January 10, 2017 3:16 AM
I have observed that for a query when the PK is in select list and if we create a NC index with column in where clause and add PK as include column this improves performance from NC index seek and keyloopup to new non clustered index seek . My doubt is when we create a Non clustered index the clustering key is already a part of it and then why adding PK column as included help change the execution plan to a better performance. Please correct me if I am wrong.

Your wording suggests that you assume that PK is always a clustered index.
It's not necessarily the case.
A table with PK may have another index as clustered, or not have a clustered index at all.

_____________
Code for TallyGenerator