How to change the primary key to be non-clustered?

AngryHacker picture AngryHacker · Apr 9, 2010 · Viewed 32k times · Source

Part-time reluctant DBA here. I want to change an existing primary key index from clustered to non-clustered. And the syntax is escaping me.

This is how it's scripted out right now.

ALTER TABLE [dbo].[Config] WITH NOCHECK ADD 
    CONSTRAINT [PK_Config] PRIMARY KEY  CLUSTERED 
    (
        [ConfigID]
    )  ON [PRIMARY] 

I am not seeing an ALTER CONSTRAINT statement in the online docs.

Answer

Andomar picture Andomar · Apr 9, 2010

Drop the clustered index, then recreate the primary key as non-clustered:

ALTER TABLE dbo.Config DROP CONSTRAINT PK_Config
go
ALTER TABLE dbo.Config ADD CONSTRAINT PK_Config 
    PRIMARY KEY NONCLUSTERED (ConfigID)