How to alter a non clustered primary key constraint to clustered

• Ramji29

  Hall of Fame

  Points: 3832

  More actions

  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

• r.dragoi

  Right there with Babe

  Points: 788

  More actions

  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

• Ramji29

  Hall of Fame

  Points: 3832

  More actions

  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

• Nick M.

  Mr or Mrs. 500

  Points: 545

  More actions

  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

• Leeland

  SSCrazy Eights

  Points: 8959

  More actions

  February 29, 2008 at 12:31 pm

  #783997

  Good stuff...thanks!

• Ramji29

  Hall of Fame

  Points: 3832

  More actions

  March 1, 2008 at 1:27 am

  #784128

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

  Rohit