SQL SERVER – 2005 Find Table without Clustered Index – Find Table with no Primary Key

One of the basic Database Rule I have is that all the table must Clustered Index. Clustered Index speeds up performance of the query ran on that table. Clustered Index are usually Primary Key but not necessarily. I frequently run following query to verify that all the Jr. DBAs are creating all the tables with no Clustered Index. It returns each table without clustered index, so I can fix it.

USE AdventureWorks ----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

Result set for AdventureWorks:
TABLE
——————————————————-
DatabaseLog
ProductProductPhoto
(2 row(s) affected)

Related Post:
SQL SERVER – 2005 – Find Tables With Primary Key Constraint in Database
SQL SERVER – 2005 – Find Tables With Foreign Key Constraint in Database

What to Do When You Find a Table Without Clustered Index

The query works because every table without a clustered index, called a heap, has a row in sys.indexes with index_id 0. A table with a clustered index has index_id 1 instead, which makes the check simple and fast.

Note that no clustered index is not the same as no primary key. A table can have a primary key created as NONCLUSTERED and still be a heap. If you care about both, check both.

Heaps are not always wrong. Staging tables that are loaded, read once and emptied can work fine as heaps. The trouble starts with heaps that get many updates. When a row grows and no longer fits on its page, SQL Server moves it and leaves a forwarding pointer behind, and scans then need extra reads. You can see this in the forwarded_record_count column of sys.dm_db_index_physical_stats when you run it in DETAILED mode.

Before you add a clustered index, pick the key with care. A narrow, unique column that only grows, like an INT IDENTITY, is a good default. Also plan the timing: building a clustered index on a big table rewrites the whole table and can block users while it runs, so do it in a quiet window.

I also like to add the row count for each heap from sys.dm_db_partition_stats. Then I fix the big, busy tables first and leave small lookup tables for later, since they rarely cause trouble.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.