One common question that often arises when working with SQL Server is whether dropping a clustered index on a column automatically drops the primary key on the same column, if it exists. In this blog post, we will explore this topic and provide a clear answer to this question.

Before we proceed, it is recommended to read our previous blog post on the related topic: “Does Dropping Primary Key Drop Clustered Index on the Same Column?”. This will provide a better understanding of the concepts discussed here.

The answer to the question is simple – No, dropping a clustered index does not automatically drop the primary key on the same table. However, there are certain conditions that need to be met in order to manipulate these indexes.

Let’s take a look at a quick demonstration to understand this better. First, we will create a sample table:

CREATE TABLE Table1(
    Col1 INT NOT NULL,
    Col2 VARCHAR(100),
    CONSTRAINT PK_Table1_Col1 PRIMARY KEY CLUSTERED (Col1 ASC)
);

Next, we can check the primary key and clustered index 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 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';

Now, let’s try to drop the clustered index without dropping the primary key constraint:

-- Drop Clustered Index
DROP INDEX PK_Table1_Col1 ON Table1;

When we attempt to drop the clustered index, it will throw an error:

Msg 3723, Level 16, State 4, Line 26
An explicit DROP INDEX is not allowed on index 'Table1.PK_Table1_Col1'. It is being used for PRIMARY KEY constraint enforcement.

In simple terms, we cannot drop a clustered index if it is associated with a primary key. To drop the clustered index, we must first remove the primary key constraint.

Understanding the relationship between clustered indexes and primary keys is crucial when working with SQL Server. By following the correct sequence of steps, you can effectively manage and manipulate these indexes to optimize your database performance.

We hope this blog post has provided you with a clear understanding of the concepts related to dropping clustered indexes and primary keys in SQL Server.