A Table that doesn’t have a Clustered Index is referred to as a HEAP Table. We can write a query like below to get all the HEAP Tables or tables that doesn’t have Clustered Index:
SELECT T.Name 'HEAP TABLE' FROM sys.indexes I INNER JOIN sys.tables T ON I.object_id = T.object_id WHERE I.type = 0 AND T.type = 'U'
Let us understand this with an example:
CREATE DATABASE SqlHintsDemoDB GO USE SqlHintsDemoDB GO /*Let us create a Non-HEAP Table Customers Table with clustered and Non-clustered index.*/ CREATE TABLE dbo.Customers ( CustomerId int IDENTITY (1, 1) PRIMARY KEY CLUSTERED NOT NULL, FirstName Varchar(50), LastName Varchar(50)) GO CREATE NONCLUSTERED INDEX IX_Customers ON dbo.Customers(FirstName, LastName) GO /*Let us create a HEAP Table (i.e. table without any Clustered Indexes)*/ CREATE TABLE dbo.Orders ( OrderId int IDENTITY (1, 1) NOT NULL , CustomerId int NOT NULL , CreationDT DATETIME NOT NULL) GO
Now let us run the query to get all the HEAP Tables or tables that doesn’t have Clustered Index and verify the result: