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

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.

Solarwinds

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)

Solarwinds
, , ,
Previous Post
How to Drop Clustered Index on Primary Key Column? – Interview Question of the Week #084
Next Post
How to Get Started with SQL Server 2016? – Interview Question of the Week #086

Related Posts

2 Comments. Leave new

  • In nutshell – Any index that gets created automatically with the primary key (non clustered or clustered), gets dropped when you drop the primary key constraint

    Reply
  • Many of your articles have helped me solve real world problems. Keep it coming.
    Nice article Pinal. in your summary your said “However, if you have clustered index on the different table,” Do you meant to say “different column”.

    Reply

Leave a Reply

Menu