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

sql sql-server sql-server-2005 primary-key clustered-index
skaeff picture skaeff · Jul 28, 2010 · Viewed 11.6k times · Source

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?

Answer

Pondlife picture Pondlife · Jul 28, 2010

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

Related questions

Add a column with a default value to an existing table in SQL Server

How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?

Read More
Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
SQL query to select dates between two dates

I have a start_date and end_date. I want to get the list of dates in between these two dates. Can anyone help me pointing the mistake in my query. select Date,TotalAllowance from Calculation where EmployeeId=1 and Date …

Read More