Dropping a Clustered Index

bizzyjunky

I'm trying to drop a clustered index for an existing table so that I can recreate it across various partitions(which should also automatically split the existing table across the partitions).

using the script below does not work. Any pointers as to how to do this?
drop index pk_vendors
on vendors with (online = on)

Error message I get is
Msg 3723, Level 16, State 4, Line 1
An explicit DROP INDEX is not allowed on index 'vendors.pk_vendors'. It is being used for PRIMARY KEY constraint enforcement.

Thanks

RobertKaucher

What you are essentially trying to do requires a table redesign. This is not a simple index you're dropping. You are infact removing the primary key. Remember that all PKs are clustered idexes by default.

You will need to either drop and recreat the table or find a way to use ALTER TABLE. I highly recommend that you do this 4 or 5 times on a test DB and check/recheck this before you perform this on the production database.

bizzyjunky

Thanks for the pointers. This is just practice im doing on a test lab. The book Im using to study advises on how to create partitions for 1:a table im about to create and 2: existing tables.

option 1 worked fine so I decided to also have a go at option 2 but seems like its not straight forward since this is something that is usually considered at the design stage.

RobertKaucher

In that case export your data, right click the table and do a "script table as -> Drop and create to -> New Query" and then just modify the T-SQL the way you want it. Then you can bulk import the data.

But no, not a simple little task a DB a would normally do.