Drop Clustred index on primary key

Question

Post reply

Drop Clustred index on primary key

2005_DBA

Mr or Mrs. 500

Points: 524
More actions
July 1, 2010 at 12:52 am

#224355

Hi Experts,
How can I drop clustered index on primary key.
what will be the impact. Clustered index is not used. This is index creted on primary key automatically.
Thanks in advance.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

You will have to drop the primary key and re-create it nonclustered.

The primary key and the associated index have to live together and cannot be dropped/created separately.

-- Gianluca Sartori

free_mascot One Orange Chip Points: 27169 More actions · Answer 2

The DROP INDEX statement does not apply to indexes created by defining PRIMARY KEY or UNIQUE constraints. To remove the constraint and corresponding index, use "ALTER TABLE" with the DROP CONSTRAINT clause.

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

2005_DBA (7/1/2010)
Hi Experts,
How can I drop clustered index on primary key.
what will be the impact. Clustered index is not used. This is index creted on primary key automatically.
Thanks in advance.

How did you determine that the clustered index is not used?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden