• Yeah, it's pretty likely that FKs are checking each constraint. You can look at the execution plan to confirm this, but I'm sure it's accurate. 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.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning