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

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 h开发者_运维知识库ad a NULL name. What does this mean?

From sys.indexes:

Name of the index...
NULL = Heap

So those are heaps.

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.

Imortant addition to above answers: Index name in sys.indexes catalog view can be NULL in TWO cases:

1) As specified in MSDN, if it is actually a heap, i.e. table has no clustered index. For each nunclustered table there is one such a record in sys.indexes view (even if table has other, nonclustered indexes).

2) If this is a statistic (MSDN surprisingly keeps silence about this!). At least, I observed such a condition on one of my databases under SQL 2008 R2 server.