Post reply

Need to change the order of columns in the primary key nonclustered index

  • October 22, 2010 at 3:10 am

    #142250

    Hi,

    We have a table 'worksheet_cache_detail' with primary key non clustered index created on the columns with below order.

    detailid,

    worksheetpk and

    parentname.

    Now I need to change the order of columns in the index for better performance like

    worksheetpk

    parentname and

    detailid.

    When I tried to create the index with drop_existing clause it is throwing the following error.

    Msg 1907, Level 16, State 2, Line 1

    Cannot recreate index 'PK_WORKSHEET_CACHE_DETAIL'. The new index definition does not match the constraint being enforced by the existing index.

    Please help me on this.

    Thanks,

    Vamsy

  • October 22, 2010 at 3:18 am

    #1240779

    Drop any foreign keys referencing this table

    Drop the primary key

    Recreate the pk in the desired order

    Recreate the foreign keys

    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

  • October 22, 2010 at 3:56 am

    #1240797

    Thaks for your reply Gail..

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

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