Can you speed up readding constraints to a table

Hi All,
I have created a number of stored procedure for archiving inactive records to another database. To do this I do the following:-
1. Remove the foreign keys and indexes from the tables I am going to archive (as the tables are large this is the fastest way)
2. Search for the inactive records, move them to the Archive database then delete from the original tables
3. Re-add the foreign keys and indexes to the tables.

My question is as follows, some of the tables are pretty large (>2 million records) and when I go to re-add the foreign keys it is pretty slow to do this. I am using the following to re-add the constraints
ALTER TABLE [FK_TBL] WITH CHECK ADD CONSTRAINT [Constraint name] FOREIGN KEY([FK_Column]) REFERENCES [PK_TBL]([PK_Col])

Is there a quicker way to readd the constraints to the table or is this the only way? I need to make sure that all the remaining Active information is not orphaned that is why I am using with check

Instead of dropping and recreating, can you use disable and enable using ALTER TABLE .. NOCHECK

Hi Skhanal,
I have tried disabling and enable the constraints but it is still slow and a lot of the tables have a lot of records so I found on some other sites that dropping and readding the constraints would be the fastest way for moving large amount of data.

If you are sure about data validity you can create the constraint with NOCHECK option. The alter the constraint to CHECK for new data.

How often is the archive process executed and how many records are moved (in average) to the archive db?
In case of a frequent archive process it might be better to approach archiving on a per record (or small batch) basis.

One note regarding the steps of the archiving process

Quote:

---

1. Remove the foreign keys and indexes from the tables I am going to archive (as the tables are large this is the fastest way)
2. Search for the inactive records, move them to the Archive database then delete from the original tables
3. Re-add the foreign keys and indexes to the tables.

---

It might be better to keep the indexes for spotting/marking the records to be archived (in case indexes help in doing so) and drop them just before deletion :
1. Mark records to be archived
2. Move records to archive db
3. Remove indexes and fk's
4. Delete archived records from the live db
5. Recreate indexes and fk's

--HTH--