Sunday, January 1, 2012

Untrusted constraints

When developers want to load a lot of data quickly, they usual disable constraints and/or keys to get a better performance. By it self this is a good practice, But when they are done the only enable them again. Behind the scenes SQL Server will check them and everything looks good again. But for constraint the developers sometimes don't use the correct statement to do this.

They use:

ALTER TABLE MyTable WITH CHECK CONSTRAINT MyConstraint

This leaves the constraint in a state called untrusted. The bad part about this is, that this can have a big impact on the performance on queries. SQL Server doesn't use untrusted constraints in his query optimizer anymore.
The way to enable the constraints correctly and tell SQL Server to recheck all the data that has been loaded is:
ALTER TABLE MyTable WITH CHECK CHECK CONSTRAINT MyConstraint

To resolve this problem you can run my following script on your databases, it checks if there are untrusted constraints and creates the correct statement to fix the problem.
SELECT 'ALTER TABLE ['
     + db_name()
     + '].['
     + s.name
     + '].['
     + o.name
     + '] WITH CHECK CHECK CONSTRAINT '
     + i.name
  FROM sys.foreign_keys i
 INNER JOIN sys.objects o ON i.parent_object_id = o.object_id
 INNER JOIN sys.schemas s ON o.schema_id        = s.schema_id
 WHERE i.is_not_trusted = 1

SELECT 'ALTER TABLE ['
     + db_name()
     + '].['
     + s.name
     + '].['
     + o.name
     + '] WITH CHECK CHECK CONSTRAINT '
     + i.name
  FROM sys.check_constraints i
 INNER JOIN sys.objects o ON i.parent_object_id = o.object_id
 INNER JOIN sys.schemas s ON o.schema_id        = s.schema_id
 WHERE i.is_not_trusted = 1

No comments:

Post a Comment