What is Trusted Constraint in SQL Server? – Interview Question of the Week #210

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)