6

I have a large table with a primary key (GUID) that is also the clustered index. There is already a integer sequence based field. So I want to leave the GUID as the PK and make the integer column the clustered index.

I can't figure any way to do this except drop the original constraint and create new PK and and new clustered index. But this takes a long time and from what I gather, rebuilds the table twice, once to go from clustered index to heap, and then heap back to clustered index.

I can't do a table rebuild (create new, migrate data, swap names) since I can't have an outage.

Any ideas?

Version: SQL Server 2008 Service Pack 2, Developer/Enterprise.

Improve this question
0

1 Answer 1

Reset to default
11

I don't think you can perform this operation online, since you can't just move the clustered index from one column to another, and you can't use DROP_EXISTING to reduce the amount of work that has to be done.

However, you can avoid an outage, if you're willing to perform the work.

  1. Create a new table with the new structure
  2. Rename the original table, and create a view with the original name - the view will be a union of old and new table - performance won't be fantastic but it won't be offline
  3. Create INSTEAD OF triggers on the view to apply DML to old or new table
    • inserts should just go to new table
    • updates should try both
    • deletes should try both
  4. Copy rows, in chunks, from the old table to the new table. Will need SET IDENTITY_INSERT ON and you should perform each "chunk" in its own transaction.
  5. Once the old table contains no rows that don't exist in the new table, in a single transaction:
    • drop the old table
    • drop the view
    • rename the new table
Improve this answer
6
  • Will this work if there are FKs referencing the table? (we can't reference a view and new inserts will not go into the original table. I guess a variation where inserting goes to both might work.)
    – ypercubeᵀᴹ
    Feb 11, 2015 at 19:27
  • @ypercube Yes, you could insert new rows into both tables and then as part of step 5 you change the FKs to point to the new table. Or you could just drop those constraints for the duration of this operation. Hopefully this is something that takes minutes/hours, not hours/days.
    – Aaron Bertrand
    Feb 11, 2015 at 19:29
  • Aaron is right, there's no way to do this without some outage. You could minimize the outage by swapping schema, and I think someone even blogged about it
    – Erik Darling
    Feb 11, 2015 at 21:05
  • Why can't he move the CI online "from one column to another" using DROP_EXISTING, ONLINE? Is there a rule preventing that?
    – usr
    Feb 14, 2015 at 9:52
  • @usr Yes, have you tried it? DROP_EXISTING is not an option for ADD CONSTRAINT, btw, so you can only replace a PK constraint with a unique clustered index. Down in the bowels it's the same thing, but semantics may be important. Anyway, trying this yields Msg 1907 : Cannot recreate index 'pk_whatever'. The new index definition does not match the constraint being enforced by the existing index.
    – Aaron Bertrand
    Feb 18, 2015 at 20:47

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.