How to change the primary key to be non-clustered?
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.
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)