Month: November 2012

SQL Server: A Gotcha with Disabling/Enabling Foreign Keys and Check Constraints

by

When loading a large dataset, you can significantly reduce the load time by disabling foreign keys and check constraints during the load process (and ETL’s often perform data integrity checks anyway).  This is commonly done with large data warehouses where entire dimensions are reloaded every day. When the load is complete, they are re-enabled, and SQL Server will ensure that the keys and constraints are honoured.  That is, as long as you re-enable them correctly. And due to the syntax, it is possible to re-enable them without forcing a re-validation of the data (in which case, they have the status of ‘not-trusted’).

Not only does this mean that referential integrity is not being enforced (which is a bad thing!), but it can also have a negative performance impact on queries, because SQL Server won’t use un-trusted constraints in order to build execution plans.

Run this TSQL to check if you have any foreign keys or check constraints that are not trusted:

-- Must be run in the database being examined:
USE MyDB
-- non trusted foreign keys
Select '[' + s.name + '].[' + o.name + '].[' + fk.name + ']' AS keyname 
from sys.foreign_keys fk 
INNER JOIN sys.objects o ON fk.parent_object_id = o.object_id 
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id 
WHERE fk.is_not_trusted = 1 AND fk.is_not_for_replication = 0 AND fk.is_disabled = 0

-- non trusted check constraints
Select '[' + s.name + '].[' + o.name + '].[' + c.name + ']' AS constraintname
from sys.check_constraints c 
INNER JOIN sys.objects o ON c.parent_object_id = o.object_id 
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id 
WHERE c.is_not_trusted = 1 AND c.is_not_for_replication = 0 AND c.is_disabled = 0
GO

To ensure that SQL Server not only re-enables a constraint, but also rechecks all the data, you have to specify the CHECK keyword twice:

ALTER TABLE MyTable 
    WITH CHECK CHECK CONSTRAINT MyConstraint
         ^^    ^^

Note: If the table is very large, you might want to perform this during scheduled maintenance.