December 8, 2009 at 9:49 pm
Comments posted to this topic are about the item Clustered Index Internals
December 9, 2009 at 12:43 am
Terribly phrased options to choose.
A clustered index physically rearanges data on disk.
rearranges?
I think this option should look like this: "When created or rebuilded, a clustered index physically rearranges data on disk".
A quote from the explanation (and from the Delaney's book):
Once created, a clustered index is maintained logically rather than physically.
...
After that (after creation), maintaining the clustered index is a logical operation.
Another option from the question:
Dropping a rebuilding a clustered index on a partition scheme will move disk to other drives.
Maybe, "Dropping and rebuilding"?
"Move disk to other drives" – what is the meaning of this? Sounds like abracadabra for me 🙂
Maybe, it means "moving logical disks C:, D:, E: to other hard disk drives"? :hehe:
December 9, 2009 at 1:14 am
vk-kirov, I agree with you. Question and answer is incomprehensible!!!!
December 9, 2009 at 2:19 am
"Dropping a rebuilding a clustered index on a partition scheme will move disk to other drives."
I have no idea what this means, and the answer doesn't seem to mention it.
Can someone explain please?!
December 9, 2009 at 2:40 am
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'.
December 9, 2009 at 2:49 am
"Dropping a rebuilding a clustered index on a partition scheme will move disk to other drives." :w00t:
Are you serious? What if I only have one disk (physical or logical (like in a SAN))? Where will it move? To Limbo or what?
I would say that the answer is wrong, becuase data will only be moved to other drives if the partition scheme uses a File group that is placed on another drive.
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
December 9, 2009 at 3:29 am
"Dropping a rebuilding a clustered index on a partition scheme will move disk to other drives." - Very confusing.
"Keep Trying"
December 9, 2009 at 3:38 am
the options where bit confusing :hehe:
December 9, 2009 at 3:55 am
This last answer would make perfect sense if it was phrased as 'May' instead of 'Will'.
Poorly worded question - especially given how many of the questions on here are irritatingly pernickety about this type of pedantic points.
December 9, 2009 at 4:43 am
This is false:
A clustered index physically rearanges data on disk.
December 9, 2009 at 7:13 am
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.
December 9, 2009 at 7:14 am
Sorry, I wasn't able to understand the last option. Even after few attempts to translate to some other language.
December 9, 2009 at 7:22 am
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."
December 9, 2009 at 8:39 am
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. 🙂
---------------------------------------------------------------------
December 9, 2009 at 9:10 am
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
Viewing 15 posts - 1 through 15 (of 40 total)
You must be logged in to reply to this topic. Login to reply