It took forever to delete one record

Have you ever tried to delete a record from a table but it seemed to have taken forever? Furthermore, you checked everything and just couldn’t find any performance problems with the server.

Well, check again. Check the foreign key references on the primary key of the table. Now you see it? Yes, SQL Server was checking the foreign key constraints on every table big or small that references your primary key table. It would only delete the record(s) after it made sure that your statement would not introduce data integrity issues.

I am now going to use the AdventureWorks.HumanResources.Employee table to demonstrate how you can work around the delete performance issue.

Check table

Assuming you already have the table name in a query window, now highlight the table and press Alt+F1. It is the same as running the “sp_help” system proc on the table.

If you got the following error instead of proper results, make sure you surround the schema and table names using quotes or square brackets.

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.

Like this:

exec sp_help 'HumanResources.Employee'
exec sp_help "HumanResources.Employee"

exec sp_help [HumanResources.Employee]

You will get several result sets and pay attention to the one titled “Table is referenced by foreign key”.

image

Disable foreign key constraints

Copy the list of constraints to a new query window.

image

Replace the beginning of the lines with “alter table “.

image

Replace semi colons with ” nocheck constraint “.

image

Your script should look like the one below

image

By executing the script, you are effectively disabling the foreign key constraints on these table.

Delete the record(s)

Now you should be able to delete the desired records with a much faster response time(assuming you’ve got appropriate indexes and everything).

delete AdventureWorks.Sales.SalesPerson
where EmployeeID =  280

Re-Enable the foreign key constraints

We can re-enable the constraints by using the same script we’ve prepared earlier for disabling the foreign key constraints. We just need to replace “nocheck” with “with check check”.

image

In case if you are wondering why CHECK CHECK, here is the official explanation from Books Online:

The WITH CHECK CHECK CONSTRAINT re-enables the constraint, and also validates the existing data against the re-enabled constraint.

So it should be read like WITH CHECK (pause) CHECK CONSTRAINT.

Finally

I hope you find the above tip helpful. However, a word of warning though, this tactical operation is better reserved for the non production environments.