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

Robin Tucker (idontwanttobespammedanymore@reallyidont.com) writes:
[quoted text, click to view]

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.

[quoted text, click to view]

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, esquel@sommarskog.se

Books Online for SQL Server SP3 at