CODEWITHSUNDEEP
sqlsql-serversql-server-2005primary-keyclustered-index
skaeff
skaeff

Reputation: 753

How to drop clustered property but retain primary key in a table. SQL Server 2005

i have the following key:

ALTER TABLE dbo.Table ADD  CONSTRAINT PK_ID PRIMARY KEY CLUSTERED 
(
ID ASC
)

so i have clustered index and primary key on ID column. Now i need to drop clustered index (i want to create new clustered index on another column), but retain primary key. Is it possible?

Upvotes: 17

Views: 14492

Answers (2)

Pondlife
Pondlife

Reputation: 16260

It's not possible in one statement, but because DDL is transactional in MSSQL, you can simply do everything inside a transaction to prevent other sessions accessing the table while it has no primary key:

begin tran
alter table dbo.[Table] drop constraint pk_id
alter table dbo.[Table] add constraint pk_id primary key nonclustered (id)
commit tran

Upvotes: 20

ceth
ceth

Reputation: 45325

It is not possible, as the index is a physical implementation of the constraint.

Upvotes: 3

Related Questions