How does adding constraints affect performance?

[Robin Tucker]

I'm considering adding domain integrity checks to some of my database table
items. How does adding such constraints affect SQL Server performance? For
example, I have a simple constraint that restricts a couple of columns to
having values within the values assigned in my application by an
enumeration:

(([Condition] >= 0 and [Condition] <=3) and ([Type] >= 0 and [Type] <=
2))

This enforces domain integrity for two enumerations having values 0, 1, 2, 3
and 0, 1, 2 in the application. Is this an efficient way of performing such
checks? What are the pitfalls of domain integrity checking?
Thanks


Robin

[Erland Sommarskog]

Robin Tucker (id************ *************@r eallyidont.com) writes:

I'm considering adding domain integrity checks to some of my database
table items. How does adding such constraints affect SQL Server
performance? For example, I have a simple constraint that restricts a
couple of columns to having values within the values assigned in my
application by an enumeration:

(([Condition] >= 0 and [Condition] <=3) and ([Type] >= 0 and [Type] <=
2))
Two pieces of advice:

1) Fewer parentheses!
2) Make that two constraints. If nothing else, this is good when the
check fails, since you more easily can tell what went wrong.
This enforces domain integrity for two enumerations having values 0, 1, 2, 3 and 0, 1, 2 in the application. Is this an efficient way of performing
such checks? What are the pitfalls of domain integrity checking?

CHECK constraints is probably the most effcient way of performing checks
of this kind.

CHECK constraints can also help to improve performance. Presuming that you
don't add your constraints WITH NOCHECK, this SELECT on large table will
return promptly:

SELECT * FROM tbl WHERE Type = 4

Not that this is the most likely query...

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp