Tables and Clustered Indexes Part 2

  • hakim.ali

    SSCarpal Tunnel

    Points: 4236

    Comments posted to this topic are about the item Tables and Clustered Indexes Part 2

    Hakim Ali
    www.sqlzen.com

  • balasaukri

    Ten Centuries

    Points: 1383

    Very well described! Thank you.

  • AmarPo

    Old Hand

    Points: 375

    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

  • hakim.ali

    SSCarpal Tunnel

    Points: 4236

    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

  • AmarPo

    Old Hand

    Points: 375

    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

  • Tharg

    SSC Eights!

    Points: 920

    "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 6 (of 6 total)

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