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 Wednesday, May 5, 2010 8:58 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:40 AM
Points: 5,916, Visits: 8,168
Comments posted to this topic are about the item Clustered indexes


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #916635
Posted Wednesday, May 5, 2010 9:17 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 10, 2014 12:49 PM
Points: 1,676, Visits: 1,754
Both the question and explanation are excellent, thank you Hugo.

I think that the options about logical/physical order will generate a very good discussion. This is because the Create Index page in BOL includes the following:

the logical order of the key values determines the physical order of the corresponding rows in a table.

Oleg
Post #916637
Posted Wednesday, May 5, 2010 10:55 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 4, 2013 3:16 AM
Points: 16, Visits: 20
I have a doubt regarding the logical order of rows for Clustered Index.
Main difference between Clustered and Non-Clustered is Clustered is physical arrangement of rows and Non-Clustered is logical arranging of rows .
Can you explain a bit more on the arrangement of actual data rows for Clustered and Non-Clustered index.
Post #916656
Posted Thursday, May 6, 2010 1:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:40 AM
Points: 5,916, Visits: 8,168
satya11001-1013569 (5/5/2010)
I have a doubt regarding the logical order of rows for Clustered Index.
Main difference between Clustered and Non-Clustered is Clustered is physical arrangement of rows and Non-Clustered is logical arranging of rows .

Hi Satya,

This is not correct. Clustered and non-clustered indexes are built very similar. The only difference is the actual contents of the leaf pages.
For a clustered index, root and intermediate pages contain the index key and a pointer to the lower-level page; leaf pages contain the all the columns (except LOB data, such as varchar(max) or xml).
For a nonclustered index, root and intermediate pages contain the index key and a pointer to the lower-level page; leaf pages contain the index key and a pointer to the data page where the complete row can be found. This pointer is either the clustered index key, or (if the table does not have a clustered index) the RID.

Can you explain a bit more on the arrangement of actual data rows for Clustered and Non-Clustered index.

A sketch of the index structure (here for a clustered index, but as I said: the only difference for a nonclustered index is the actual contents of the leaf pages) can be found on http://msdn.microsoft.com/en-us/library/ms177443.aspx. The blue pointers are the pointers to lower level index pages just mentioned. The black arrows indicate the "next page"" / "previous page" pointers found on every page in an index. These pointer chains are used when an index is processed in its logical order.

The physical arrangement of these pages can be completely different. As an example, let's suppose that an existing table happens to have all its 10,000 leaf pages on the first 10,000 pages in the database file (very unlikely in reality, but work with me). Now an INSERT is executed and the new row should be inserted in the fifth page - but that page is already full, so it has to be split - half the data remains on page 5, half the data goes to a new page that, logicallly, belongs between page 5 and the "old" page 6. SQL Server will not physically move "old" pages 6 through 10,000 up one location - that would really kill performance! Instead, a new page will be allocated "somewhere" in the data file. This new page will have its "previous page" and "next page" pointers pointing to the "old" pages 5 and 6 respectively, and the "next page" pointer on the old page 5 and the "previous page" pointer on the old page 6 will point to the new page. The result is that the pointer chain now still implements the logical order of the index; the physical location of pages however does not.
(And since this same allocation mechanism has been used when the first 10,000 pages were allocated, it is indeed extremely unlikely to find 10,000 data pages allocated consecutively.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #916696
Posted Thursday, May 6, 2010 1:27 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: Wednesday, July 23, 2014 12:55 AM
Points: 558, Visits: 1,458
An excellent question....which I got completely wrong....I hate these "select all that apply" questions Thanks for the learning experience though.
Post #916704
Posted Thursday, May 6, 2010 2:25 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 4, 2013 3:16 AM
Points: 16, Visits: 20
Thank you Hugo .
I had the misconception of physical arrangement !!
Post #916722
Posted Thursday, May 6, 2010 2:33 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: Friday, June 27, 2014 2:20 AM
Points: 974, Visits: 691
Thanks Hugo, an intersting question and a great explanation of how clustered indexes are stored.

Hope this helps,
Rich



Post #916726
Posted Thursday, May 6, 2010 3:38 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 12:52 AM
Points: 21,385, Visits: 9,601
Hugo Kornelis (5/5/2010)
Comments posted to this topic are about the item <A HREF="/questions/Indexing/69839/">Clustered indexes</A>


Holy sh***it only 2% of correct answers as of now (I'm in the mojority of course ).

Thanks for the xml info... never used it before so I learned something here.
Post #916746
Posted Thursday, May 6, 2010 3:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 14, 2012 6:24 PM
Points: 9, Visits: 17
thanks Hugo
Post #916760
Posted Thursday, May 6, 2010 6:19 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 1:54 PM
Points: 320, Visits: 1,476
Great question and explanation.

I also got tripped up by the difference between logical and physical ordering.
Post #916854
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse