Quick Scan Report – Missing Primary Keys

What this check looks for

User tables across every database with no primary key constraint. The message names the database, schema and table.

Why it matters

A table without a primary key has no declared way to identify a single row, and in most cases no clustered index either.

The integrity problem first. Without a primary key, nothing prevents duplicate rows. That sounds theoretical until an ETL process runs twice, or a retry inserts the same batch again, and now there are two of everything with no way to tell which is which. Deleting one of an exact duplicate pair requires a TOP (1) delete or a window function, which is a sign the table lost the argument long ago.

Then the practical consequences, which are where the real cost is:

• Heaps have forwarded records. When a row in a heap is updated and no longer fits on its page, SQL Server leaves a forwarding pointer and moves the row elsewhere. Every read of that row then follows two pointers. Forwarded records accumulate and are only cleared by a rebuild, and a heap with many of them performs far worse than its size suggests.
• No efficient seek. Without a clustered index, every lookup is either a table scan or a non clustered seek followed by a RID lookup.
• Non clustered indexes carry a RID rather than a clustering key, which is actually narrower, but the lookup back into the heap is less efficient in practice on an updated table.
• Replication requires a primary key for transactional publications. A table without one cannot be published.
• Change Data Capture and Change Tracking both need one.
• Many ORMs and data tools refuse to work with a keyless table, or work but cannot update a row.
• Availability group readable secondaries and many third party tools make the same assumption.

When a table legitimately has no primary key, and this is the exception the check description points at:

• A genuine staging table that is truncated and bulk loaded every run. A heap is faster to insert into, and nothing queries it by key. This is a real design and it is correct.
• A very large append only log table where inserts dominate overwhelmingly and reads are always by range scan over a non clustered index.
• A table being loaded right now, before its indexes are created.

The distinguishing question is whether anything updates the rows. A heap that is inserted into and scanned is fine. A heap that is updated accumulates forwarded records and degrades, and that is the case this finding is really about.

How to confirm it yourself

Tables with no primary key, in each database:

SELECT SCHEMA_NAME(t.[schema_id])                 AS [schema_name],
       t.[name]                                   AS [table_name],
       SUM(p.[rows])                              AS [rows],
       CAST(SUM(a.[total_pages]) * 8.0 / 1024 AS DECIMAL(12,1)) AS [size_mb],
       MAX(CASE WHEN i.[type] = 1 THEN 1 ELSE 0 END) AS [has_clustered_index],
       t.[create_date]
  FROM sys.tables            AS t WITH (NOLOCK)
 INNER JOIN sys.partitions   AS p WITH (NOLOCK) ON p.[object_id] = t.[object_id]
                                               AND p.[index_id] IN (0, 1)
 INNER JOIN sys.allocation_units AS a WITH (NOLOCK) ON a.[container_id] = p.[partition_id]
  LEFT JOIN sys.indexes      AS i WITH (NOLOCK) ON i.[object_id] = t.[object_id]
 WHERE t.[is_ms_shipped] = 0
   AND NOT EXISTS (SELECT 1 FROM sys.key_constraints AS kc WITH (NOLOCK)
                    WHERE kc.[parent_object_id] = t.[object_id] AND kc.[type] = 'PK')
 GROUP BY t.[schema_id], t.[name], t.[create_date]
 ORDER BY [rows] DESC;

has_clustered_index of 0 means it is a heap, which is the more consequential half of the finding. A table with a clustered index but no primary key constraint is a much smaller problem.

Forwarded records, which is the direct measurement of heap damage:

SELECT OBJECT_SCHEMA_NAME(ps.[object_id]) AS [schema_name],
       OBJECT_NAME(ps.[object_id])        AS [table_name],
       ps.[record_count],
       ps.[forwarded_record_count],
       CAST(ps.[forwarded_record_count] * 100.0
            / NULLIF(ps.[record_count], 0) AS DECIMAL(5,2)) AS [forwarded_pct],
       ps.[avg_page_space_used_in_percent]
  FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, 0, NULL, 'DETAILED') AS ps
 WHERE ps.[index_level] = 0
   AND ps.[forwarded_record_count] > 0
 ORDER BY ps.[forwarded_record_count] DESC;

Any material forwarded record percentage is a heap that should have been a clustered table.

Whether the table is actually updated, which is the deciding question:

SELECT OBJECT_NAME(ios.[object_id]) AS [table_name],
       ios.[leaf_insert_count],
       ios.[leaf_update_count],
       ios.[leaf_delete_count],
       ios.[range_scan_count],
       ios.[singleton_lookup_count]
  FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, 0, NULL) AS ios
 ORDER BY ios.[leaf_update_count] DESC;

High leaf_update_count on a heap is the combination to act on. Insert-only with range scans is the legitimate staging pattern.

And whether duplicates already exist, for a table you believe has a natural key:

SELECT [CustomerCode], COUNT(*) AS [copies]
  FROM [dbo].[Customers]
 GROUP BY [CustomerCode]
HAVING COUNT(*) > 1;

How to fix it

Add a primary key where the table has a genuine identifier. Where it does not, add a clustered index at least.

If a natural key exists and is unique:

-- confirm first
SELECT [OrderNumber], COUNT(*) FROM [dbo].[Orders]
 GROUP BY [OrderNumber] HAVING COUNT(*) > 1;

-- then declare it
ALTER TABLE [dbo].[Orders]
  ADD CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED ([OrderNumber]);

If no natural key exists, add a surrogate:

ALTER TABLE [dbo].[Orders] ADD [OrderID] INT IDENTITY(1,1) NOT NULL;

ALTER TABLE [dbo].[Orders]
  ADD CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED ([OrderID]);

Adding an identity column rewrites the table, so on anything large it is a maintenance window item.

If duplicates already exist, they have to go first:

WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY [OrderNumber] ORDER BY (SELECT NULL)) AS [rn]
      FROM [dbo].[Orders]
)
DELETE FROM ranked WHERE [rn] > 1;

Look at what the duplicates are before deleting them. Two rows with the same key and different data is a data quality problem with a history, not a mechanical cleanup.

Choose the clustering key carefully, because it is the most consequential index decision on the table. A good clustered index key is:

• Narrow, since every non clustered index carries it.
• Ever increasing, so inserts append rather than splitting pages.
• Unique, or SQL Server adds a uniquifier.
• Static, since updating it moves the row.

An INT IDENTITY satisfies all four, which is why it is the conventional choice. A random GUID satisfies none of them and is the usual cause of a badly fragmenting clustered index.

If the table is a deliberate heap, leave it and record why. A comment in the deployment script or an extended property saves the next person from re-raising this:

EXEC sys.sp_addextendedproperty
     @name = N'MS_Description',
     @value = N'Deliberate heap. Truncate and bulk load each run, never updated, '
            + N'read only by range scan on IX_StagingOrders_LoadDate.',
     @level0type = N'SCHEMA', @level0name = N'staging',
     @level1type = N'TABLE',  @level1name = N'StagingOrders';

And if a heap has to stay but has forwarded records, rebuilding it clears them:

ALTER TABLE [staging].[StagingOrders] REBUILD;

That is a maintenance item that recurs, which is itself an argument for a clustered index.

How long it takes

About half an hour to review. Adding a key to a large table, especially one needing a new identity column, is a maintenance window item per table.

Related reports

Related checks

Frequently asked questions

Is a heap always wrong? No. A staging table that is truncated, bulk loaded, scanned and never updated is a legitimate and efficient heap. The problem case is a heap that gets updated, because those accumulate forwarded records.

What are forwarded records? When an updated row no longer fits on its page in a heap, SQL Server leaves a pointer and moves the row. Every subsequent read follows two pointers. They only clear on a rebuild.

Should the primary key be the clustered index? Usually, and it is the default. If a different column is a better clustering key, declare the primary key as NONCLUSTERED and cluster on the other column deliberately.

Can I add a primary key without downtime? Adding a clustered primary key to an existing heap rewrites the table. On Enterprise Edition it can be done online; otherwise it needs a window, and adding a new identity column needs one either way.