|
|
Posted by Dan Guzman on 09/06/07 12:17
> 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" <zzzxtreme@yahoo.com> wrote in message
news:1189076493.419195.7440@y42g2000hsy.googlegroups.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?
>
Navigation:
[Reply to this message]
|