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.

   [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.

Rate this post

Ing. Jan Zedníček - Data Engineer & Controlling

My name is Jan Zedníček and I have been working as a freelancer for many companies for more than 10 years. I used to work as a financial controller, analyst and manager at many different companies in field of banking and manufacturing. When I am not at work, I like playing volleyball, chess, doing a workout in the gym.

🔥 If you found this article helpful, please share it or mention me on your website

Leave a Reply

Your email address will not be published. Required fields are marked *