Does Dropping Primary Key Drop Non-Clustered Index on the Column? – Interview Question of the Week #085

Question: When we drop a primary key, does SQL Server also drop a nonclustered index on that column?

This is the next question in my small series about primary keys and indexes. The precise answer depends on which nonclustered index you mean.

SQL Server creates a unique index to enforce a primary key. If that primary key is nonclustered, dropping its constraint removes that supporting nonclustered index. A separate index that you created with CREATE INDEX is a different object and remains. A clustered index on another column remains too.

My original experiment showed the first distinction clearly. It had a nonclustered primary key on Col1 and a clustered index on Col2:

Those screenshots do not show an independently created nonclustered index on Col1. To test that part of the interview question, run this additional experiment in a scratch database:

IF OBJECT_ID(N'dbo.IQ085_Demo', N'U') IS NOT NULL
    THROW 50001, 'Demo table already exists. Use a scratch database.', 1;

CREATE TABLE dbo.IQ085_Demo
(
    Col1 int NOT NULL,
    Col2 int NOT NULL,
    CONSTRAINT PK_IQ085_Demo PRIMARY KEY NONCLUSTERED (Col1)
);

CREATE CLUSTERED INDEX CX_IQ085_Demo
    ON dbo.IQ085_Demo (Col2);

CREATE NONCLUSTERED INDEX IX_IQ085_Independent
    ON dbo.IQ085_Demo (Col1);

SELECT name, type_desc, is_primary_key
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'dbo.IQ085_Demo')
  AND index_id > 0
ORDER BY name;

ALTER TABLE dbo.IQ085_Demo DROP CONSTRAINT PK_IQ085_Demo;

SELECT name, type_desc, is_primary_key
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'dbo.IQ085_Demo')
  AND index_id > 0
ORDER BY name;

DROP TABLE dbo.IQ085_Demo;

Before the ALTER TABLE, the catalog should list the clustered index, the independent nonclustered index, and the nonclustered primary-key index. Afterward, only the primary-key index is removed. Keep the two objects separate in your answer; sharing a column does not make an independent index part of the constraint.

If you are working through this sequence, also read my questions about dropping a primary key and its clustered index, dropping a clustered index under a primary key, and the correct way to remove that clustered index.

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