A discussion in the newsgroups before the holidays lead to trusted constraints and performance. This inspired me to blog about it, but I decided to have some vacation first :-). Instead of having one long article, I’ll do a two-part. This one is about non-trusted constraints in general, and another one will follow about non-trusted constraints and performance.
As you might know, we can disable check and foreign key constraints. This can be done when we create the constraint, for instance:
USE tempdb
CREATE TABLE t1(c1 int)
INSERT INTO t1(c1) VALUES(-1)
GO
ALTER TABLE t1 WITH NOCHECK ADD CONSTRAINT CK_t1_c1 CHECK(c1 > 0)
Above allow us to add the constraint even though we have rows that violates the constraint. The constraint isn’t disabled, but we (can) have rows in the table that violates the constraint – the constraint isn’t trusted. We can also disable an existing constraint:
USE tempdb
CREATE TABLE t2(c1 INT CONSTRAINT CK_t2_c1 CHECK(c1 > 0) )
INSERT INTO t2(c1) VALUES(1)
GO
ALTER TABLE t2 NOCHECK CONSTRAINT CK_t2_c1
GO
INSERT INTO t2(c1) VALUES(-1)
Again, we now have rows in the table that violates the constraint. For the first example, the constraint is enabled, but we didn’t check for existing rows when we added the constraint. If we try to add a row which violates the constraint, we get an error message. For the second example, the constraint isn’t even enabled. We can enable a disabled constraint:
ALTER TABLE t2 CHECK CONSTRAINT CK_t2_c1
GO
INSERT INTO t2(c1) VALUES(-1)
The immediate above INSERT command will fail with an error message. The constraint in table t2 is now enabled. But the constraint for both table t1 and table t2 are non-trusted. For table t1, we added the constraint with existing data, and told SQL Server to not check existing data. SQL Server cannot trust this constraint. For table t2, we disabled the constraint, added data, then enabled the constraint. SQL Server cannot trust the constraint because we might have added data which violates the constraint while the constraint was disabled. There’s an easy way to check whether you have non-tructed constraints. For instance, for check constraints:
SELECT OBJECT_NAME(parent_object_id) AS table_name, name
FROM sys.check_constraints
WHERE is_not_trusted = 1
Now, can we turn a non-trusted constraint into a trusted constraint? Yes. But we first need to get rid of offending data:
DELETE FROM t1 WHERE c1 < 0
DELETE FROM t2 WHERE c1 < 0
And now we want to make sure the constraints are trusted:
ALTER TABLE t1 WITH CHECK CHECK CONSTRAINT CK_t1_c1
ALTER TABLE t2 WITH CHECK CHECK CONSTRAINT CK_t2_c1
There’s no typo above. “WITH CHECK” is validate the data, and “CHECK CONSTRAINT” is to enable the constraint.
So, why would we want to bother with disabling and non-trusted constraints? The purpose of constraints is to make sure that we have consistent data. Why would we want to break this in the first place? Well, rarely. But here are a coule of possible scenarios where one could consider disabling constraints:
- We run some batch operation once a week. With constraint enabled, this takes 5 hours. With constraints disabled, it takes 1 hour. We “know” that the batch is written in a way so that it doesn’t violate any of our constraints.
- We want to expand the domain of allowable values for a column. Today we allow values Y and N. We will also allow value U for the column. This is implemented as a check constraint. We remove the existing constraint and add the new one (which also allow for U). We know that no of the existing rows cannot violate the new constraint since we expand the domain of allowable values. Adding the new constraint with NOCHECK is much faster.
Above examples might seem a bit … constructed. I haven’t encountered much non-trusted constraints in reality, but it has happended. And my initial goal was to talk about non-trusted constraints and performance, and this will come in the following blog post.