How to alter a non clustered primary key constraint to clustered

  • Hello,

    I've a table with primary key defined as non-clusterd, now without dropping it can I modify the existing index to clustered.

    Thanks,

    Rohit

  • create table tutu (fid int)

    create nonclustered index ix_tutu_fid on tutu (fid)

    sp_helpindex tutu

    -- ix_tutu_fidnonclustered located on PRIMARYfid

    create unique clustered index ix_tutu_fid on tutu (fid)

    WITH (DROP_EXISTING = ON);

    sp_helpindex tutu

    -- ix_tutu_fidclustered, unique located on PRIMARYfid

    br,

    R

  • Thanks for your reply,

    Does the 'DROP EXISTING' works on primary key also i.e. if instead of the non clustered index, if I've created the primary key with non-clusteres index and then I'm trying to alter the primary key constraint to clustered. Will this work?

    Thanks

    Rohit

  • yes it does.

    amending the above example slightly.

    create table tutu (fid int constraint ix_tutu_fid primary key nonclustered )

    sp_helpindex tutu

    -- ix_tutu_fid nonclustered, unique, primary key located on PRIMARY

    create unique clustered index ix_tutu_fid on tutu(fid) WITH DROP_EXISTING

    sp_helpindex tutu

    -- ix_tutu_fid clustered, unique, primary key located on PRIMARY

  • Good stuff...thanks!

  • Thanks a lot for your time and help.......

    Rohit

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

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