5

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?

0

1 Answer 1

3

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!

1
  • As you mentioned, I was hoping for something a little bit more all-in-one-go. It's not "fast", but your solution will work and it will keep everything online. Feb 19, 2015 at 12:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.