Post reply

How to alter a non clustered primary key constraint to clustered

  • February 26, 2008 at 12:39 am

    #182557

    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

  • February 28, 2008 at 8:02 am

    #783186

    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

  • February 28, 2008 at 10:18 pm

    #783601

    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

  • February 28, 2008 at 11:22 pm

    #783608

    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

  • February 29, 2008 at 12:31 pm

    #783997

    Good stuff...thanks!

  • March 1, 2008 at 1:27 am

    #784128

    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