changing clustered index to nonclustered on primary key via TSQL

[sodakotahusker]

I need to create a new clustered index on a table in a script. Unfortunately the primary key column was set up as a clustered index. I can not use the drop index statement on the primary key. How can I successfully get this new clustered index applied?

 

[Skittle]

I havn't tried this but I made a note of it ages ago because I thought it might come in handy.

CREATE INDEX MyIndex ON MyTable(Col1 ASC)
WITH DROP_EXISTING

My notes say it will drop the existing clustered index and create the new one.



Dazed and confused
(N+, MCAD)

 

[sodakotahusker]

I got this error. Cannot re-create index 'PK_tblClaimInvoice'. The new index definition does not match the constraint being enforced by the existing index.



PS: I replaced COL1 with the name of my column. I hope that was the correct thing to do?

 

[vongrunt]

What about dropping PK constraint instead of index (ALTER TABLE blah DROP CONSTRAINT PK_blah)? That should remove PK and it's clustered index.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[mrdenny]

As usual Vongrunt is correct. You need to drop the current primary key constraint, then recreate it as a nonclustered key constraint, then you can create your clustered index.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[sodakotahusker]

Thanks guys! This forum is the best one on any site that I have run into. I get the fastest and most accurate answers here!!