Why does the sys.indexes table have a null index name?

Question

I fulfilled this request:

SELECT i.name AS IndexName, s.used_page_count * 8 AS IndexSizeKB FROM sys.dm_db_partition_stats AS s JOIN sys.indexes AS i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id WHERE s.[object_id] = object_id('dbo.Stu') ORDER BY i.name 

and the largest index returned is NULL. What does it mean?

Answer 1

From sys.indexes :

Index Name ...
NULL = Heap

So these are heaps.

Answer 2

The sys.indexes view not only displays indexes, but also tables that have no indexes. Such tables are called heaps. In this case, the index name is missing. I agree, this can be misleading.

 SELECT i.object_id, i.type_desc, i.name AS IndexName, s.used_page_count * 8 AS IndexSizeKB FROM sys.dm_db_partition_stats AS s JOIN sys.indexes AS i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id WHERE s.[object_id] = object_id('dbo.Stu') ORDER BY i.object_id, i.name 

Basically, if the query you send returns a NULL index, this means that your dbo.Stu table does not have a clustered index.

I would recommend creating a clustered index in a table.

Answer 3

An imitative addition to the answers above: The index name in the sys.indexes catalog view can be NULL in two cases:

1) As indicated in MSDN, if it is actually a heap, that is, the table does not have a clustered index. For each nunclustered table, there is one such entry in the sys.indexes view (even if the table has other, nonclustered indexes).

2) If this is statistics (MSDN is surprisingly silent about it!). At least I observed such a condition in one of my databases under SQL Server 2008 R2.