CODEWITHSUNDEEP
sql-serversql-server-2005
Abe Miessler
Abe Miessler

Reputation: 85056

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

Upvotes: 11

Views: 9444

Answers (3)

Denys Avilov
Denys Avilov

Reputation: 434

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.

Upvotes: 3

Piotr Rodak
Piotr Rodak

Reputation: 1941

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.

Upvotes: 8

Remus Rusanu
Remus Rusanu

Reputation: 294317

From sys.indexes:

Name of the index...
NULL = Heap

So those are heaps.

Upvotes: 13

Related Questions