Interview Question of the Week #062 – How to Find Table Without Clustered Index (Heap)?

Question: How do I find tables without a clustered index? For a disk-based rowstore table, a row in sys.indexes with index_id 0 identifies a heap. Include the schema name so similarly named tables do not become ambiguous.

The original question often follows another one: does creating a primary key always create a clustered index? A primary key is clustered by default when no clustered index already exists, but you can explicitly make it nonclustered. A heap can therefore have a primary key and other nonclustered indexes.

-- Original inventory, in your chosen AdventureWorks database:
SELECT DISTINCT [TABLE]=OBJECT_NAME(object_id)
FROM sys.indexes
WHERE index_id=0 AND OBJECTPROPERTY(object_id,'IsUserTable')=1
ORDER BY [TABLE];

-- Include schema names and restrict this inventory to disk-based rowstore heaps:
SELECT SCHEMA_NAME(t.schema_id) AS SchemaName,t.name AS TableName
FROM sys.tables AS t
JOIN sys.indexes AS i ON i.object_id=t.object_id AND i.index_id=0
WHERE t.is_ms_shipped=0 AND t.is_memory_optimized=0
ORDER BY SchemaName,TableName;

Finding a heap is the beginning of a review

The original advice to create a clustered index on every heap was too broad. A temporary staging table that is loaded, processed and emptied can be a reasonable heap. A heap with suitable nonclustered indexes can also serve particular access patterns well.

Frequent updates that enlarge rows can create forwarded records, adding work to later reads. Large scans and unsuitable access paths may be expensive. Those are reasons to examine the workload, execution plans and indexes, rather than treating the inventory as proof of high CPU or I/O.

When a clustered index is appropriate, choose its key deliberately. It changes the row locator used by nonclustered indexes too. A clustered columnstore is a different storage structure, not a heap simply because it lacks a rowstore clustered B-tree.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.