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 Index Internals Expand / Collapse
Author
Message
Posted Wednesday, December 9, 2009 7:13 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 2:13 PM
Points: 31,040, Visits: 15,472
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
Post #831384
Posted Wednesday, December 9, 2009 7:14 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 1:20 AM
Points: 1,375, Visits: 1,315
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
Post #831386
Posted Wednesday, December 9, 2009 7:22 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: Today @ 10:57 AM
Points: 911, Visits: 218
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."
Post #831394
Posted Wednesday, December 9, 2009 8:39 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:06 AM
Points: 5,867, Visits: 12,950
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.


---------------------------------------------------------------------

Post #831484
Posted Wednesday, December 9, 2009 9:10 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:21 AM
Points: 1,676, Visits: 1,757
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
Post #831524
Posted Wednesday, December 9, 2009 9:23 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: Today @ 10:57 AM
Points: 911, Visits: 218
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.


Post #831538
Posted Wednesday, December 9, 2009 9:29 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 2:13 PM
Points: 31,040, Visits: 15,472
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
Post #831545
Posted Wednesday, December 9, 2009 9:34 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: Today @ 10:57 AM
Points: 911, Visits: 218
If you cause a page split. But if not? It is still false as a general statement.
Post #831553
Posted Wednesday, December 9, 2009 9:39 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 29, 2014 10:16 AM
Points: 1,626, Visits: 478
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?
Post #831559
Posted Wednesday, December 9, 2009 10:10 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 4:15 PM
Points: 17,654, Visits: 15,506
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #831591
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse