Check constraints are useful for more than just verifying the data that goes into your tables. The query optimizer can use them to avoid looking for values that couldn’t possibly be there.

For example, imagine a listprice field with a constraint that keeps the field value over $100. If a query asks for the records with values under $100, the query optimizer can return the data without reading any records, just because of the constraint.

The problem is that the constraint can be disabled, and records can be inserted or updated in the meantime. SQL Server solves this problem by keeping a flag for each constraint, which states if the constraint is trusted or not.

The query optimizer only uses the constraint when it’s flagged as trusted. If it isn’t, the query optimizer won’t use it, and queries that look for values which don’t exist will have a higher cost.

Here’s an example you can try in your test environment, using NORTHWIND as a test database

CREATE TABLE  productTest    (  id INT IDENTITY(1, 1) ,

      productname VARCHAR(50) ,

      listprice DECIMAL       

— Constraint to check listprice field        CONSTRAINT chkprice CHECK ( listprice > 100 )    )

 GO

— import data from products table, but only valid records

INSERT  INTO productTest       

SELECT  productname ,

                unitprice

        FROM   products

        WHERE   unitprice > 100

Let’s check if the constraint is valid:

SELECT  name ,

        OBJECT_NAME(parent_object_id) “table” ,

        is_not_trusted

FROM    sys.check_constraints

If you check the execution plans for the following queries, you’ll see they’re different, even though the results are the same.

SELECT  productname ,

        listprice

FROM    productTest

WHERE   listprice = 120.00OPTION  ( RECOMPILE ) 

 SELECT  productname ,

        listprice

FROM    productTest

WHERE   listprice = 50.00OPTION  ( RECOMPILE )

The first one uses a clustered index scan to check if there are records with listprice $120. The second only needs a constant scan, because the constraint that there isn’t a value of $50 in the table.

The problem appears when you disable the constraint, perhaps because you want to import a lot of records that you’re already sure are valid:

ALTER TABLE productTest NOCHECK CONSTRAINT ALL

If you query the is_not_trusted flag again, you’ll notice that the constraint is already marked as not trusted, even before any records were inserted or updated.

It will remain untrusted, even if you enable the constraint again, because any new records haven’t been checked:

ALTER TABLE productTest CHECK CONSTRAINT ALL

To solve this problem, you need to enable the constraint again with the CHECK option. This means the constraint will immediately check all the records in the table:

ALTER TABLE productTest WITH CHECK CHECK CONSTRAINT ALL

Be sure to keep your constraints trusted, so the query optimizer can use them to avoid unnecessary reads.

Another Interesting stuff with help of Red-Gate