By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,550 Members | 2,757 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,550 IT Pros & Developers. It's quick & easy.

How does adding constraints affect performance?

P: n/a
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

Jul 23 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Robin Tucker (id*************************@reallyidont.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****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.