Sql-server – How to efficiently create a new PRIMARY KEY CLUSTERED definition online

clustered-indexprimary-keyschemasql server

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?

Best Answer

If you're running Enterprise edition, you can add and remove indices, including clustered indices, with ONLINE.

Since your existing PK isn't being used, you can start by dropping it. Then create your new clustered index, and finally drop your old non-clustered index which is now obsolete.

 ALTER TABLE MyTable DROP CONSTRAINT PK_MyTbl_SrgKey WITH (ONLINE=ON)
 ALTER TABLE MyTable ADD CONSTRAINT PK_MyTbl_BizKey PRIMARY KEY (X) WITH (ONLINE=ON)
 DROP INDEX IX_MyTbl_BizKey ON MyTable WITH (ONLINE=ON)

At no point will the biz key field be un-indexed, and there should be minimal blocking.

I'd guess you're already familiar with this syntax and you're hoping for something simpler or more atomic. Hopefully someone can offer this!

Related Question