Finding heap tables in your SQL Server database

Active tables without clustered indexes

What is a Heap in SQL Server table?

A table without a clustered index is called Heap.

It consists only of data pages.

By the way, this check is a part of our SQL Server Health Check service.

Note: You can have one or more nonclustered indexes this type of table.

Why should you care about them?

On tables without clustered and nonclustered indexes, SQL Server will do a table scan to find any row, degrading the query performance.

Neither the data pages nor the physical placement of the pages are guaranteed to be in any particular order.

All tables should have a clustered index, with rare exceptions (one reason could be a table that only has INSERTs, like log files).

How can you list all tables without a clustered index?

A heap is always identified by index_id = 0 in the catalog tables.

You can use the following query to list heap tables from one database.

  1. SELECT SCH.name + '.' + TBL.name AS TableName
  2. FROM sys.tables AS TBL 
  3. INNER JOIN sys.schemas AS SCH ON TBL.schema_id = SCH.schema_id 
  4. INNER JOIN sys.indexes AS IDX ON TBL.object_id = IDX.object_id AND IDX.type = 0 
  5. ORDER BY TableName
Figure 1 – Query output (Heap tables).

How can you fix them?

  1. For active tables, create the right clustering index.  (Sometimes, there is a primary key, and someone just forgot to set it as the clustered index).
  2. Start with tables that are used the most.

More information

Microsoft – Heaps (Tables without Clustered Indexes).

Elevate your SQL Servers with our managed services, performance tuning, and consulting services, tailored for companies seeking optimal database solutions.

Picture of Mark Varnas

Mark Varnas

Hey I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable. I channel my SQL into our SQL Managed Services, SQL Consulting and our internal database products.

Leave a Reply

Your email address will not be published. Required fields are marked *