SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Clustered Index Internals


Clustered Index Internals

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)

Group: Administrators
Points: 147093 Visits: 19434
If you think something is wrong, please provide a reason and reference. We have not reason to take your work for it over anyone else's.

The third answer has been edited and we are looking for another reference to support the partitioning item.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
honza.mf
honza.mf
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2557 Visits: 1323
Sorry, I wasn't able to understand the last option. Even after few attempts to translate to some other language.



See, understand, learn, try, use efficient
© Dr.Plch
adrian.buzila
adrian.buzila
SSC Eights!
SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)

Group: General Forum Members
Points: 978 Visits: 268
This is wrong:
"A clustered index physically rearanges data on disk."
because it is out of context
and because: "Once created, a clustered index is maintained logically rather than physically."
george sibbald
george sibbald
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24758 Visits: 13698
jts_2003 (12/9/2009)
This reminded me of something that has been puzzling me recently about clustered indexes. If the leaf level of a clustered index actually is the table's data itself, does this mean the table data only exists in the index, or is there another copy somewhere else?
e.g. if a table without a clustered index has one created, does all the table data get copied into the leaf level of the clustered index, or does it get 'moved'.


as you say the the leaf level of the clustered index is the table data itself so by definition there is only one copy of it. The clustered 'index' consists of the root node, a variable no of intermediate nodes, the last of which points to the actual data as the clustered index controls the logical ordering of the data. Its more accurate to think of the leaf node of the clustered index existing as the table data.

As to the question itself the explanation contradicts the answer as posed:


Explanation: "... all the columns of every row in the table are in the leaf level of a clustered index. At the time a clustered index is created, data in the table is copied and ordered by the clustering key. Once created, a clustered index is maintained logically rather than physically.

therefore the clustered index only dictates physical order at creation or rebuild time, so the options missed an important word, rebuild, as in
A clustered index rebuild physically rearranges data on disk.

so long as the question generates discussion it has fulfilled its purpose. :-)

---------------------------------------------------------------------
Oleg Netchaev
Oleg Netchaev
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2213 Visits: 1817
Not that there is anything wrong, but there is no explanation in the answer about the "Dropping and rebuilding a clustered index on a partition scheme may move data to other drives" statement.

The clustered index on a partition scheme has one row in the sys.partitions per partition, each with index_id = 1. Instead of having one, it has as many b-tree structures as the number of partitions. Dropping and rebuilding index on a partition will rearrange the data, but where the moving of the data to other drives come into play? I am not saying that it is not happening, but the QoD does not provide an explanation to this point. In a mean time it would be nice to find it out.

Oleg
adrian.buzila
adrian.buzila
SSC Eights!
SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)

Group: General Forum Members
Points: 978 Visits: 268
I agree that is not wrong, but is false as a general statement. It is out of context.
It is true where you create/rebuilt an index, but false when you insert/update/delete on a table. When you insert/update/delete the order for the existing data is not modified on the disk. The order in this case is mantained logically.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)

Group: Administrators
Points: 147093 Visits: 19434
Actually I'm not sure it's wrong. When you insert/update data you can cause a page split. In that case, the data is rearranged on disk.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
adrian.buzila
adrian.buzila
SSC Eights!
SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)

Group: General Forum Members
Points: 978 Visits: 268
If you cause a page split. But if not? It is still false as a general statement.
Source-NH
Source-NH
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2063 Visits: 479
I am very interested in how the rebuilding of a clustered index would result in data being moved to another partition. The partition of a particular record in a table is determined through a calculation based on data in the record itself. Unless either the data or the partition function are changed, than a given record will always be placed into the same partition that it was previously placed in. If this is an accurate statement, than an index rebuild should NEVER result in data being moved into a different partition.

Also, the doubly linked list would imply that there are pointers on every page to the previous and next pages in the table. I have NEVER read anywhere in any documentation that this exists. As far as I knew, the IAM pages contained the bit maps providing the allocation of pages to a table. Can anyone point me to documentation that describes this linked list?
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)

Group: General Forum Members
Points: 66949 Visits: 18570
There have been a few concerns regarding the moving of data to a different drive if you rebuild an index in a partition scheme.

I want to provide a scenario to support the "may" in this option.

When partitioning a table, you create a filegroup for each partition. Once created files may be created for each filegroup. It may be necessary for each file to be assigned to a different drive. Let's say the table is 300GB and spread across 2 partitions and filegroups. Each of these filegroups has 10 files spread across 10 drives - that means each file is roughly 15GB, if we have partitioned the data down the middle (it's easier for this scenario).

Each partition data will remain in that partition when a rebuild occurs, however, due to the rebuild we have a scenario where the data could now potentially move from one file to another in the filegroup and thus be on a different drive.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

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