4

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?

  • For your last sentence, I'm not sure what you mean by "is it efficient" - are you trying to do this efficiently, or do it online? Because that approach is certainly not online. – Aaron Bertrand Feb 18 '15 at 17:14
  • @AaronBertrand I guess I'm trying to say that ideally we want our applications to keep working (inserting and reading) with as little interruption as possible - so really fast and/or online. – Joseph Daigle Feb 18 '15 at 17:34
  • I think you can convert the PK to a normal unique CI using SWITCH PARTITION. Then, you can change the index structure any way you like. – usr Feb 18 '15 at 18:08
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!

  • 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. – Joseph Daigle Feb 19 '15 at 12:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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