sql rules and udt

[Nick Chan]

hello, i've just started playing around with rules and udt
is it possible to alter rule?
are rules 'slower' compared to check constraint?

[Dan Guzman]

hello, i've just started playing around with rules and udt

is it possible to alter rule?

You'll need to recreate the rule, which requires that you unbind, drop,
create and bind again. See sample script at the end of this post.

are rules 'slower' compared to check constraint?

I haven't used rules for many years nor have I seen a performance comparison
between rules and CHECK constraints. However, I would not use rules for new
development. Below is an excerpt from the SQL 2005 Books Online:

<Excerpt
href="ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/b016a289-3a74-46b1-befc-a13183be51e4.htm">

CREATE RULE will be removed in a future version of Microsoft SQL Server.
Avoid using CREATE RULE in new development work, and plan to modify
applications that currently use it. We recommend that you use check
constraints instead. Check constraints are created by using the CHECK
keyword of CREATE TABLE or ALTER TABLE. For more information, see CHECK
Constraints.

</Excerpt>
USE tempdb
GO

EXEC sp_addtype 'mytype', 'int'
GO

CREATE RULE RUL_mytype AS (@mytype 0)
GO

EXEC sp_bindrule 'RUL_mytype', 'mytype'
GO

CREATE TABLE dbo.MyTable
(
col1 mytype NOT NULL
)
GO

INSERT INTO dbo.MyTable VALUES(1)
INSERT INTO dbo.MyTable VALUES(-1)
GO

EXEC sp_unbindrule 'mytype', 'RUL_mytype'
GO

DROP RULE RUL_mytype
GO

CREATE RULE RUL_mytype AS (@mytype < 0)
GO

EXEC sp_bindrule 'RUL_mytype', 'mytype'
GO

INSERT INTO dbo.MyTable VALUES(-1)
INSERT INTO dbo.MyTable VALUES(1)
GO

SELECT * FROM dbo.MyTable
GO
--
Hope this helps.

Dan Guzman
SQL Server MVP

"Nick Chan" <zz*******@yahoo.comwrote in message
news:11********************@y42g2000hsy.googlegrou ps.com...

hello, i've just started playing around with rules and udt
is it possible to alter rule?
are rules 'slower' compared to check constraint?

[Erland Sommarskog]

Nick Chan (zz*******@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/SQLSer...dbackID=282393

Feel free to vote!
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Nick Chan]

thanks a lot guys. that's very helpful !
i wish i could use RULE to maintain employee morale (hard to get
programmers here)
gonna use CHECK for now
will vote too
On Sep 7, 5:37 am, Erland Sommarskog <[e-mail]:

Nick Chan ([e-mail]) 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 Connecthttps://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?Fe...

Feel free to vote!

--
Erland Sommarskog, SQL Server MVP, [e-mail]

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

[--CELKO--]

>i wish i could use RULE to maintain employee morale (hard to get programmers here) gonna use CHECK for now <<

For future reference, you might want to read up on CREATE ASSERTION
which is another part of Standard SQL that might show up in the future
versions of T-SQL.

[Nick Chan]

thanks mr celko
ps : ur tree has been running in my server for 3 years , 150k nodes
On Sep 7, 11:51 pm, --CELKO-- <[e-mail]:

i wish i could use RULE to maintain employee morale (hard to get programmers here) gonna use CHECK for now <<

For future reference, you might want to read up on CREATE ASSERTION
which is another part of Standard SQL that might show up in the future
versions of T-SQL.

[--CELKO--]

>ps : ur tree has been running in my server for 3 years , 150k
nodes <<

How is performance? Everyone asks for "Real World" examples of the
Nested Sets model from someone other than me! I am not a "trusted
source" :)

[Nick Chan]

the retrieval is of course still very fast
but updates was getting slower and slower, noticably starting from 75K
nodes. like it used to take less than 3 sec to add a node. now it
kinda take 11sec.
it was a dual xeon with 6gb ram, win2k3 ent, sql ent. and just me
handling it (me not dba, just asp.net programmer)
given a better server and dba, it may not be 75k nodes.

we moved to a better server because it was growing rapidly.

so i 'created', out of desperation, another structure because my boss
was mad at me because of the 11sec.
it is unorthodox and im kinda embarassed to discuss it. im just self-
taught

briefly it is like this
A
/ \
B C
/ \ /\
D E F G
/
H

so the table looks like this

id node parent level
1 A null 1
2 B A 1
3 D A 1
4 D B 2
5 E A 1
6 E B 2
7 H A 1
8 H B 2
9 H D 3
10 C A 1
11 F A 1
12 F C 2
13 G A 1
14 G C 2

so if i add another node under H, say 'X',
i would insert records for X, like this

15 X A 1
16 X B 2
17 X D 3
18 X H 4

it's fast, because i just retrieve all parents of H, make a copy and
insert 1 more record (X-H-4).

u can imagine how big the table is going to be. from 75K rows (celko
tree) to about 4-5million rows (new table).

in our 'financial report', we have take a selected node, and select
its parent. so with this table , i just do a simple select * from
where node='X',

I 'imagine' the growth would be logarithmic like this :
http://www.ifi.uio.no/it/latex-links...s/objaxes2.gif
rather than exponential, because in our table, the max level column
grow slower and slower.

id column is clustered, node column is indexed.

another table will store each node's number of child nodes.

but it works so well and we have eliminated the celko-tree just
recently for very large apps

i welcome any constructive criticism, because i'm quite inexperienced,
and am a college dropout.


On Sep 15, 6:00 am, --CELKO-- <[e-mail]:

ps : ur tree has been running in my server for 3 years , 150k

nodes <<

How is performance? Everyone asks for "Real World" examples of the
Nested Sets model from someone other than me! I am not a "trusted
source" :)