Question: What is Trusted Constraint in SQL Server?
Answer: I received this question during my recent SQL Server Performance Tuning Practical Workshop. I think it is a very interesting concept and I have seen many experts also do not know about this one. Instead of giving a definition, I am going to explain to you with a simple example.
First, let us create a sample table with a check constraint in it.
USE TempDB GO CREATE TABLE dbo.TestTable (ID INT NOT NULL, Column1 INT NOT NULL, CONSTRAINT Cons_Int CHECK (Column1 > 0) ) GO
Next, run the following command to check the constraint.
SELECT name, is_not_trusted FROM sys.check_constraints;
It will return us the following the result.
I really do not like the naming convention of is_not_trusted, I would have personally preferred is_trusted. However, I have no control to influence the name. Anyway, now run the following command where we will disable and enable the check constraint.
-- Disable Constraint ALTER TABLE dbo.TestTable NOCHECK CONSTRAINT Cons_Int; GO -- Enable Check Constraint Not Trusted ALTER TABLE dbo.TestTable CHECK CONSTRAINT Cons_Int;
When you run the above script it will give us the following the result.
The reason the value of the is_not_trusted is changed now 1 is because when we ran the query to enable the constraint we did not use the additional keyword of WITH CHECK.
If you run the following command once again the new constraint will be applied with the value is_not_trusted value as zero and in other words, it will create a trusted constraint.
-- Enable Check Constraint Trusted ALTER TABLE dbo.TestTable WITH CHECK CHECK CONSTRAINT Cons_Int;
When the trusted constraints are established, they actually check the pre-existed value in the table before the constraint is successfully established. If the constraint is not trusted, it does not check the value of the previous records, hence you can’t say that the value in the table column is valid for the constraint.
The trusted constraint can help with the performance of the query (in the execution plan), however, we will discuss this in the future blog posts.
Reference: Pinal Dave (https://blog.sqlauthority.com)
2 Comments. Leave new
Hi Pinal,
Do you have any quantitative data that shows the effect of having untrusted constraints on execution plans? For example, do logical reads go up with untrusted constraints versus having them all trusted? Do index seeks become index scans?
Hello Pinal,
Please check this post’s formatting. The datasets presented do not align with the text discussing them. They appear to be one paragraph too low.
Thank you