One of the most common questions asked in SQL Server interviews is whether dropping a primary key on a column automatically drops a clustered index on the same column. While many candidates may initially answer with a simple “yes” or “no,” the reality is that the relationship between primary keys and clustered indexes is more nuanced.

When you drop the primary key constraint on a column where there is a clustered index, it will indeed drop the clustered index along with the primary key constraint. This means that the column will no longer have a clustered index associated with it.

To better understand this concept, let’s take a look at a simple reproduction script:

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

-- 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 Clustered Index 
SELECT OBJECT_NAME(object_id),name
FROM sys.indexes 
WHERE OBJECTPROPERTY(object_id, 'IsUserTable') = 1
		AND type_desc='CLUSTERED'
		AND OBJECT_NAME(object_id) = N'Table1'
GO

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

-- 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 Clustered Index 
SELECT OBJECT_NAME(object_id),name
FROM sys.indexes 
WHERE OBJECTPROPERTY(object_id, 'IsUserTable') = 1
		AND type_desc='CLUSTERED'
		AND OBJECT_NAME(object_id) = N'Table1'
GO

-- Clean up 
DROP TABLE Table1
GO

In the above script, we first create a sample table called “Table1” with a primary key constraint on the “Col1” column. We then check the name of the primary key and the presence of a clustered index on the table.

Next, we drop the primary key constraint using the ALTER TABLE statement. It’s important to note that we are only dropping the primary key constraint, not the clustered index itself.

Finally, we run the same scripts again to check the name of the primary key and the presence of the clustered index. This time, the results will be empty, indicating that both the primary key constraint and the clustered index have been dropped.

From this example, it is clear that dropping the primary key constraint on a column with a clustered index will also drop the clustered index itself. This relationship between primary keys and clustered indexes is important to understand when designing and managing database tables.

Do you have any favorite interview questions related to SQL Server? Let me know in the comments, and I’ll do my best to answer them in future blog posts.