Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Clustered indexes Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, May 05, 2010 8:58 PM
 SSCertifiable Group: General Forum Members Last Login: Yesterday @ 12:18 PM Points: 5,603, Visits: 7,802
 Comments posted to this topic are about the item Clustered indexes Hugo Kornelis, SQL Server MVPVisit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #916635
 Posted Wednesday, May 05, 2010 9:17 PM
 SSCommitted Group: General Forum Members Last Login: Tuesday, December 03, 2013 5:28 PM Points: 1,676, Visits: 1,728
 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 05, 2010 10:55 PM
 Grasshopper Group: General Forum Members Last Login: Friday, October 04, 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 06, 2010 1:08 AM
 SSCertifiable Group: General Forum Members Last Login: Yesterday @ 12:18 PM Points: 5,603, Visits: 7,802
 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 MVPVisit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #916696
 Posted Thursday, May 06, 2010 1:27 AM
 Mr or Mrs. 500 Group: General Forum Members Last Login: Today @ 2:07 AM Points: 558, Visits: 1,418
 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 06, 2010 2:25 AM
 Grasshopper Group: General Forum Members Last Login: Friday, October 04, 2013 3:16 AM Points: 16, Visits: 20
 Thank you Hugo .I had the misconception of physical arrangement !!
Post #916722
 Posted Thursday, May 06, 2010 2:33 AM
 SSC Eights! Group: General Forum Members Last Login: Thursday, December 05, 2013 3:47 AM Points: 970, Visits: 658
 Thanks Hugo, an intersting question and a great explanation of how clustered indexes are stored. Hope this helps,Rich
Post #916726
 Posted Thursday, May 06, 2010 3:38 AM
 SSC-Insane Group: General Forum Members Last Login: Friday, June 21, 2013 1:53 PM Points: 21,376, Visits: 9,590
 Hugo Kornelis (5/5/2010)Comments posted to this topic are about the item Clustered indexesHoly 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 06, 2010 3:54 AM
 Forum 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 06, 2010 6:19 AM
 Old Hand Group: General Forum Members Last Login: Sunday, December 08, 2013 5:26 PM Points: 307, Visits: 1,397
 Great question and explanation.I also got tripped up by the difference between logical and physical ordering.
Post #916854

 Permissions

 Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.