Does index_id=0 in sys.indexes refer to the table

Question

Unfortunately the BOL is a little vague on this, but index_id = 0 on sys.indexes or sys.partitions appears to refer to the table itself where there is no clustered index on the table. True - or am I missing something?

Answer 1

Does index_id=0 in sys.indexes refer to the table itself?

No, For heap based tables it will always exist and it refers to the IAM (Index Allocation Map). For tables with clustered indexes it will not exist.

Notice the "type_desc" is HEAP on these indexes, there is a really good blog entry about the Index Allocation Map.

So, Heap tables (ones with no clustered index) will always have one index with entry index_id = 0, and tables with clustered indexes will always have an entry with index_id = 1.