Heap or Clustered Index: When a Heap Table Is the Right Choice

Heap or clustered index is a choice you make for every table, and a clustered index is the safer default.

A heap is not a mistake. It fits a few jobs well. It also has one habit, forwarded records, that quietly multiplies the reads of a scan. The next sections measure that habit and the two places where a heap wins.

What a Heap Is

A heap is a table without a clustered index. SQL Server stores its rows on any page that has room, in no order. The only way to find a row is to scan every page, or to use a nonclustered index. That index points at the row with an 8 byte row identifier: the file, the page and the slot.

A clustered table stores its rows in the order of the key, and finds them by that key. The heap or clustered index choice decides how SQL Server finds every row.

The demo builds the same table twice. One copy is a heap, and the other has a clustered index on the ticket number. Each has 50,000 rows with an empty Details column. The first query reports the size and the forwarded records of the heap, and the reads of a full scan.

IF DB_ID(N'ForwardedHeapDemo') IS NULL CREATE DATABASE ForwardedHeapDemo;
GO
USE ForwardedHeapDemo;
GO
DROP TABLE IF EXISTS dbo.TicketsHeap;
DROP TABLE IF EXISTS dbo.TicketsClustered;
CREATE TABLE dbo.TicketsHeap (TicketID int NOT NULL, Title varchar(200) NOT NULL, Details varchar(2000) NULL);
CREATE TABLE dbo.TicketsClustered (TicketID int NOT NULL, Title varchar(200) NOT NULL, Details varchar(2000) NULL);
CREATE CLUSTERED INDEX CIX_TicketsClustered ON dbo.TicketsClustered (TicketID);
INSERT dbo.TicketsHeap (TicketID, Title, Details)
SELECT TOP (50000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), 'Ticket', NULL FROM sys.all_columns AS a CROSS JOIN sys.all_columns AS b;
INSERT dbo.TicketsClustered (TicketID, Title, Details) SELECT TicketID, Title, Details FROM dbo.TicketsHeap;

SELECT N'Before' AS Moment, OBJECT_NAME(object_id) AS TableName, page_count, record_count, forwarded_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'dbo.TicketsHeap'), 0, NULL, 'DETAILED');
SET STATISTICS IO ON;
SELECT COUNT(*) AS Tickets FROM dbo.TicketsHeap;
SET STATISTICS IO OFF;

The heap holds 143 pages and no forwarded records. The Messages tab reports 143 logical reads for the count, so one read per page.

Where a Heap Hurts: Forwarded Records

Now give half of the tickets a longer Details text. A row that grows can outgrow its page. A clustered table splits the page. A heap moves the row to a page with room and leaves a forwarding pointer at the old place. The scan then visits the old slot and follows the pointer, so every moved row costs a second read. The script widens the same rows in both tables.

UPDATE dbo.TicketsHeap SET Details = REPLICATE('d', 400) WHERE TicketID % 2 = 0;
UPDATE dbo.TicketsClustered SET Details = REPLICATE('d', 400) WHERE TicketID % 2 = 0;
GO
SELECT OBJECT_NAME(object_id) AS TableName, index_type_desc, page_count, record_count, forwarded_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED')
WHERE OBJECT_NAME(object_id) LIKE N'Tickets%' AND index_level = 0;
SET STATISTICS IO ON;
SELECT COUNT(*) AS Tickets FROM dbo.TicketsHeap;
SELECT COUNT(*) AS Tickets FROM dbo.TicketsClustered;
SET STATISTICS IO OFF;

The heap has 24,290 forwarded records. Its record count of 74,290 includes them, and the same 50,000 tickets now cost 25,782 reads. The clustered table has no forwarding and needs 2,642 reads. Its page splits left the pages half full, so it holds 2,631 pages. A rebuild fixes that, but the point stands: the heap pays 10 times more for the same count. The pictures come from a second server, which read 25,774 pages on the heap.

The numbers depend on how much a row grows. Any heap whose rows grow after the insert collects forwarded records.

Find the Forwarded Records

A cursor is not needed to find the heaps that need a rebuild. This query lists every heap of the current database with forwarded records and writes the fix statement for each. It does not run anything. The SAMPLED mode counts forwarded records, and it reads fewer pages than DETAILED does on a big table. The query still reads every index of the database, so run it in a quiet window on a big database. To look at one table, pass its object id and 0.

SELECT OBJECT_SCHEMA_NAME(ps.object_id) AS SchemaName, OBJECT_NAME(ps.object_id) AS TableName, ps.page_count, ps.record_count, ps.forwarded_record_count,
       CAST(100.0 * ps.forwarded_record_count / NULLIF(ps.record_count, 0) AS decimal(5,1)) AS ForwardedPct,
       CONCAT(N'ALTER TABLE ', QUOTENAME(OBJECT_SCHEMA_NAME(ps.object_id)), N'.', QUOTENAME(OBJECT_NAME(ps.object_id)), N' REBUILD;') AS FixStatement
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS ps
WHERE ps.index_id = 0 AND ps.forwarded_record_count > 0;

A rebuild of a heap rewrites the whole table. It also rebuilds every nonclustered index on it, because the row identifiers change. Run it in a quiet window on a big table.

ALTER TABLE dbo.TicketsHeap REBUILD;
GO
SELECT index_type_desc, page_count, record_count, forwarded_record_count FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'dbo.TicketsHeap'), 0, NULL, 'DETAILED');
SET STATISTICS IO ON;
SELECT COUNT(*) AS Tickets FROM dbo.TicketsHeap;
SET STATISTICS IO OFF;

The heap shrank from 1,492 to 1,389 pages, and the forwarded records are gone. The count reads 1,389 pages, against 25,782 before. The cure lasts only until rows grow again.

Ranges and Lookups Favor the Clustered Index

A heap has no order, so a range of ticket numbers needs a scan. The clustered table seeks to the start of the range and reads on.

SET STATISTICS IO ON;
SELECT COUNT(*) AS InRange FROM dbo.TicketsHeap WHERE TicketID BETWEEN 1000 AND 1100;
SELECT COUNT(*) AS InRange FROM dbo.TicketsClustered WHERE TicketID BETWEEN 1000 AND 1100;
SET STATISTICS IO OFF;

Both queries return 101 rows. The heap reads all 1,389 pages, and the clustered table reads 10. Sorted output, grouping on the key and range queries all work the same way. The key order saves a scan or a sort.

Where a Heap Wins

In the heap or clustered index decision, two cases favor the heap. A staging table takes a large load, is read once, and is emptied. Nothing needs the rows in order, and nothing updates them in place. A heap suits that work, because nothing needs the rows in order.

The second case is a wide clustering key. A nonclustered index on a clustered table carries the clustering key in every row. On a heap it carries the 8 byte row identifier. The next script builds both cases with a 36 character key and an index on City.

DROP TABLE IF EXISTS dbo.WideHeap;
DROP TABLE IF EXISTS dbo.WideClustered;
CREATE TABLE dbo.WideHeap (Code char(36) NOT NULL, City varchar(30) NOT NULL);
CREATE TABLE dbo.WideClustered (Code char(36) NOT NULL PRIMARY KEY CLUSTERED, City varchar(30) NOT NULL);
INSERT dbo.WideHeap (Code, City)
SELECT TOP (100000) CONVERT(char(36), NEWID()), 'City' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 500 AS varchar(10)) FROM sys.all_columns AS a CROSS JOIN sys.all_columns AS b;
INSERT dbo.WideClustered (Code, City) SELECT Code, City FROM dbo.WideHeap;
CREATE INDEX IX_WideHeap_City ON dbo.WideHeap (City);
CREATE INDEX IX_WideClustered_City ON dbo.WideClustered (City);
GO
SELECT OBJECT_NAME(i.object_id) AS TableName, i.name AS IndexName, ps.page_count
FROM sys.indexes AS i CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), i.object_id, i.index_id, NULL, 'LIMITED') AS ps
WHERE i.name LIKE N'IX[_]Wide%' ORDER BY TableName;

The same index takes 307 pages on the heap and 655 on the clustered table. With a narrow integer key the difference reverses. A 4 byte key is smaller than the 8 byte row identifier. A wide key is the case where a heap saves space.

You could argue that a heap also loads faster, because it keeps no order. That is plausible. Test your own load before you rely on it. The measurements here show the cost on the read side.

What to Remember

Choose a clustered index unless you can say why a heap fits. A heap fits staging tables and tables with a wide clustering key. Check heaps for forwarded records, and rebuild the ones where the share is high. The heap or clustered index choice is cheap to make once and costly to undo on a big table.

When you finish, run the cleanup script.

USE master;
GO
IF DB_ID(N'ForwardedHeapDemo') IS NOT NULL
BEGIN
    ALTER DATABASE ForwardedHeapDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE ForwardedHeapDemo;
END;

A heap is not a bad table, it is a table that has to earn the missing index.

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