Post reply

Dropping a Clustered Index

  • June 3, 2010 at 1:22 pm

    #225341

    Im new to sql server and practicing using it on a home lab.

    I'm trying to drop a clustered index for an existing table so that I can recreate it across 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? I have also tried to change the index to non-clustered to see if i could then recreate a clustered one. I'm hoping I wont have to take off the primary key constraint as the table is a foreign key in aother table.

    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

  • June 3, 2010 at 2:26 pm

    #1176191

    Since it is a PK (not just an index), you will have to drop it. Try this:

    ALTER TABLE dbo.Vendors DROP CONSTRAINT PK_Vendors

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • June 3, 2010 at 2:38 pm

    #1176201

    Well, unfortunately you are going to have to drop the constraint. The constraint is enforced using the index and the index cannot be dropped until the constraint has been removed.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

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

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