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 (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)

Group: General Forum Members
Points: 223387 Visits: 33564
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
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17556 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
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4186 Visits: 697
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
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1776 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
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1759 Visits: 431
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
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1759 Visits: 431
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
SSCrazy Eights
SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)

Group: General Forum Members
Points: 9644 Visits: 2470
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 (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6809 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
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7295 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
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1631 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