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


Clustered indexes


Clustered indexes

Author
Message
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96687 Visits: 33013
Excellent question and, holy cow, I got it right!

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Daniel Bowlin
Daniel Bowlin
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7838 Visits: 2629
Henry_Lee (5/6/2010)
Great question and explanation.

I also got tripped up by the difference between logical and physical ordering.


Me too. Good question. Thanks.
dgabele
dgabele
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3945 Visits: 667
Good question, got tripped up on:

"All nonclustered indexes include the clustered index columns in their index pages"

I thought this was not true for the mere point that a table does not have to have a clustered index - meaning that some nonclustered indexes only have the RID, therefore not ALL nonclustered indexes include clustered index columns...



Andeavour
Andeavour
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1298 Visits: 932
I thought this was not true for the mere point that a table does not have to have a clustered index - meaning that some nonclustered indexes only have the RID, therefore not ALL nonclustered indexes include clustered index columns...


You've sort of proved your own point there. If there is no clustered index on the table, then there is no clustering key to include, but if the table HAS a clustered index then the non-clustered indexes ALL include the clustering key.



rjv_rnjn
rjv_rnjn
SSC Eights!
SSC Eights! (945 reputation)SSC Eights! (945 reputation)SSC Eights! (945 reputation)SSC Eights! (945 reputation)SSC Eights! (945 reputation)SSC Eights! (945 reputation)SSC Eights! (945 reputation)SSC Eights! (945 reputation)

Group: General Forum Members
Points: 945 Visits: 430
I re-read the MSDN article as to why I thought it's physical order of arrangement.
MSDN: http://msdn.microsoft.com/en-us/library/aa933131(SQL.80).aspx
Because the clustered index dictates the physical storage order of the data in the table
And the option was:
Rows in a table are PHYSICALLY stored in the clustered index order

I am not sure I quite understand the difference.

A good learn about the XML index, didn't know that.
rjv_rnjn
rjv_rnjn
SSC Eights!
SSC Eights! (945 reputation)SSC Eights! (945 reputation)SSC Eights! (945 reputation)SSC Eights! (945 reputation)SSC Eights! (945 reputation)SSC Eights! (945 reputation)SSC Eights! (945 reputation)SSC Eights! (945 reputation)

Group: General Forum Members
Points: 945 Visits: 430
Re-read the MSDN article you'd attached in one of your earlier posts (Clustered Index Structures). Good to know. Thanks.
Ken Wymore
Ken Wymore
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6549 Visits: 2396
rjv_rnjn (5/6/2010)
I re-read the MSDN article as to why I thought it's physical order of arrangement.
MSDN: http://msdn.microsoft.com/en-us/library/aa933131(SQL.80).aspx
Because the clustered index dictates the physical storage order of the data in the table
And the option was:
Rows in a table are PHYSICALLY stored in the clustered index order

I am not sure I quite understand the difference.


The part in MSDN about rows being physically stored in the clustered index order is what threw me off as well. I think Hugo's explanation makes sense though.
Cliff Jones
Cliff Jones
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5187 Visits: 3648
What threw me off was "All nonclustered indexes include the clustered index columns in their index pages" . I thought this was incorrect because the clustered index columns are contained in the Leaf Node Data Pages and not every index page.
Chad Crawford
 Chad Crawford
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4317 Visits: 18732
Wahoo! Got it right! I have to admit that I wasn't sure about the last three options, but I figured that since they were there, at least one of them had to be marked, so I guessed then used BOL to confirm before submitting (so maybe I cheated a little on that). Thanks Hugo (and Kalen, since without having read her books and class I never would have got this right).

Chad
Bradley Deem
Bradley Deem
SSC Eights!
SSC Eights! (965 reputation)SSC Eights! (965 reputation)SSC Eights! (965 reputation)SSC Eights! (965 reputation)SSC Eights! (965 reputation)SSC Eights! (965 reputation)SSC Eights! (965 reputation)SSC Eights! (965 reputation)

Group: General Forum Members
Points: 965 Visits: 1248
dgabele (5/6/2010)
Good question, got tripped up on:

"All nonclustered indexes include the clustered index columns in their index pages"

I thought this was not true for the mere point that a table does not have to have a clustered index - meaning that some nonclustered indexes only have the RID, therefore not ALL nonclustered indexes include clustered index columns...


Good intentions with the question, but the one check box was a crap shoot (which I guessed wrong on). Better wording could have been "When a table has a unique clustered index all nonclustered indexes include exactly the clustered index key in their index pages." (Notice the reduction in ambiguity.)

I choose false because not all nonclustered indexes include the clustered index columns in their index pages. IE Heaps, nonclustered indexes will include a pointer to the row.

From BOL
If the table is a heap, which means it does not have a clustered index, the row locator is a pointer to the row. The pointer is built from the file identifier (ID), page number, and number of the row on the page. The whole pointer is known as a Row ID (RID).

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