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

August 21, 2016

Question: When we drop primary key on the column, does it automatically drop a non-clustered index on the same column (if created)?

Before we see answer of this question, I suggest you read following three blog posts, they discuss various similar aspects related to the primary key and clustered index.

Answer: Yes! It is true. Well, the answer in one simple statement – “When you drop the primary key constraint on a column where there is a non-clustered index, it will drop the non-clustered index along the clustered index as well.“

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

Here is a small reproduction script.

First, let us create a sample table.

-- Create Table
CREATE TABLE Table1(
Col1 INT NOT NULL,
Col2 VARCHAR(100)
CONSTRAINT PK_Table1_Col1 PRIMARY KEY NONCLUSTERED (
Col1 ASC)
)
GO
-- Create Clustered Index on Separate Column
CREATE CLUSTERED INDEX IDX_Table1_CL ON dbo.Table1(Col2)
GO

Next let us check the primary key on the table with following script. Along with that we will also check indexes on the same table.

-- Check the Name of Primary Key
SELECT name  
FROM sys.key_constraints  
WHERE type = 'PK' 
		AND OBJECT_NAME(parent_object_id) = N'Table1'
GO

-- Check the Indexes
SELECT OBJECT_NAME(object_id),name
FROM sys.indexes 
WHERE OBJECTPROPERTY(object_id, 'IsUserTable') = 1
		AND OBJECT_NAME(object_id) = N'Table1'
GO

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

Now let us drop the primary key constraint. Please note that we are not dropping the non-clustered index, we are just dropping primary key constraint on the column where there is a non-clustered index.

-- Drop Primary Key Constraint
ALTER TABLE Table1
DROP CONSTRAINT PK_Table1_Col1
GO

Now, once again, let us run two scripts where we can see the primary key and clustered index.

-- Check the Name of Primary Key
SELECT name  
FROM sys.key_constraints  
WHERE type = 'PK' AND OBJECT_NAME(parent_object_id) = N'Table1'
GO

-- Check the Indexes
SELECT OBJECT_NAME(object_id),name
FROM sys.indexes 
WHERE OBJECTPROPERTY(object_id, 'IsUserTable') = 1
		AND OBJECT_NAME(object_id) = N'Table1'
GO

This time you can see that results are empty.

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

Let us clean up the created table for our experiment.

-- Clean up 
DROP TABLE Table1
GO

Summary: From the example it is very clear that when we drop the primary key constraint on a column where there is a non-clustered index, it will drop a non-clustered index on the table as well. However, if you have clustered index on the different table, it will remain as it is.

To get complete idea – please read following three blog posts on the same topic.

Let me know what you think about this blog post. Leave your favorite interview question in the comment and I will do my best to answer in the future blog posts.

Reference: Pinal Dave (https://blog.sqlauthority.com)