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.

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

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.

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

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.

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

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)

, , ,
Previous Post
Does NOLOCK Really Applies No Lock? – Interview Question of the Week #209
Next Post
Does Parallel Threads Process Equal Rows? – Interview Question of the Week #211

Related Posts

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?

    Reply
  • 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

    Reply

Leave a Reply

Menu