How to Drop Clustered Index on Primary Key Column? – Interview Question of the Week #084

Question: How do you drop the clustered index that enforces a primary key?

Answer: Remove the primary-key constraint with ALTER TABLE … DROP CONSTRAINT. If you still need that primary key, recreate it as NONCLUSTERED as part of a planned change. DROP INDEX cannot directly remove its enforcing index.

The first two articles in this sequence established the relationship: dropping a primary key can remove its clustered index, while DROP INDEX is refused for that protected index. This question turns the observation into a deliberate change.

In the original Table1 experiment, both metadata queries returned PK_Table1_Col1:

After ALTER TABLE Table1 DROP CONSTRAINT PK_Table1_Col1, both queries were empty:

Those are useful historical results and they remain here. They also show the consequence that a brief answer can overlook: the example removed the primary key, not just a storage structure.

Keep the primary key when that is the goal

For a fresh sample table with no foreign keys, this example first reproduces error3723 and then changes the primary key from clustered to nonclustered in a transaction. It preserves the rows and the uniqueness rule:

IF @@TRANCOUNT <> 0
    THROW 50001, 'Run this demonstration outside an existing transaction.', 1;
IF OBJECT_ID('tempdb..#SqlaConvert39471') IS NOT NULL
    THROW 50001, 'The sample table already exists in this session.', 1;
CREATE TABLE #SqlaConvert39471
 (Col1 int NOT NULL, Col2 varchar(100) NULL,
  CONSTRAINT PK_Sqla39471 PRIMARY KEY CLUSTERED (Col1));
INSERT #SqlaConvert39471 VALUES (1,'One'),(2,'Two');
SELECT N'Before' AS Step, i.name, i.type_desc, i.is_primary_key
FROM tempdb.sys.indexes AS i
WHERE i.object_id = OBJECT_ID('tempdb..#SqlaConvert39471');
BEGIN TRY
    EXEC(N'DROP INDEX PK_Sqla39471 ON #SqlaConvert39471;');
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ExpectedError;
END CATCH;
-- Keep the PK rule while changing its physical implementation.
BEGIN TRY
    BEGIN TRANSACTION;
    ALTER TABLE #SqlaConvert39471 DROP CONSTRAINT PK_Sqla39471;
    ALTER TABLE #SqlaConvert39471 ADD CONSTRAINT PK_Sqla39471
        PRIMARY KEY NONCLUSTERED (Col1);
    COMMIT;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK;
    THROW;
END CATCH;
SELECT N'After' AS Step, i.name, i.type_desc, i.is_primary_key
FROM tempdb.sys.indexes AS i
WHERE i.object_id = OBJECT_ID('tempdb..#SqlaConvert39471');
SELECT Col1, Col2 FROM #SqlaConvert39471 ORDER BY Col1;
DROP TABLE #SqlaConvert39471;

Before the change, the enforcing index is CLUSTERED. After it, the table has a HEAP entry and a NONCLUSTERED primary-key index. The primary key still exists and the two data rows remain. The transaction is important because dropping the constraint alone would leave its rule absent.

Plan the real table’s dependencies

This small sample has no foreign keys. A referenced primary key cannot simply be dropped as shown while dependencies remain. For a real redesign, script those dependencies and constraint properties, plan locking and storage work, and verify the resulting definitions and data. Changing the clustering arrangement can also affect nonclustered indexes.

My original warning that every table must have both a clustered index and a primary key was too broad. Heaps are valid structures, and designs need to match their workload and integrity requirements. The point here is to retain the rules you intend while changing the index that implements them.

Tell me which follow-up question you would ask after this demonstration. Understanding what remains is at least as useful as knowing which DROP command succeeds.

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