|
|
Posted by Erland Sommarskog on 09/06/07 21:37
Nick Chan (zzzxtreme@yahoo.com) writes:
> hello, i've just started playing around with rules and udt
> is it possible to alter rule?
As Dan said, unbind, drop, recreate and rebind. All operations are
very swift.
> are rules 'slower' compared to check constraint?
Rules or check constraints should make any difference for implementing
the business rules.
However, provided that a check constraint is applied WITH CHECK and
never disabled, the optimizer can trust the constraint, which can help
the optimizer to find a better plan. To take a simple example, say
that you have a constraint that goes CHECK (col IN ('A', 'B', 'C'))
and you run the query:
SELECT COUNT(*) FROM tbl WHERE col = 'D'
this query will return 0 instantly, and the table will never be accessed.
This can never happen with a rule, as when a rule is bound, the current
data is not checked for validity.
Nevertheless, binding rules and defaults to user-defined types is a
very useful feature. Microsoft says in Books Online for SQL 2008,
currently in beta, that the version after SQL 2008 will not have
rules and bound defaults. Since there is not alternative functionality,
I think this would be a serious mistake. I have filed an item for
this on Connect
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=282393
Feel free to vote!
--
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
Navigation:
[Reply to this message]
|