Menu
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.
413
In case we want to create a nonclustered primary key, we have to explicitly define it during primary key creation. As we know that a table can has only one clustered index created on it. Because clustered index orders the table data as per it's key and thus we can order a table in only one way.
Yes you can, by specifying the primary key be nonclustered.
Taking all this into consideration, you cannot alter an index to make it clustered. However, you can create a new one so long as one does not already exist. Either that or drop the clustered index, create your new clustered index and then create your old clustered index as a non clustered index.
The answer is NO. It is not possible at all. If I have to say in the most simple words, Primary Keys exists with either Clustered Index or Non-Clustered Index.
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
