THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SELECT Hints, Tips, Tricks FROM Hugo Kornelis WHERE RDBMS = 'SQL Server'

Can you trust your constraints?

SQL Server allows you to temporarily disable your CHECK and FOREIGN KEY constraints. I would not recommend this as part of your regular production schedule, but there are certainly cases where this comes in handy.

 

One example is if you’re copying thousands of rows from another table, and you already know with 100% certainty that the data doesn’t violate any constraint. The overhead of checking could impact performance of your process; in the worst case making the execution time exceed your maintenance window. Disabling the constraints can result in a tremendous reduction of execution time.

 

Another example would be the case where you’re importing lots of data from a third party; the raw data might violate some constraints, but these errors are all corrected as part of the process before the constraints are re-enabled. I would personally prefer to import the raw data to a staging table, do the cleansing there and only then copy the data to the production table – which of course takes us back to the first example J.

 

However, there is a large caveat that many people seem to be unaware of – if you don’t take care while re-enabling the constraints, you might end up disabling the query optimizer’s potential to generate the best possible execution plan for your queries!

 

Let’s first look at the abridged syntax for disabling end re-enabling a constraint:

 

ALTER TABLE <tablename>

      NOCHECK CONSTRAINT <constraintname>;

 

ALTER TABLE <tablename>

      WITH { CHECK | NOCHECK }

      CHECK CONSTRAINT <constraintname>;

 

The first syntax disables a constraint and the second syntax re-enables it. Note the “WITH {CHECK | NOCHECK}” clause. Specifying WITH CHECK signifies to SQL Server that you want it to check the re-enabled constraint for all rows in the table; if one or more fail to satisfy the constraint, the ALTER TABLE command fails. Specifying WITH NOCHECK (the default for existing constraints) means that existing rows are not checked. This is of course faster, but it has a severe side effect that you should really be aware of: you may know with 100% certainty that all rows in the table still abide by the constraint, but SQL Server doesn’t know this. As soon as you enable a constraint without checking the existing rows, SQL Server will mark the constraint as “not trusted”. This means that the query optimizer will no longer use it’s knowledge of the constraint to optimize your queries.

 

To see the effects of this, I set up a simple test with one table, three columns, and a CHECK constraint:

 

CREATE TABLE dbo.Test

      (KeyColumn int NOT NULL,

       CheckColumn int NOT NULL,

       LongColumn char(4000) NOT NULL,

       CONSTRAINT PK_Test PRIMARY KEY (KeyColumn),

       CONSTRAINT CK_Test CHECK (CheckColumn > 0)

      );

INSERT INTO dbo.Test (KeyColumn, CheckColumn, LongColumn)

SELECT 1, 1, REPLICATE('a', 4000)

UNION ALL

SELECT 2, 2, REPLICATE('b', 4000)

UNION ALL

SELECT 3, 3, REPLICATE('c', 4000)

UNION ALL

SELECT 4, 4, REPLICATE('d', 4000)

UNION ALL

SELECT 5, 5, REPLICATE('e', 4000)

UNION ALL

SELECT 6, 6, REPLICATE('f', 4000)

UNION ALL

SELECT 7, 7, REPLICATE('g', 4000);

go

 

If I now execute a query that, because of the CHECK constraint can’t possibly return any rows, I’ll get an execution plan that doesn’t even touch the table:

 

SELECT KeyColumn, CheckColumn

FROM   dbo.Test

WHERE  CheckColumn < 0

AND    LEFT(LongColumn, 5) = RIGHT(LongColumn, 5);


 

But let’s now examine what happens if I, for whatever reason, disable and later re-enable the constraint:

 

ALTER TABLE dbo.Test

      NOCHECK CONSTRAINT CK_Test;

-- Imagine something actuallly happening here

ALTER TABLE dbo.Test

      CHECK CONSTRAINT CK_Test;

 

Because I didn’t specify the “WITH [CHECK | NOCHECK]” clause when re-enabling the constraint, the command defaults to not checking the existing population. As a result, SQL Server now feels that it can no longer trust this constraint, as can be seen in the catalog view sys.check_constraints:

 

SELECT LEFT(name, 20) AS name, is_not_trusted

FROM   sys.check_constraints;

 

name                 is_not_trusted

-------------------- --------------

CK_Test              1

 

Since SQL Server no longer trusts this constraint, running the query that searches for negative values in CheckColumn will now cause an execution plan to be created that scans the entire table:

 

SET STATISTICS IO ON;

SELECT KeyColumn, CheckColumn

FROM   dbo.Test

WHERE  CheckColumn < 0

AND    LEFT(LongColumn, 5) = RIGHT(LongColumn, 5);

 

KeyColumn   CheckColumn

----------- -----------

 

Table 'Test'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


 

To prevent this from happening, always make sure to use WITH CHECK when you re-enable a constraint:

 

ALTER TABLE dbo.Test

      WITH CHECK CHECK CONSTRAINT CK_Test;

 

It looks a bit silly at first, the CHECK from the WITH CHECK option directly followed by the CHECK command to re-enable the constraint – but hey, I didn’t create the syntax, you know! You’ll get used to it, eventually.

 

Anyway, this results in the constraint being trusted again. The example query above will now, once more, return an empty result set without ever actually reading as much as a single page of the table’s data.

 

SELECT LEFT(name, 20) AS name, is_not_trusted

FROM   sys.check_constraints;

 

name                 is_not_trusted

-------------------- --------------

CK_Test              0

 

Now whereas this example might seem a bit contrived, it should be noted that this also applies to FOREIGN KEY constraints. I chose to use a CHECK constraint here to keep the sample code relatively short, but using a trusted FOREIGN KEY constraint, the optimizer can actually completely remove joined tables from a query! That is of course much more a real-world scenario than searching for a value that’s not permitted – and one that can have an even bigger impact on performance!!

 

So the bottom line is to always make sure that you include the WITH CHECK option when re-enabling a constraint. Or, to conclude with an awful pun, always double-check that there’s a double CHECK in the command.

Published Thursday, March 29, 2007 5:21 PM by Hugo Kornelis

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Uri Dimant said:

Hi, Hugo

Great example, I have one client that does exactly the same (did not specify WITH CHECK option) , so  I have already talked to him :-))) .Thanks

April 1, 2007 4:48 AM
 

jt said:

Thanks for that insight Hugo,

WRT to FKs - I was thrown by "...but using a trusted FOREIGN KEY constraint, the optimizer can actually completely REMOVE JOINED TABLES from a query!", esp the "remove joined tables" part.

Can you help me out and provide an example please, as I've never seen this before on a plan. much appreciated!

April 2, 2007 1:08 PM
 

Hugo Kornelis said:

Hi jt,

>>Can you help me out and provide an example please, as I've never seen this before on a plan. much appreciated!<<

Sure, no problem. The elimination of a join can (of course) only happen if no columns from the eliminated table are used, i.e. if the join is only needed to verify the existance of a row in the joined table. Here are two (semantically equivalent) queries for use in the AdventureWorks database that return address info for employees that actually exist in the employee table:

USE AdventureWorks;

go

SELECT     ea.EmployeeID, ea.AddressID

FROM       HumanResources.EmployeeAddress AS ea

INNER JOIN HumanResources.Employee AS e

     ON   e.EmployeeID = ea.EmployeeID;

SELECT     ea.EmployeeID, ea.AddressID

FROM       HumanResources.EmployeeAddress AS ea

WHERE EXISTS

(SELECT   *

 FROM     HumanResources.Employee AS e

 WHERE    e.EmployeeID = ea.EmployeeID);

go

If you check the execution plan, you'll see that the emplyee table is not used at all; because of the foreign key constraint, the optimizer can safely skip this. Now turn the constraint into a not trusted one:

ALTER TABLE HumanResources.EmployeeAddress

     NOCHECK CONSTRAINT FK_EmployeeAddress_Employee_EmployeeID;

ALTER TABLE HumanResources.EmployeeAddress

     WITH NOCHECK CHECK CONSTRAINT FK_EmployeeAddress_Employee_EmployeeID;

Rerun the queries above, and both will show an execution plan that includes a join to the Employee table, to check if the employee referenced in the EmployeeAddress table really exists. Restore the trusted state of the constraint to get the original plans again:

ALTER TABLE HumanResources.EmployeeAddress

     WITH CHECK CHECK CONSTRAINT FK_EmployeeAddress_Employee_EmployeeID;

April 2, 2007 3:00 PM
 

Aaron Prohaska said:

This is specific to SQL Server 2005? What is the equivalent in SQL Server 2000?

April 4, 2007 12:35 PM
 

Alex Kuznetsov said:

Hi Hugo,

An excellent point, thanks! Just wanted to add that a UNIQUE constraint can help the optimizer to eliminate an unnecessary sort.

April 4, 2007 1:43 PM
 

Hugo Kornelis said:

Aaron,

No, you can witness the exact same behaviour on SQL Server 2000. All the code above (except the query against sys.check_constraints - see below) can be executed unchanged on SQL Server 2000, and the results will be completely identical.

The only exception, as noted, is that there's no easy way to check if a constraint is trusted in SQL Server 2000. The only way I was able to find was to check the undocumented bits in the column "sysobjects.status", with this query:

SELECT name, status FROM sysobjects WHERE type = 'C';

When I tested this, the value for status was 2 when the constraint was enabled and trusted, 2050 (2 + 2048) when enabled and not trusted, and 2306 (2 + 256 + 2048) when disabled and not trusted.

April 4, 2007 5:02 PM
 

rsocol said:

Hugo, you wrote: "there's no easy way to check if a constraint is trusted in SQL Server 2000". But there is one easy and documented way to see if the constraints are not trusted:

SELECT name, OBJECTPROPERTY(id,'CnstIsNotTrusted') as is_not_trusted

FROM sysobjects WHERE xtype='C'

Razvan

April 6, 2007 9:28 AM
 

Hugo Kornelis said:

Razvan, you are right (as usual). Thanks for reminding me that even in SQL Server 2000, system tables were not the only source of information.

April 6, 2007 11:07 AM
 

Eric said:

Hugo

Thanks for this insight. Would there be any reason for the FK example you gave above *not* skipping  the join ? Trace flags settings ?

I tried to reproduce your example with a simple example,

but the optimizer keeps scanning the joined table :

create table Parent (iParent int primary key not null)

create table Child (iChild int not null, iParent int not null)

-- create FK with check

alter table Child with check add constraint FK_Child_parent foreign key (iparent) references Parent (iParent)

-- verify it is trusted

select name = object_name(constid), untrust = objectproperty(constid, 'CnstIsNotTrusted')

from sysforeignkeys where object_name(constid) = 'FK_Child_parent'

-- simple query with execution plan

select p.* from Parent p inner join Child c on c.iParent = p.iParent

The execution plan shows a table scan on Child and an index seek on Parent.

Why is the optimizer not skipping the JOIN ?

My env is SQL 2000 SP3.

Thanks ahead

--Eric

April 12, 2007 7:46 AM
 

Eric said:

Oops, disregard previous post, my mistake.

In fact the select query should be :

select c.* from Child c inner join Parent p on c.iParent = p.iParent

And the optimizer now correctly skips the join.

Thanks anyway !

--Eric

April 12, 2007 7:51 AM
 

Alexander Kuznetsov said:

You cannot use CHECK constraints to compare columns in different rows or in different tables, unless

January 21, 2009 6:52 PM
 

Alexander Kuznetsov said:

I have made up a simple example when a CHECK constraint that uses a UDF should succeed but always fails.

July 1, 2009 10:11 AM
 

Douglas Osborne said:

I wrote a quick script to do this for foreign keys

SELECT 'ALTER TABLE ' + OBJECT_NAME( Parent_Object_ID ) + ' WITH CHECK CHECK CONSTRAINT ' + Name AS [SQL to Execute]

FROM sys.foreign_keys

WHERE Is_Not_Trusted = 1

ORDER BY OBJECT_NAME( Parent_Object_ID )

January 21, 2011 11:24 AM
 

Douglas Osborne said:

And to see all of the records which need to be 'cleaned' for the actual non trusted check constraint s - this will list all of the actual failed records.

DBCC CHECKCONSTRAINTS WITH ALL_ERRORMSGS

January 21, 2011 11:27 AM

Leave a Comment

(required) 
(required) 
Submit

About Hugo Kornelis

Hugo is co-founder and R&D lead of perFact BV, a Dutch company that strives to improve analysis methods and to develop computer-aided tools that will generate completely functional applications from the analysis deliverable. The chosen platform for this development is SQL Server. In his spare time, Hugo likes to visit the SQL Server newsgroups, in order to share and enhance his knowledge of SQL Server.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement