17

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?

mavis
  • 3,100
  • 3
  • 24
  • 32
skaeff
  • 753
  • 2
  • 13
  • 25
  • I agree with @Demas but what edition of SQL Server are you on? (e.g. Enterprise/Standard). Do you have to worry about concurrent users? Do you have other non clustered indexes on the table? – Martin Smith Jul 28 '10 at 07:47

2 Answers2

20

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
Pondlife
  • 15,992
  • 6
  • 37
  • 51
3

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

ceth
  • 44,198
  • 62
  • 180
  • 289