July 21, 2013 at 10:28 pm
Comments posted to this topic are about the item Tables and Clustered Indexes Part 2
Hakim Ali
www.sqlzen.com
July 22, 2013 at 8:41 am
Very well described! Thank you.
July 22, 2013 at 12:27 pm
This is a nice demonstration of the fact that a clustered index is essentially the same as a "clustered table" but in several places in the article it was clearly implied that if a table has a non-clustered index defined on it is no longer a heap.
For example:
Note that this table is currently a heap, it does not have any indexes of any type.
The index is ignored, and the table is treated like a heap, resulting in a table scan.
Disabling the non-clustered index causes the query to treat the table like a heap
This is not true. A table is not considered a heap only--and only--when a clustered index is defined on it. If it has one or more non-clustered indexes but no clustered index it is still considered a heap.
All a non-clustered index (on a heap table) is a copy of one or more columns stored separately (from the table) on the disk. A non-clustered index doesn't in anyway change the structure of the heap itself hence a table remains a heap even when you have one or more non-clustered indexes defined on it. On the other hand, when you define a clustered index on a heap table the data in then physically organized in the way you defined the clustered index (and rewritten to the disk with the index structures built on top of the data pages).
See this MSDN article for the official word.
Amar
July 22, 2013 at 12:33 pm
AmarPo (7/22/2013)
To the author:This is a nice demonstration of the fact that a clustered index is essentially the same as a "clustered table" but in several places in the article it was clearly implied that if a table has a non-clustered index defined on it is no longer a heap.
Thanks for the feedback. I went back and re-read the article, and I don't think I agree with your assessment - it does not imply that tables with only non-clustered indexes are no longer heaps. I do not say at any point that adding a non-clustered index to a table makes it stop being a heap. I am merely stating (and this is backed up with evidence from execution plans in the article) that from the query's point of view, the table is or is not treated like a heap depending on whether certain usable indexes exist or not. The query chooses to use the non-clustered index if it exists, and thus does not treat the table as a heap. When this index does not exist, the query treats the table as a heap. Where is the implication of the table no longer actually being a heap?
Hakim Ali
www.sqlzen.com
July 22, 2013 at 12:43 pm
To the author:
If you don't mind, it might be worth to rewrite the article to remove this implication so folks new to SQL Server won't read it the wrong way and get a message you didn't intend. Thanks!
Amar
July 25, 2013 at 3:25 am
"A table does not so much have a clustered index as a table is a clustered index. "
Thanks, this is a nice (and memorable) way of putting it.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply