Clustered Index Internals

  • Brandon Forest

    SSCommitted

    Points: 1814

    Comments posted to this topic are about the item Clustered Index Internals

  • vk-kirov

    SSCertifiable

    Points: 7686

    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:

  • Carlo Romagnano

    SSC-Insane

    Points: 22008

    vk-kirov, I agree with you. Question and answer is incomprehensible!!!!

  • Toreador

    SSChampion

    Points: 11259

    "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?!

  • jts2013

    Hall of Fame

    Points: 3226

    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'.

  • SQLWinther

    SSCertifiable

    Points: 5946

    "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

  • ChiragNS

    One Orange Chip

    Points: 26137

    "Dropping a rebuilding a clustered index on a partition scheme will move disk to other drives." - Very confusing.

    "Keep Trying"

  • Bhavesh_Patel

    SSCrazy

    Points: 2259

    the options where bit confusing :hehe:



    [font="System"]Bhavesh Patel[/font]

    http://bhaveshgpatel.wordpress.com/
  • RichB

    SSCrazy Eights

    Points: 9651

    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.

  • adrian.buzila

    Ten Centuries

    Points: 1116

    This is false:

    A clustered index physically rearanges data on disk.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720887

    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.

  • honza.mf

    SSCertifiable

    Points: 5519

    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

    Ten Centuries

    Points: 1116

    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

    SSC Guru

    Points: 104200

    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

    SSCertifiable

    Points: 5272

    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 41 total)

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