What is a heap table?
A table without a clustered index is called heap.
It consists only of data pages.
Note: You can have one or more non-clustered indexes though.
Why should you care about heaps?
When query is run on a heap table, SQL Server will do a table scan to find any row, which makes query performance terrible.
Why? Because the physical placement of the pages are not guaranteed to be in any particular order. What does that mean? Its a time consuming spaghetti. And not neatly organized data structure.
By the way, All (most, not all) tables should have a clustered index.
Rare exceptions exist. One of them is a table that only has a lot of INSERTs. Log table could be a good example.
I have seen tables that process Terabytes of inserts each hour. That table you want to be a Heap.
How can you find all tables missing 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 a single database:
SELECT SCH.name + '.' + TBL.name AS TableName
FROM sys.tables AS TBL
INNER JOIN sys.schemas AS SCH ON TBL.schema_id = SCH.schema_id
INNER JOIN sys.indexes AS IDX ON TBL.object_id = IDX.object_id
AND IDX.type = 0
ORDER BY TableName
How can you fix the SQL Server heap table?
- 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). - Start with tables that are used the most.
