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