Interview Question of the Week #062 – How to Find Table Without Clustered Index (Heap)?

Question: How to Find Table Without Clustered Index (Heap)?

Answer: In SQL Server, when we create a primary key it automatically creates clustered index on the table, unless we explicitly mentioned not to create one. Due to this reason, the most of the table where there is a primary key, there are good chances it is also a clustered index key.

There are always chances that tables do not have clustered index on it. Here is a quick script which can help us to find a table without a clustered index.

USE AdventureWorks2014 -- Replace AdventureWorks with your DBName
GO
SELECT DISTINCT [TABLE] = OBJECT_NAME(OBJECT_ID)
FROM SYS.INDEXES
WHERE INDEX_ID = 0
AND OBJECTPROPERTY(OBJECT_ID,'IsUserTable') = 1
ORDER BY [TABLE] 
GO

Here is a result of the above query executed on the Adventureworks database of SQL Server 2014.

A table without a clustered index, which is often called as the heap is not good for the performance of the database. Database with many heap tables, end up using very high critical resources like CPU, Memory and disk IO. It is always considered as a good idea to have clustered index on the table. The above script is useful to find all the tables without clustered index. Once you find them, you can create appropriate clustered index on it.

Reference: Pinal Dave (https://blog.sqlauthority.com)