Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Clustered indexes Expand / Collapse
Author
Message
Posted Thursday, May 6, 2010 6:34 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 15,737, Visits: 28,143
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #916869
Posted Thursday, May 6, 2010 6:43 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 7:35 AM
Points: 2,819, Visits: 2,563
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.
Post #916885
Posted Thursday, May 6, 2010 6:50 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 5:46 AM
Points: 2,899, Visits: 469
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...



Post #916891
Posted Thursday, May 6, 2010 7:05 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, August 21, 2014 6:44 AM
Points: 999, Visits: 869
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.



Post #916911
Posted Thursday, May 6, 2010 7:33 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, April 25, 2014 8:31 AM
Points: 513, 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.
Post #916947
Posted Thursday, May 6, 2010 7:37 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, April 25, 2014 8:31 AM
Points: 513, Visits: 430
Re-read the MSDN article you'd attached in one of your earlier posts (Clustered Index Structures). Good to know. Thanks.
Post #916954
Posted Thursday, May 6, 2010 7:39 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:57 AM
Points: 3,323, Visits: 1,988
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.
Post #916958
Posted Thursday, May 6, 2010 8:19 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 8:36 AM
Points: 3,918, Visits: 3,638
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.
Post #917008
Posted Thursday, May 6, 2010 8:21 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 12:37 PM
Points: 2,607, Visits: 17,929
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
Post #917010
Posted Thursday, May 6, 2010 8:35 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 5:54 AM
Points: 554, Visits: 1,197
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).
Post #917026
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse