A table without a primary key (clustered index) is referred to as a heap because it’s just a pile of unordered data. In contrast, a table with a clustered index is better structured into a balanced tree. The primary key itself is a clustered index and every table should have one.
An optimized table or query organized into a balanced tree performs much better within the data model when it comes to queries. Besides serving as the primary key, it also enforces the uniqueness of records. The above-mentioned is crucial for the proper functioning of a data warehouse.
How to Find a List of Tables Without a Primary Key?
To do this, we will use system tables. The list of indexes is stored in the sys.indexes table, and the list of tables is stored in the sys.tables table. Both tables can be linked through the object_id attribute.
SELECT
[Tab].[name] AS [Table]
FROM sys.tables [tab]
LEFT JOIN sys.indexes [ind]
ON [tab].[object_id] = [ind].[object_id]
WHERE [ind].[is_primary_key] <> 1
The sys.indexes table has an attribute called is_primary_key, so after joining, we can display tables that don’t have any primary key indexes in the table. Similarly, you can display tables that have or don’t have CLUSTERED/NONCLUSTERED indexes and so on.
If you are interested in the state of your indexes in terms of fragmentation, this information can be found in the sys.dm_db_index_physical_stats table.