Sept 2023 Discount: Comprehensive Database Performance Health Check | Testimonials

Does Dropping Primary Key Drop Clustered Index on the Same Column? – Interview Question of the Week #082

Pinal Dave
SQL Interview Questions and Answers
11 Comments

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

Answer: This is one of the most popular questions, whenever I ask this to any candidate, they usually give an answer in yes or no. However, if I ask them follow up question to confirm, I have noticed most of the time they change the reply or accept that they really do not know the answer. It is totally fine. As when I was beginning with SQL, I even did not know the answer of it. I learn doing various experiments and doing various interviews.

Does Dropping Primary Key Drop Clustered Index on the Same Column? - Interview Question of the Week #082 keyindex-800x285

Well, the answer in one simple statement – “When you drop the primary key constraint on a column where there is a clustered index, it will drop clustered index along the clustered index as well.“

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 CLUSTERED (
Col1 ASC)
)
GO

Next let us check the primary key on the table with following script. Along with that we will also check clustered index 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 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

Now let us drop the primary key constraint. Please note that we are not dropping the clustered index, we are just dropping primary key constraint on the column where there is a 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 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

This time you can see that results are empty.

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 clustered index, it will drop clustered index on the table as well.

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)

Clustered Index, SQL Constraint and Keys, SQL Index, SQL Server
Previous Post
How to Find Outdated Statistics? – Interview Question of the Week #081
Next Post
Does Dropping Clustered Index Drop Primary Key on the Same Column? – Interview Question of the Week #083

Related Posts

11 Comments. Leave new

  • Kevin Fries
    August 1, 2016 4:33 am

    Note: I pulled the code for the most part from one of Klaus Aschenbrenner’s posts.

    I’ll preface this by stating that in many of my systems, the primary key is a composite and not necessarily the same as the clustered key.

    This is my understanding of how it works. What you wrote was a tad confusing in that dropping a composite primary key (PK) or a PK on a single column will leave the clustered key intact if the clustered key does not have the same columns.

    I realize you know this but this part is for the other readers. A primary key is not necessarily the same key as the clustered key on a table. If you choose to use a unique identifier for the clustered index and specify the PK as nonclustered like the below:

    — Create the Primary Key constraint on an ever-increasing
    — key column
    CREATE TABLE Foo2
    (
    Col1 INT NOT NULL PRIMARY KEY NONCLUSTERED,
    Col2 UNIQUEIDENTIFIER NOT NULL,
    Col3 INT NOT NULL
    )
    GO

    — Create the Clustered Index on a random key column
    CREATE UNIQUE CLUSTERED INDEX ci_Col2 ON Foo2(Col2)
    GO

    Query sys.indexes and you’ll see the primary and clustered keys

    Then drop the primary key.

    ALTER TABLE [dbo].[Foo2] DROP CONSTRAINT [PK__Foo2__A259EE553CA2B97A]
    GO

    The clustered key will still exist.

    Thanks for giving me the many opportunities to learn from you. Now you also know that I give interviewers a hard time with their questions.

    Reply
    • Pinal Dave
      August 1, 2016 6:37 am

      Thanks for your comment.

      The discussion which I had in the blog post is in the same column, which is primary key and clustered index. This does not apply for any system where PK and CI are on different columns.

      This is actually the next part of the question which I am currently writing for the upcoming week.

      I appreciate adding it here.

      Reply
  • vaibhav shringi
    August 1, 2016 11:46 am

    Primary key is always bound to an clustered or non-clustered index thus dropping key will drop the index too. So your question and answer will only work if we are creating an primary key with clustered index (which is default for heap/new table).

    Note: We can have both clustered and non-clustered index on same column.

    Creation of primary key on a column of a table which already have a clustered index on the same column, will create a separate non-clustered index. So any future dropping attempts will only drop the new non-clustered index.

    Reply
  • Dave Poole
    August 4, 2016 9:26 pm

    I feel the terminology used is a bit confusing in this post.

    If the Primary Key is the clustered key then dropping the primary key will drop the clustered key because they are one and the same.

    If the Primary Key is not the clustered key and you have a separate clustered index on exactly the same columns then dropping the primary key does not drop the clustered index.

    IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=’dbo’ AND TABLE_NAME=’TestTable’)
    BEGIN
    DROP TABLE dbo.TestTable
    PRINT ‘DROPPED TABLE: dbo.TestTable’
    END
    GO
    CREATE TABLE dbo.TestTable (
    TestTableID INT NOT NULL IDENTITY(1,1)
    CONSTRAINT pk_TestTable PRIMARY KEY NONCLUSTERED,
    TestTableName varchar(50) not null
    constraint UQ_TestTable unique
    )
    GO
    SELECT
    TestName=’No Clustered index created yet’,
    Table_Name=OBJECT_NAME(object_id),
    IndexName=name,
    type_desc
    FROM sys.indexes
    WHERE OBJECTPROPERTY(object_id, ‘IsUserTable’) = 1
    AND OBJECT_NAME(object_id)=’TestTable’ AND OBJECT_SCHEMA_NAME(object_id)=’dbo’
    GO

    CREATE CLUSTERED INDEX idx_TestTable_Clustered ON dbo.TestTable(TestTableID)

    GO
    SELECT
    TestName=’Clustered index created’,
    Table_Name=OBJECT_NAME(object_id),
    IndexName=name,
    type_desc
    FROM sys.indexes
    WHERE OBJECTPROPERTY(object_id, ‘IsUserTable’) = 1
    AND OBJECT_NAME(object_id)=’TestTable’ AND OBJECT_SCHEMA_NAME(object_id)=’dbo’
    GO

    ALTER TABLE dbo.TestTable DROP CONSTRAINT pk_TestTable

    SELECT
    TestName=’Primary Key Dropped’,
    Table_Name=OBJECT_NAME(object_id),
    IndexName=name,
    type_desc
    FROM sys.indexes
    WHERE OBJECTPROPERTY(object_id, ‘IsUserTable’) = 1
    AND OBJECT_NAME(object_id)=’TestTable’ AND OBJECT_SCHEMA_NAME(object_id)=’dbo’
    GO

    Reply
  • Amit Srivastava
    August 12, 2016 12:09 pm

    Two Questions –
    1. What is the reason of drop of CLUSTERED INDEX on deleting PK?
    2. Is this applicable for Non CLUSTERED Index as well

    Reply
  • Pinal Dave
    August 17, 2016 1:57 pm

    Hi Amit,
    1) The question is answer here: https://blog.sqlauthority.com/2016/07/31/dropping-primary-key-drop-clustered-index-column-interview-question-week-082/#comment-1969011
    2) Yes, it does the same for non clustered index

    Reply
  • M McDonald
    August 24, 2016 4:04 pm

    Same comment as Dave Poole. However, this is a slight modification of your scripts…

    — Create Table and nonclustered PK
    CREATE TABLE Table1(
    Col1 INT NOT NULL,
    Col2 VARCHAR(100)
    CONSTRAINT PK_Table1_Col1 PRIMARY KEY NONCLUSTERED (
    Col1 ASC)
    )
    GO

    –Create clustered index
    create clustered index ci_Table1_Col1 on Table1
    (Col1)

    — 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

    The clustered index remains.

    Reply
  • haidder
    December 14, 2016 9:00 pm

    Hi Pinal, Hope you are well. One quick question, what is the best practice when you are getting data in multiple language and wanted to load in one data warehouse. I appreciate this question is not related to the above topic but hopefully, you can give your valuable opinion.
    Many thanks.
    Mustafa

    Reply
  • Avi
    September 26, 2017 5:24 pm

    “When you drop the primary key constraint on a column where there is a clustered index, it will drop clustered index along the clustered index as well.“

    “it will drop clustered index along the clustered index as well.“,Not able to get this,Clustered index along with clustered index?

    Reply

Leave a Reply

Idera

Pinal Dave is an SQL Server Performance Tuning Expert and independent consultant with over 21 years of hands-on experience. He holds a Masters of Science degree and numerous database certifications.

Pinal has authored 13 SQL Server database books and 53 Pluralsight courses. To freely share his knowledge and help others build their expertise, Pinal has also written more than 5,600 database tech articles on his blog at https://blog.sqlauthority.com.

Pinal is an experienced and dedicated professional with a deep commitment to flawless customer service. If you need help with any SQL Server Performance Tuning Issues, please feel free to reach out at pinal@sqlauthority.com.

Pinal is also a CrossFit Level 1 Trainer (CF-L1) and CrossFit Level 2 Trainer (CF-L2).

Nupur Dave is a social media enthusiast and an independent consultant. She primarily focuses on the database domain, helping clients build short and long term multi-channel campaigns to drive leads for their sales pipeline.

Comprehensive Database Performance Health Check

Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.

Once you learn my business secrets, you will fix the majority of problems in the future.

SQL Server Performance Tuning Practical Workshop

Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.

Essentially I share my business secrets to optimize SQL Server performance.