Grant Fritchey (3/28/2014)
If you are doing very large scale deletes, or inserts, it's fairly common to drop the foreign keys first. You just have to make sure you have good scripts that won't leave orphans and, when you recreate the FKs, you use the WITH CHECK option.But, again, the way to limit locking and blocking is to reduce the number of rows being deleted at one time. And the way to do that is not to use ROWCOUNT, but to filter the records through the WHERE clause in some other fashion. On 2000, you have a lot fewer options than if you were on 2005 or better.
1) Ok, Say i am droppping foreign keys and start deleting the records,what will happen in the middle of delete , if the server is broken, how to reenable the Foreign keys .
2) While running the Delete statement if i stop the query how to reenable the foreign keys(As you said Orphans) any links for that to restore orphans.
I using SQL Server 2000 only plan to migrate to sql 2005 in middle of next year until that i have to use SQL 2000.So finding best way to deal with delete statements.