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?

This blog has moved! You can find this content at the following new location:

https://SQLServerFast.com/blog/hugo/2007/03/can-you-trust-your-constraints/

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
 

John Klemetsrud said:

The is_not_trusted bit will always remain = 1 if the is_not_for_replication bit = 1. The only way to fix this is by dropping the FK Constraint and rebuilding it, but this can be very difficult because when you rebuild it you have to make sure all other options remain the same while only changing those 2. Also, consider there may be some "unknown" valid reason the top level application sitting atop the database wants it this way AND/OR if changing it may violate some support from the application vendor.

January 26, 2015 6:29 PM
 

Tron Magnus Svagard said:

I'm having a similar issue with 2 tables involved.

This query...

SELECT

dx.*

FROM [dbo].[DIM_X] dx

INNER JOIN [dbo].[APL_X_ID_mapping] map1

ON dx.DIM_X_ID = map1.DIM_X_ID

touches only the DIM_X table.

But...

SELECT

dx.ColA,

dx.ColB,

dx.ColC

FROM [dbo].[DIM_X] dx

INNER JOIN [dbo].[APL_X_ID_mapping] map1

ON dx.DIM_X_ID = map1.DIM_X_ID

INNER JOIN [dbo].[APL_X_ID_mapping] map2

ON map1.[APL_X_mapping_ID] = map2.[APL_X_mapping_ID]

touches the APL_X_ID_mapping table twice.

DIM_X has a foreign key constraint to APL_X_ID_mapping on the column DIM_X_ID. In addition the APL_X_mapping_ID has a unique constraint. (APL_X_mapping is a one-to-one-mapping table)

Is it possible to implement this optimization in any way?

I know the example might seem strange, but it's a simplification of a real use case.

April 16, 2015 6:28 PM
 

Henrik Staun Poulsen said:

hi Hugo,

I ran the query that Douglas Osborne provided. Twice. But I find the constraint is still has is_not_trusted=1

When I dropped the constraint, and created it again (this time with "With Check Check") I got is_not_trusted=0

I've searched high and low on Google, but I cannot find an explanation. Can you help?

TIA, Henrik

April 24, 2015 4:40 AM
 

aikman said:

Hi

Can Untrusted Foreign key be mitigated with indexes

Regards

Håkan

August 10, 2015 6:42 AM
 

Hugo Kornelis said:

@Tron (Sorry for the late reply):

The relevant change is the second join. You write that APL_X_mapping_ID is unique, so every row in the ampping table joins to itself. A very strange query.

One possible explanation is if APL_X_mapping_ID is nullable. NULL is not equal to NULL, so SQL Server will have to get the row and check for that - those rows would be rejected from the query result.

Another possible explanation is that you probably don't have a foreign key on this column. That would not change anything for the data model because a foreign key from a column to itself is useless, but perhaps the logic in the optimizer that removes useless joins only responds to foreign key constraints? (this is speculation on my part)

@Henrik (Sorry for the late reply):

Douglas' query produces a result set that contains the SQL you have to run. So you first run Douglas' query (with Results to Text mode), then copy the result set, paste it in a new window and run that.

Do check the SQL produced first. Douglas' query does not escape table and constraint names, so you are vulnerable to injection or (more probable) will get errors if you have table/constraint names that have spaces.

After running the second query, do check the result for error messages.

@Håkan:

Simple answer: no.

Indexes can be used to quickly retrieve rows based on a value.

(Trusted) constraints can be used to simplify query logic before even starting to retrieve rows, because some conditionas are guaranteed to hit.

Okay, slightly more nuanced answer: if you have untrusted constraints and they cause performance issues, an index *can* sometimes alleviate the problem. But that's like telling someone who shoots himself in the foot every Saturday to apply a band aid and take a pain killer. Why not take away his gun or teach him to aim? (In other words, instead of using that index you should fix the root cause, which is the untrusted constraint).

August 10, 2015 4:34 PM
 

John said:

Could you possibly provide an example where you demonstrate the change in query plan using a foreign constraint instead of a local column/data constraint?

June 23, 2016 11:03 AM
 

Hugo Kornelis said:

Hi John,

I recently wrote an article on SQL Server Central covering exactly that topic: http://www.sqlservercentral.com/articles/Foreign+Keys+(FK)/138195/

I hope this helps!

Cheers,

Hugo

June 25, 2016 3:24 PM

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.
Privacy Statement