Why does the sys.indexes table have a NULL Index name?

Question

I ran this query:

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 had a NULL name. What does this mean?

Answer 1

The sys.indexes view shows not only indexes, but also tables which don't have indexes on them. Such tables are called heaps. In such case, there is no name of the index. This can be misleading, I agree.

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 posted returns NULL index name, it means there is no clustered index on your table dbo.Stu.

I would recommend creating a clustered index on the table.

Answer 2

From sys.indexes:

Name of the index...
NULL = Heap

So those are heaps.