How to Create Primary Key Without ANY Index? – Interview Question of the Week #143

Can I create a primary key without any index in SQL Server? Read without any index twice. The question isn’t asking whether the index must be clustered.

A primary key enforces a unique, non-null value for every row. SQL Server backs that constraint with a unique index. On a table that has no clustered index, the default primary-key index is clustered. You can choose NONCLUSTERED instead, or SQL Server will use a nonclustered backing index when the table already has a clustered index.

Try both index types

These are the two tables from my original interview example. Run them in a disposable lab database, since they create permanent sample tables.

IF OBJECT_ID(N'dbo.TestTable', N'U') IS NOT NULL
 OR OBJECT_ID(N'dbo.TestTable1', N'U') IS NOT NULL
    THROW 50137, 'Lab table exists. Choose a new private lab database.', 1;

CREATE TABLE dbo.TestTable
(
    ID int NOT NULL PRIMARY KEY,
    Col1 int NOT NULL
);

CREATE TABLE dbo.TestTable1
(
    ID int NOT NULL PRIMARY KEY NONCLUSTERED,
    Col1 int NOT NULL
);

The original SSMS Object Explorer capture shows the primary key and its corresponding index in both cases. One index is clustered; the other is unique and nonclustered.

You can also inspect the catalog rather than relying on Object Explorer. unique_index_id connects each constraint to its backing index:

SELECT t.name AS TableName,
       kc.name AS PrimaryKeyName,
       i.name AS BackingIndexName,
       i.type_desc AS IndexType
FROM sys.key_constraints AS kc
JOIN sys.tables AS t
  ON t.object_id = kc.parent_object_id
JOIN sys.indexes AS i
  ON i.object_id = kc.parent_object_id
 AND i.index_id = kc.unique_index_id
WHERE kc.type = 'PK'
  AND t.name IN (N'TestTable', N'TestTable1');

The answer is no: SQL Server does not keep a primary-key constraint with no backing index. A clustered index is not mandatory, but some unique index is. DROP INDEX cannot separately remove an index created to enforce a primary-key constraint. Dropping the constraint removes its index too.

That distinction is why I like this interview question. A candidate who says “make it nonclustered” has changed the index type, not removed the index. What other wording would you use to test the same idea?

References: Microsoft primary-key constraints and DROP INDEX restrictions.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.