What is a heap table, and when is a table without a clustered index acceptable?
Quick Answer
A heap is a table with no clustered index — rows are stored in no particular order and located by a Row ID (RID). Heaps are fine for pure staging/bulk-load tables that are truncated and reloaded, or tiny lookup tables. For most real tables a narrow clustered index is better, because heaps suffer from forwarded records and can't do efficient range scans.
A heap is just a table with no clustered index — the rows aren't ordered and the engine finds them by a physical Row ID. They're acceptable for staging tables that you bulk-load and truncate, because inserting into a heap is fast, and for tiny lookup tables. But for a normal table I'd always add a clustered index, because heaps get forwarded records when rows grow and no longer fit their page — reads then have to follow pointers, and deletes don't free the space until you rebuild.
- Forwarded records — updating a row so it no longer fits its page leaves a “forwarding pointer”; reads then chase pointers, and
DELETEs don't reclaim space without a rebuild. - Non-clustered index lookups on a heap do RID lookups (similar cost to key lookups).
- Good for: ETL staging (bulk insert into a heap is fast, minimally logged), very small static tables.
- Bad for: anything with updates, range queries, or that acts as a foreign-key target — give it a clustered index.