Effect of untrusted constraints

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jc_inst - 18 Jul 2008 08:59 GMT

Hi all,

I've read an article about untrusted constraints, and used this query to
check the foreign key constraints:

SELECT table_name, constraint_name
FROM information_schema.table_constraints
WHERE (constraint_type = 'FOREIGN KEY' or constraint_type = 'CHECK') AND
objectproperty(object_id(constraint_name), 'CnstIsNotTrusted') = 1

I have a list of constraints, and looking at their definition (using the
table designer), the field "check existing data on creation or re-enabling"
is in most cases set to "No".

I should change this, but I would like to know the effect this may have been
causing, not on the data, but for example on the query plans.

Thank you

Reply to this Message

Erland Sommarskog - 18 Jul 2008 09:40 GMT

> I've read an article about untrusted constraints, and used this query to
> check the foreign key constraints:
[quoted text clipped - 10 lines]
> I should change this, but I would like to know the effect this may have
> been causing, not on the data, but for example on the query plans.

That may indeed have an effect, since as far as the optimizer is concerned,
the constraints are null and void. Consider this example:

create table bludder (a int NOT NULL)
insert bludder (a) SELECT object_id from sys.objects
alter table bludder with nocheck add
constraint no_negative CHECK (a >= 0)
go
set statistics io on
select count(*) from bludder where a IN (-1, -2)
set statistics io off
go
alter table bludder with check check constraint no_negative
go
set statistics io on
select count(*) from bludder where a IN (-1, -2)
set statistics io off
go
drop table bludder

You will see that the query plans are different and that in the second
query, the table is not touched. (The reason I use IN and not a simple
=, is that with an expression like "a = -1", I get autoparameterisation
which is not good for the demo.)

Whether this can have any practical significance for your application,
it's difficult to say, but there is at least one case where it can have an
enormous effect, and that is partitioned views.

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Reply to this Message

jc_inst - 18 Jul 2008 10:08 GMT

Thank you. I'll update the constraints. I've not created these tables, and if
it weren't for that article, I would have missed it.

> > I've read an article about untrusted constraints, and used this query to
> > check the foreign key constraints:
[quoted text clipped - 39 lines]
> it's difficult to say, but there is at least one case where it can have an
> enormous effect, and that is partitioned views.

Reply to this Message