How to find Active tables without a clustered index

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.

Think before you add a Clustered Index

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

Query to find active tables without a Clustered Index

  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

Read more on Index Management

SQL Server - How to find the largest sql index and table size

SQL Server - Predict ALTER INDEX REORGANIZE finish time

SQL Server Disable Indexes and Rebuild Indexes dynamically

SQL Index Fragmentation and sys.dm_db_index_physical_stats

SQL Server - Index missing Statistics

SQL Server - List all indexes of all tables