Question: How can I find all active tables but without a clustered index? I’m doing a review of databases to create a report for database owners.
Answer: An alternative way of calling a table without a clustered index is “heap”. In summary , they are scattered across the disk(s) in no order. The main benefit is for very fast inserts, for example in an ETL process requiring a staging table to commit some data clean up and then moving out again into the target permanent table.
Don’t just add a clustered index to every heap and hope for the best! Think about which tables require a clustered index , apply and test . Once in production monitor the performance and maintain the indexes effectively
SELECT DISTINCT so.name,so.type_desc,idx.type_desc FROM sys.indexes idx INNER JOIN sys.objects so ON idx.object_id = so.object_id INNER JOIN sys.partitions p ON idx.object_id = p.object_id AND idx.index_id = p.index_id LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON idx.object_id = ius.object_id AND idx.index_id = ius.index_id WHERE idx.type_desc = 'HEAP' AND COALESCE(ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates) IS NOT NULL AND so.type <> 'S' order by so.name