schema_name

Is the name of the schema to which the rule belongs.

rule_name

Is the name of the new rule. Rule names must comply with the rules for identifiers. Specifying the rule owner name is optional.

condition_expression

Is the condition or conditions that define the rule. A rule can be any expression valid in a WHERE clause and can include elements such as arithmetic operators, relational operators, and predicates (for example, IN, LIKE, BETWEEN). A rule cannot reference columns or other database objects. Built-in functions that do not reference database objects can be included. User-defined functions cannot be used.

condition_expression includes one variable. The at sign (@) precedes each local variable. The expression refers to the value entered with the UPDATE or INSERT statement. Any name or symbol can be used to represent the value when creating the rule, but the first character must be the at sign (@).

Note:
Avoid creating rules on expressions that use alias data types. Although rules can be created on expressions that use alias data types, after binding the rules to columns or alias data types, the expressions fail to compile when referenced.

CREATE RULE cannot be combined with other Transact-SQL statements in a single batch. Rules do not apply to data already existing in the database at the time the rules are created, and rules cannot be bound to system data types. For more information, see Data Types (Database Engine).

A rule can be created only in the current database. After you create a rule, execute sp_bindrule to bind the rule to a column or to alias data type. A rule must be compatible with the column data type. For example, "@value LIKE A%" cannot be used as a rule for a numeric column. A rule cannot be bound to a text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml, CLR user-defined type, or timestamp column. A rule cannot be bound to a computed column.

Enclose character and date constants with single quotation marks (') and precede binary constants with 0x. If the rule is not compatible with the column to which it is bound, the SQL Server Database Engine returns an error message when a value is inserted, but not when the rule is bound.

A rule bound to an alias data type is activated only when you try to insert a value into, or to update, a database column of the alias data type. Because rules do not test variables, do not assign a value to an alias data type variable that would be rejected by a rule that is bound to a column of the same data type.

To get a report on a rule, use sp_help. To display the text of a rule, execute sp_helptext with the rule name as the parameter. To rename a rule, use sp_rename.

A rule must be dropped by using DROP RULE before a new one with the same name is created, and the rule must be unbound byusing sp_unbindrule before it is dropped. To unbind a rule from a column, use sp_unbindrule.

You can bind a new rule to a column or data type without unbinding the previous one; the new rule overrides the previous one. Rules bound to columns always take precedence over rules bound to alias data types. Binding a rule to a column replaces a rule already bound to the alias data type of that column. But binding a rule to a data type does not replace a rule bound to a column of that alias data type. The following table shows the precedence in effect when rules are bound to columns and to alias data types on which rules already exist.

If a column has both a default and a rule associated with it, the default must fall within the domain defined by the rule. A default that conflicts with a rule is never inserted. The SQL Server Database Engine generates an error message each time it tries to insert such a default.

To execute CREATE RULE, at a minimum, a user must have CREATE RULE permission in the current database and ALTER permission on the schema in which the rule is being created.

A. Creating a rule with a range

CREATE RULE range_rule
AS 
@range>= $1000 AND @range <$20000;

B. Creating a rule with a list

CREATE RULE list_rule
AS 
@list IN ('1389', '0736', '0877');

C. Creating a rule with a pattern

CREATE RULE pattern_rule 
AS
@value LIKE '__-%[0-9]'

Reference

Other Resources

Help and Information