Question: Does dropping a clustered index also drop a primary key on the same column?
Answer: If that index enforces the primary key, SQL Server rejects an explicit DROP INDEX. If it is an independent clustered index and the primary key is nonclustered, the index can be dropped without removing the primary key, even when both use the same column.
This follows last week’s primary-key drop experiment. Reversing the question is useful. Dropping a constraint and trying to drop its enforcing index are not two commands for the same operation.
In the original example, PK_Table1_Col1 was both the primary-key constraint’s name and the name of its enforcing clustered index. DROP INDEX produced error3723: the index was being used for PRIMARY KEY constraint enforcement. Nothing was dropped.
Reproduce the refusal
This temporary-table version catches the expected error and checks what remains. The second case separates the clustered index from the nonclustered primary key:
IF OBJECT_ID('tempdb..#SqlaDrop39406') IS NOT NULL
OR OBJECT_ID('tempdb..#SqlaSeparate39406') IS NOT NULL
THROW 50001, 'A sample table already exists in this session.', 1;
DECLARE @States TABLE (Step nvarchar(80), PrimaryKeys int, ClusteredIndexes int);
CREATE TABLE #SqlaDrop39406
(Col1 int NOT NULL, Col2 varchar(100) NULL,
CONSTRAINT PK_Sqla39406 PRIMARY KEY CLUSTERED (Col1));
BEGIN TRY
EXEC(N'DROP INDEX PK_Sqla39406 ON #SqlaDrop39406;');
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ExpectedError, ERROR_MESSAGE() AS Message;
END CATCH;
INSERT @States
SELECT N'After rejected DROP INDEX',
(SELECT COUNT(*) FROM tempdb.sys.key_constraints
WHERE parent_object_id = OBJECT_ID('tempdb..#SqlaDrop39406') AND type = 'PK'),
(SELECT COUNT(*) FROM tempdb.sys.indexes
WHERE object_id = OBJECT_ID('tempdb..#SqlaDrop39406') AND type = 1);
CREATE TABLE #SqlaSeparate39406
(Col1 int NOT NULL, Col2 varchar(100) NULL,
CONSTRAINT PK_SqlaSeparate39406 PRIMARY KEY NONCLUSTERED (Col1));
CREATE CLUSTERED INDEX CX_SqlaSeparate39406 ON #SqlaSeparate39406(Col1);
INSERT @States
SELECT N'Independent index before DROP',
(SELECT COUNT(*) FROM tempdb.sys.key_constraints
WHERE parent_object_id = OBJECT_ID('tempdb..#SqlaSeparate39406') AND type = 'PK'),
(SELECT COUNT(*) FROM tempdb.sys.indexes
WHERE object_id = OBJECT_ID('tempdb..#SqlaSeparate39406') AND type = 1);
DROP INDEX CX_SqlaSeparate39406 ON #SqlaSeparate39406;
INSERT @States
SELECT N'Independent index after DROP',
(SELECT COUNT(*) FROM tempdb.sys.key_constraints
WHERE parent_object_id = OBJECT_ID('tempdb..#SqlaSeparate39406') AND type = 'PK'),
(SELECT COUNT(*) FROM tempdb.sys.indexes
WHERE object_id = OBJECT_ID('tempdb..#SqlaSeparate39406') AND type = 1);
SELECT * FROM @States;
DROP TABLE #SqlaDrop39406, #SqlaSeparate39406;
The first error result should report3723. Its state row still has one primary key and one clustered index. For the second table, the independent clustered-index count falls from one to zero while the primary-key count stays one. Both structures were defined on Col1, so the decisive point is ownership, not whether the columns differ.
Choose the operation you intend
If you actually want to remove a primary-key constraint and its enforcing index, use ALTER TABLE … DROP CONSTRAINT. If you want to keep uniqueness but change the physical arrangement, plan a constraint/index redesign, including dependencies. Do not treat a failed DROP INDEX as an invitation to discard the primary key.
Dropping an independent clustered index turns a rowstore table into a heap and can require work on its nonclustered indexes. A small demonstration makes the rule easy to see; it does not predict the duration or impact on a large production table.
The original “No” answer was right for its clustered-primary-key example. The more useful interview answer tells the interviewer which object SQL Server is protecting and why.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
18 Comments. Leave new
Hi Pinal,
I have a doubt here. We are not allowed to drop clustered index if it is on Primary Key by using SQL syntax DROP INDEX TableName.IndexName. However, We can do it from Object Brower Window. Just right click on the index and delete it. It will be deleted even if it is on primary key. And also, Primary key will also be deleted. So according to me answer should be ‘Yes’.
Hi,
We can create primary key as non-clustered index too, so answer is No. When we created Primary key index on a table, it will automatically create index on a table with same name (but work as a constraint). We cannot drop index individually, it will give below error as mentioned in post. For this we have to drop constraint. This will remove Primary Key constraint & index.
Dropping Clustered Index Drop Primary Key on the Same Column? – No
Dropping Primary Key Drop Index on the Same Column? – Yes
I have the same question too, We can still drop the Cluster index from SSMS(It will remove both cluster index and PK constraint). Its not possible only through Command.
Here is the answer to your question: http://blog.sqlauthority.com/2016/08/14/drop-clustered-index-primary-key-column-interview-question-week-084/
When you drop from SSMS, it generates the code to drop constraint which you can do it.
Hi Pinal,
You are correct, explicit drop is not allowed,
however, I right clicked on the index and select “Delete”. It actually allows me to delete the clustered index and it also removes primary key constraint.
So can I consider this, right click and deleting things from SQL is as good as drop command. If yes, we can drop clustered index.
Kindly provide your review.
Here is the blog post written on the subject to your question: http://blog.sqlauthority.com/2016/08/14/drop-clustered-index-primary-key-column-interview-question-week-084/
When you drop from SSMS, it generates the code to drop constraint which you can do it.
I think I got my answer,
I checked the script in separate window and I found it actually drops the constraint which then drops index as well.
Script is
ALTER TABLE [Emp]
DROP CONSTRAINT [PK_01]
GO
The question here is whether we can delete the index while retaining the primary key.
You can recreate, only non-clustered index if you want and keep that index. However, when you create a Primary Key with Clustered index, when you drop PK or Remove CL it removes both.
Here, like last week, the “key” is how the PK & CI are created — almost like a trick question… If they are created separately, one can be dropped without the other
— 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 Clustered Index
DROP index ci_Table1_Col1 on Table1
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
Thanks for your valuable input. It is so much fun to receive the details from you.
Thanks Pinal.
Can you please answer to the Shweta’s comment.
Here is the blog post which explains the next step – http://blog.sqlauthority.com/2016/08/14/drop-clustered-index-primary-key-column-interview-question-week-084/
Here is answer to your question: http://blog.sqlauthority.com/2016/08/14/drop-clustered-index-primary-key-column-interview-question-week-084/
When you drop from SSMS, it generates the code to drop constraint which you can do it.
I understood now. Thanks for the quick confirmation..
Hi Pinal,
Could you please clarify my doubt here? What I’m trying to achieve is retaining a primary key while removing the non clustered index created on it. And then create a clustered index on the same column. How can I do that?
Here is the sample queries I tried.
— Create Table
CREATE TABLE dbo.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
DROP INDEX IDX_Table1_CL ON dbo.Table1
GO–Removed clustured index IDX_Table1_CL successfully
SELECT name FROM sys.key_constraints WHERE type = ‘PK’ AND OBJECT_NAME(parent_object_id) = N’Table1′
SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N'[dbo].[Table1]’) AND name = N’PK_Table1_Col1′
/*Primary key and nonclustered Index are there*/
DROP INDEX PK_Table1_Col1 ON dbo.Table1
GO
–Getting following error here.
/*
An explicit DROP INDEX is not allowed on index ‘dbo.Table1.PK_Table1_Col1’. It is being used for PRIMARY KEY constraint enforcement.*/
Your assistance in this matter would be greatly appreciated.
ALTER TABLE [dbo].[Table1] DROP CONSTRAINT [PK_Table1_Col1]
GO
You can drop the clustered index without affecting the primary key if that clustered index is not associated with the primary key. If the clustered index is created separately, the creation of primary key will obviously create a nonclustered index. Dropping the clustered index then won’t affect the primary key. Moreover, if you drop the primary key, it will drop the nonclustered index created along with primary key and won’t affect the clustered index on the same column.