SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Effect of Clustered Index on Non-Clustered Index


Effect of Clustered Index on Non-Clustered Index

Author
Message
Sean Grebey
Sean Grebey
SSC-Enthusiastic
SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)

Group: General Forum Members
Points: 187 Visits: 87
Not remotely a DBA so forgive me if this is a novice question, but will having a clustered index in a database improve the performance of non-clustered indexes?

Sean
Sean Lange
Sean Lange
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60483 Visits: 17954
Sean Grebey (7/2/2013)
Not remotely a DBA so forgive me if this is a novice question, but will having a clustered index in a database improve the performance of non-clustered indexes?

Sean


Indexes are at the table level not at the database level. A clustered index will not make some other index faster. Indexes are there to help queries perform faster. An index by itself does not have a performance measure. I think you should do some reading on indexes to get a basic understanding of what they do. Over on the left is a link to Stairways, there is a whole series of article on indexes that starts with the very basic and progresses more advanced with each article. Find an hour or so and dive in.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Sean Grebey
Sean Grebey
SSC-Enthusiastic
SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)

Group: General Forum Members
Points: 187 Visits: 87
Sure I understand that indexes are against tables. And I understand the difference between clustered and non-clustered indexes. My question is will non-clustered indexes perform better if there is a clustered index on the table. That is will they used the clustered key perform better if one exists?
Sean Grebey
Sean Grebey
SSC-Enthusiastic
SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)

Group: General Forum Members
Points: 187 Visits: 87
I was asking because from what I understand if there is a clustered index non-clustered indexes use the clustered key, otherwise it uses the rowID (file number, page number and slot number), so it would seem that having a clustered index would improve performance of the non-clustered indexes but I haven't actually found anywhere that said so.
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38000 Visits: 14411
Sean Grebey (7/2/2013)
I was asking because from what I understand if there is a clustered index non-clustered indexes use the clustered key, otherwise it uses the rowID (file number, page number and slot number), so it would seem that having a clustered index would improve performance of the non-clustered indexes but I haven't actually found anywhere that said so.

It's not possible to make that declaration without more information, e.g. the number of columns in the clustering key, its data types and whether it is declared as unique or whether it is actually unique, the data access patterns of the table. Focus on picking a good clustering key, one that is narrow, unique, preferably ever-increasing and most importantly one that supports your most common data access patterns.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)

Group: General Forum Members
Points: 208133 Visits: 41973
Sean Grebey (7/2/2013)
Sure I understand that indexes are against tables. And I understand the difference between clustered and non-clustered indexes. My question is will non-clustered indexes perform better if there is a clustered index on the table. That is will they used the clustered key perform better if one exists?


They absolutely can (it always depends) especially if the clustered index is unique. The columns of a clustered index are added to every non-clustered index. If there is no unique clustered index, then an 8 byte "uniquefier" is also added to the index. Same holds true for when a non-unique clustered index is used except it's worse. In such cases, the columns of the clustered index are added to the columns of the non-clustered index and then the 8 byte uniquifier is added to that which could make a single column clustered index quite wide and comparatively slow.

One of the best presentations I've found on the subject can be found in the "movie" at the following URL.
http://technet.microsoft.com/en-US/sqlserver/gg508879.aspx

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
GilaMonster
GilaMonster
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216619 Visits: 46278
Jeff Moden (7/6/2013)
If there is no unique clustered index, then an 8 byte "uniquefier" is also added to the index.


Not quite

If there's no clustered index, the 8-byte RID is added to all nonclustered indexes as the row location. That's all that's added and it's only when there's no clustered index at all. It's not a uniquifier, it's the physical RID, the combination of File ID, Page Number, Slot index.

Same holds true for when a non-unique clustered index is used except it's worse. In such cases, the columns of the clustered index are added to the columns of the non-clustered index and then the 8 byte uniquifier is added to that which could make a single column clustered index quite wide and comparatively slow.


When there is a clustered index (of any form) then the clustered index key (the entire of it) is added to all nonclustered indexes as the row location. When a clustered index is not unique, the clustered index key gets an additional nullable 4-byte uniquifier added to the clustered index key (only actually has a value on duplicate rows). Since it's part of the clustered index key, the uniquifier is added to all nonclustered indexes as well.

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


Erland Sommarskog
Erland Sommarskog
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5028 Visits: 875
Sean Grebey (7/2/2013)
I was asking because from what I understand if there is a clustered index non-clustered indexes use the clustered key, otherwise it uses the rowID (file number, page number and slot number), so it would seem that having a clustered index would improve performance of the non-clustered indexes but I haven't actually found anywhere that said so.


You could argue that adding a clustered index, makes the non-clustered indexes slower. As long as the table is a heap, lookup is by RID, that is you go straight to the data. With a clustered index, there are a few more logical reads to traverse the tree of the clustered index.

Then again, with the clustered key as part of the non-clustered index, their may be queries where the index becomes covering, which is a huge performance boost.

As it stands, in SQL Server, clustered index is the norm, and you should only use heaps if you know exactly what you are doing. (In other products, the reverse may apply.) You may gain some performance with heaps, but you lose a lot in manageability, which in the end will affect your performance negatively.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)

Group: General Forum Members
Points: 208133 Visits: 41973
GilaMonster (7/6/2013)
Jeff Moden (7/6/2013)
If there is no unique clustered index, then an 8 byte "uniquefier" is also added to the index.


Not quite

If there's no clustered index, the 8-byte RID is added to all nonclustered indexes as the row location. That's all that's added and it's only when there's no clustered index at all. It's not a uniquifier, it's the physical RID, the combination of File ID, Page Number, Slot index.

Same holds true for when a non-unique clustered index is used except it's worse. In such cases, the columns of the clustered index are added to the columns of the non-clustered index and then the 8 byte uniquifier is added to that which could make a single column clustered index quite wide and comparatively slow.


When there is a clustered index (of any form) then the clustered index key (the entire of it) is added to all nonclustered indexes as the row location. When a clustered index is not unique, the clustered index key gets an additional nullable 4-byte uniquifier added to the clustered index key (only actually has a value on duplicate rows). Since it's part of the clustered index key, the uniquifier is added to all nonclustered indexes as well.


I certainly blew that one out of my nose. Blush Thank you VERY much for the education, Gail.

Back to the books.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search