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

Question

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.

Solution

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)

Comments

[Bruno]

I understand this is an old post, however it is interesting to point out that, if the index is being used together with a primary key and it has been already referenced by another table, you won't be able to DROP it. (At least in MS SQL Server)

[Kavya Shetty]

@Bruno Is there an alternative to change the primary key to non-clustered if the index is being used together with a primary key and it has been already referenced by another table in MSSQL.

[simon coleman]

@KavyaShetty Yes, use the answer from stackoverflow.com/questions/483193/… to identify the referencing fkeys. And this to disable them (but choose a selective approach rather than the 'all' approach in the answer) stackoverflow.com/questions/159038/… Re-enable when you have put the pkey back on... One commenter there usefully notees to check for triggers too!