Moving from a non-clustered PK to a clustered PK in SQL 2005

Question

HI all, I recently asked this question in another thread, but was told it would be better to move it to a new question instead.

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.

Answer 1

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 re-creates all FKs with the option WITH NOCHECK to avoid trawling through all FKs to check constraint compliance. Lastly, I enabled 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 :)