Given a table with a PRIMARY KEY definition (acting as the clustered index), I want to efficiently change the definition of the PRIMARY KEY (or at a minimum, just the clustered index) online.
I would love to simply do the following:
CREATE UNIQUE CLUSTERED INDEX PK_MyTable ON dbo.MyTable ( [Col2] ASC ) WITH (DROP_EXISTING = ON, ONLINE = ON);
However this results in an error:
Cannot recreate index 'PK_MyTable'. The new index definition does not match the constraint being enforced by the existing index.. And I understand why.
Is there another way to accomplish this?
For background: we have a table that is only queried via a nonclustered index. The characteristics of the that index are quite good to be the clustered index of the table (i.e. unique and unchanging narrow key, inserts are primarily at the "end"). The current PRIMARY KEY (the clustered index) is simply a surrogate key. The table is not the target of any FK relationships, and rows are never queried by the current PRIMARY KEY. We would like to efficiently change the clustered index online. We are running Enterprise edition.
If terms of "online" operations; at a minimum we want to continue inserting new rows, but we can temporarily suspend queries until the operations complete.
If it's not possible to do this with the existing table online, is there an approach (maybe involving a new table and copying the data) that could work with as little interruption as possible?