Tables Without Clustered Indexes

Do all of your tables contain clustered indexes?  Some say every table should have a clustered index.  Others say it depends.  When databases are being designed and developed, your developers might have overlooked creating clustered indexes on some of your database tables.  Having a useful clustered index on your tables will improve the performance of your queries, especially if you are returning data periodically in order by the clustered index key.  Here is a simple script to identify those tables in your database that don’t have a clustered index. 

-- Tables without clustered indexes
SELECT TOP 1000, i.type_desc, o.type_desc, o.create_date
FROM sys.indexes i
INNER JOIN sys.objects o
ON  i.object_id = o.object_id
WHERE o.type_desc = 'USER_TABLE'
AND i.type_desc = 'HEAP'

See all articles by Greg Larsen

Gregory Larsen
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Latest Articles