When working with SQL Server, it is important to understand the relationship between primary keys and non-clustered indexes. One common question that arises is whether dropping a primary key on a column automatically drops a non-clustered index on the same column. In this blog post, we will explore this concept and provide a clear answer to this question.

Before we dive into the answer, it is recommended to read the following three blog posts that discuss various aspects related to primary keys and clustered indexes:

• Does Dropping Primary Key Drop Clustered Index on the Same Column?
• Does Dropping Clustered Index Drop Primary Key on the Same Column?
• How to Drop Clustered Index on Primary Key Column?

The answer to the question is yes! When you drop the primary key constraint on a column where there is a non-clustered index, it will automatically drop the non-clustered index along with the clustered index. Let’s take a look at a small reproduction script to demonstrate this:

-- Create Table
CREATE TABLE Table1(
Col1 INT NOT NULL,
Col2 VARCHAR(100),
CONSTRAINT PK_Table1_Col1 PRIMARY KEY NONCLUSTERED (Col1 ASC)
)

-- Create Clustered Index on Separate Column
CREATE CLUSTERED INDEX IDX_Table1_CL ON dbo.Table1(Col2)

In the above script, we create a table called “Table1” with a primary key constraint on the “Col1” column and a non-clustered index on the “Col2” column. We also create a clustered index on a separate column for demonstration purposes.

Next, we can check the primary key and indexes on the table using the following script:

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

-- 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'

After executing the above script, we can see the name of the primary key and the indexes associated with the “Table1” table.

Now, let’s drop the primary key constraint on the “Col1” column:

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

After dropping the primary key constraint, we can once again check the primary key and indexes on the table:

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

-- 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'

This time, the results will be empty, indicating that the primary key and non-clustered index have been dropped.

It is important to note that if you have a clustered index on a different column, it will remain unaffected by dropping the primary key constraint.

In summary, when you drop the primary key constraint on a column where there is a non-clustered index, the non-clustered index will be dropped along with the clustered index. However, if you have a clustered index on a different column, it will remain intact.

Feel free to leave your favorite interview question in the comments, and I will do my best to answer it in future blog posts.