Moving from a non-clustered PK to a clustered PK in SQL 2005
- by adaptr
HI all, I recently asked this question in another thread, and thought I would reproduce it here with my solution:
What if I have an auto-increment INT as my non-clustered primary key, and there are about 15 foreign keys defined to it ? (snide comment about original designer being braindead in the original :) )
This is a 15M row table, on a live database, SQL Standard, so dropping indexes is out of the question. Even temporarily dropping the foreign key constraints will be difficult.
I'm curious if anybody has a solution that causes minimal downtime.
I tested this in our testing environment and finally found that the downtime wasn't as severe as I had originally feared.
I ended up writing a script that drops all FK constraints, then drops the non-clustered key, re-creates the PK as a clustered index, and finally re-created all FKs WITH NOCHECK to avoid trawling through all FKs to check constraint compliance. Then I just enable the CHECK constraints to enable constraint checking from that point onwards, and all is dandy :)
The most important thing to realize is that during the time the FKs are absent, there MUST NOT be any INSERTs or DELETEs on the parent table, as this may break the constraints and cause issues in the future.
The total time taken for clustering a 15M row, 800MB index was ~4 minutes :)
