SQL Code Smells: Heap Tables

You inherit a database and start digging through the schema. Everything looks reasonable on the surface until you notice a handful of tables with no clustered index. No obvious reason. They just never got one. In some cases they have been sitting that way for years, quietly degrading read and write performance while nobody thought to look.

Heap tables are one of those code smells that tend to accumulate in the background. They do not cause immediate errors, they do not throw warnings, and on small datasets they are nearly invisible. But as data grows, the cost becomes real.

What is a heap table?

In SQL Server, a table is stored as a heap when it has no clustered index. Without a clustered index, SQL Server has no concept of row order for that table. Data pages are allocated as rows come in, and SQL Server tracks the location of each row using an internal structure called a Row Identifier, or RID.

By contrast, a table with a clustered index stores its rows in the logical order of the index key. The index and the table data are the same structure. With a heap, the data sits in an unordered pile and every non-clustered index on that table has to store RID pointers back to the heap rather than clustering key values.

Heap tables are not a configuration mistake or a setting that got missed. They are simply tables where a clustered index was never created, often because the table was thrown together quickly or because the developer assumed SQL Server would handle ordering automatically.

Why does it exist in code?

Heap tables tend to appear for a few common reasons:

• A table was created with a CREATE TABLE statement and a primary key was never added. SQL Server does not require a primary key, and without one there is no clustered index by default.
• A table was created specifically for staging or bulk load operations where order did not matter at the time, and then it stayed in that state after the immediate need passed.
• A clustered index was deliberately dropped to speed up a bulk insert operation and was never rebuilt afterward.
• The schema was designed by someone unfamiliar with how SQL Server physically stores data, where it is easy to assume SQL handles row storage efficiently regardless of index structure.

In legacy systems in particular, heap tables can be surprisingly common. The application has been running for years, the queries still work, and there has never been a reason to look closely at how the underlying tables are structured.

Why is it a problem?

Heap tables create performance problems on both reads and writes, and the two problems have different root causes.

Read performance: table scans and RID lookups

Without a clustered index, SQL Server has no efficient way to locate a specific range of rows. Queries that would perform an index seek on a properly structured table instead require a full table scan, reading every data page regardless of how many rows actually match the condition.

Non-clustered indexes on heap tables help, but they come with their own cost. When a non-clustered index is used to find matching rows, SQL Server then has to follow a RID pointer from each index entry back to the heap to retrieve the full row. This is called a RID lookup, and it is roughly equivalent in cost to the key lookup problem you see with non-clustered indexes on clustered tables. On large heaps with many matching rows, the RID lookup overhead can make even a well-indexed heap perform poorly.

Write performance: forwarded records and fragmentation

Heap tables have a particularly damaging behavior when rows are updated and grow in size. If an updated row no longer fits on its original data page, SQL Server moves it to a new page and leaves a forwarding pointer in the original location. The original slot stays occupied but points elsewhere.

Over time, a frequently updated heap accumulates large numbers of these forwarded records. Every read that hits a forwarded record requires an extra I/O to follow the pointer to the new location. Fragmentation builds up, page density drops, and what should be a simple read becomes a chain of pointer follows across scattered pages.

Unlike clustered index fragmentation, which can be addressed with a rebuild or reorganize, heap fragmentation requires either rebuilding the heap explicitly or adding a clustered index to resolve properly.

When heap tables are actually fine

Not every heap table is a problem worth solving immediately. There are legitimate scenarios where a heap is the right choice or where the cost of adding a clustered index outweighs the benefit.

• Staging and ETL tables. Tables used purely for bulk loading data before transformation and truncation do not benefit meaningfully from a clustered index. The data is written once, processed, and discarded. The overhead of maintaining an index during the bulk load is real, and the performance gain during the brief read phase is minimal.
• Very small reference tables. A table with a few dozen rows that is read infrequently will be scanned regardless of whether a clustered index exists. The structural overhead of adding and maintaining an index on a table this small is not worth the effort.
• Append-only log or audit tables. Tables where rows are only ever inserted and never updated avoid the forwarded records problem entirely. If queries against these tables are always full scans by design, the absence of a clustered index has limited impact.

The key question to ask is whether the table is being queried in a way that would benefit from ordered storage, and whether rows are being updated in place. If both answers are no, the heap may be acceptable as-is.

How to find it with Database Health Monitor

Database Health Monitor gives you two direct ways to find heap tables in your environment.

The Unclustered Tables report

The Unclustered Tables report gives you a straightforward list of every table in a database that has no clustered index, sorted by size. This is the fastest way to get an inventory of heaps and prioritize which ones are worth addressing first. A 50-row staging table at the top of the list is a very different conversation from a 20 million row transaction table sitting without a clustered index.

Enterprise Index Review

The Enterprise Index Review provides a deeper look at heap tables as part of its full index health analysis. The Heap Tables section of the report lists tables with no clustered index along with their size, giving you context alongside the other index-related findings for that instance.

The value of finding heaps inside the Enterprise Index Review is the surrounding context. You can see at a glance whether a heap table also has missing index suggestions, unused non-clustered indexes, or fragmentation issues that compound the heap problem. A heap table with three non-clustered indexes and a stack of missing index recommendations is telling a much more urgent story than a heap table with no indexes at all.

Tip: Use the Unclustered Tables report to build your initial list, then cross-reference the larger tables in the Enterprise Index Review to see the full index picture around each one before deciding on a fix. The size column in the Unclustered Tables report is your best starting point for prioritization.

How to fix it

The standard fix for a heap table is to add a clustered index. In most cases this means adding a primary key, which by default creates a clustered index on the key column.

-- Add a primary key, which creates a clustered index by defaultALTER TABLE dbo.OrdersADD CONSTRAINT PK_Orders PRIMARY KEY CLUSTERED (OrderID)-- If a primary key already exists as non-clustered,-- add a separate clustered index on a more appropriate columnCREATE CLUSTERED INDEX CIX_Orders_OrderDateON dbo.Orders (OrderDate)

Choosing the right clustering key

The choice of clustering key matters. A good clustering key is narrow, unique or near-unique, and reflects the most common access pattern for that table. An identity integer or a date column used in most WHERE clauses are typical good choices. A wide string column or a GUID generated randomly makes a poor clustering key because it causes page splits on every insert and bloats every non-clustered index that references it.

Rebuilding an existing heap

If you cannot add a clustered index immediately but want to address forwarded record fragmentation in the short term, you can rebuild the heap to reclaim space and remove forwarded records:

-- Rebuild a heap to remove forwarded records and reclaim spaceALTER TABLE dbo.Orders REBUILD

This does not solve the underlying structural problem, but it clears accumulated forwarded records and can restore read performance temporarily while a longer-term fix is planned.

Find these in your environment today

Heap tables are easy to miss because they do not announce themselves. Queries still run, data still comes back, and nothing fails. The cost is paid gradually in slower reads, extra I/O from RID lookups, and write overhead from forwarded records that builds up over time.

What makes this smell worth catching early is that the fix gets significantly harder the longer you wait. Adding a clustered index or primary key to a small table is a trivial operation. Doing the same thing on a table with tens or hundreds of millions of rows is a different matter entirely. SQL Server has to physically reorganize every data page to impose the new order, which means the operation can take minutes, hours, or longer depending on table size, server load, and available disk space. During that time the table may be locked or have severely limited availability depending on your SQL Server edition and how the operation is run.

Certain features and configurations add further complexity to fixing heaps after the fact:

• Change Data Capture (CDC). If CDC is enabled on a heap table, adding a clustered index requires disabling CDC first, making the schema change, and then re-enabling and reconfiguring CDC afterward. On large tables in active environments this can be a significant and risky operation that needs careful planning and a maintenance window.
• Replication. Tables involved in transactional Replication require schema changes to be coordinated across the publisher and all subscribers. Adding a clustered index to a replicated heap is not a simple ALTER TABLE operation and may require removing the table from the publication and re-adding it after the change.
• Row-level security and triggers. These do not prevent the change, but they add surface area for things to go wrong during a large schema change and should be reviewed and tested carefully before proceeding.
• Availability Groups. In an AG environment, adding a clustered index to a large table will generate substantial log traffic that needs to synchronize across replicas. On a busy system this can cause replica lag and may trigger alerts or affect failover readiness during the operation.

None of these are reasons to avoid fixing heap tables. They are reasons to find them early, while the tables are still small enough that the fix is straightforward and the surrounding complexity is manageable.

The Unclustered Tables report and Enterprise Index Review in Database Health Monitor make it straightforward to get a complete picture of where heaps exist in your environment and how large they have grown. A few minutes in either report is usually enough to identify whether you have a heap table problem worth addressing.

Download Database Health Monitor free at DatabaseHealth.com and start finding code smells in your environment today.

Download Today!