Clustered indexes

Question

Clustered indexes

Hugo Kornelis

SSC Guru

Points: 64790
More actions
May 5, 2010 at 8:58 pm

#221291

Comments posted to this topic are about the item Clustered indexes
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Viewing 15 posts - 1 through 15 (of 54 total)

You must be logged in to reply to this topic. Login to reply

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 1

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

satya11001-1013569 SSC Rookie Points: 40 More actions · Answer 2

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.

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 3

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/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Chris Houghton SSCrazy Points: 2557 More actions · Answer 4

An excellent question....which I got completely wrong....I hate these "select all that apply" questions 😛 Thanks for the learning experience though.

satya11001-1013569 SSC Rookie Points: 40 More actions · Answer 5

Thank you Hugo .

I had the misconception of physical arrangement !!

RichardDouglas SSCarpal Tunnel Points: 4029 More actions · Answer 6

Thanks Hugo, an intersting question and a great explanation of how clustered indexes are stored.

Hope this helps,
Rich

[p]
[/p]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

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 :w00t:).

Thanks for the xml info... never used it before so I learned something here.

darkloud Grasshopper Points: 15 More actions · Answer 8

darkloud

Grasshopper

Points: 15

May 6, 2010 at 3:54 am

#1163032

thanks Hugo

Henry_Lee SSCrazy Points: 2714 More actions · Answer 9

Great question and explanation.

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

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 10

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 11

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 SSCertifiable Points: 5295 More actions · Answer 12

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 SSCrazy Points: 2600 More actions · Answer 13

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 Hall of Fame Points: 3023 More actions · Answer 14

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.