Post reply

alter non-clustered index on primary key

  • July 14, 2008 at 10:31 am

    #103531

    I have a table (in test database which is part of production db) with nonclustered, unique, primary key located on PRIMARY key,

    i need to alter this to clustered index.

    To do this I have run this code:

    create unique clustered index index_name

    on table_name(primary_key_cloumn) WITH DROP_EXISTING

    While running this an error has occurred:

    Cannot convert a clustered index to a nonclustered index using the DROP_EXISTING option.

    But when i executed sp_helpindex i got this

    index_name nonclustered, unique, primary key located on PRIMARYprimary_key_column

    I am sure there is no clustered index on this table. Why am i getting this error. what should i do now?

    I have a copy of the same database on my local machine and I tried to run this code locally and i could able to change nonclustred index to clustered index. Why am i not able to do it on test server when i am able to do it locally?

    Suggest me on this.

  • July 14, 2008 at 12:06 pm

    #842603

    Double-check that there is no clustered index on the table.

    If need be, manually drop the non-clustered index, then create the clustered index that's supposed to replace it.

    Also double-check that you're replacing an existing index (same name on same table).

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • July 14, 2008 at 6:31 pm

    #842764

    I have nonclustered index on primary key and i am unable to drop it.

  • July 15, 2008 at 1:27 am

    #842854

    It's an index enforcing the primary key. To drop it, you will have to use ALTER TABLE ... DROP CONSTRAINT rather than DROP INDEX

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 15, 2008 at 1:37 pm

    #843379

    Thank You!

    it worked.

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

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