MSSQL Server Rules Simplified

One of the most exciting RDBMS Feature RULE is very simple with MSSQL Server Creates an object called a rule. When bound to a Field, data type, a rule specifies the acceptable values that can be inserted into that field. Here i am using a database study with in localhost Our table test50 create table test50 ( f1 int, f2 varchar(100) ) Creatting a RULE

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

Binding this rule to f1 of table50

exec sp_bindrule ‘range_rule’,'test50.f1′

For testing try to insert values beyond the rule

insert into test50 values(1,’abcd’)

Returned error as follows, saying this cannot be executed because of this rule bind

The following error occured while executing the query: Server: Msg 513, Level 16, State 0, Line 16 A column insert or update conflicts with a rule imposed by a previous CREATE RULE statement. The statement was terminated. The conflict occurred in database ‘study’, table ‘study.test50′, column ‘f1′. The statement has been terminated.

Again try with acceptable values and selecting view of table

insert into test50 values(1500,’abcd’)

select * from test50 f1       f2 1500  abcd

Another kind of rule with patterns

create rule rule_pattern as @var like ‘abcd%’

Binding this to f2 of table50

exec sp_bindrule ‘rule_pattern’ ,’test50.f2′

trying to violate rule

insert into test50 values(1500,’efgh’)

The following error occured while executing the query: Server: Msg 513, Level 16, State 0, Line 39 A column insert or update conflicts with a rule imposed by a previous CREATE RULE statement. The statement was terminated. The conflict occurred in database ‘study’, table ‘study.test50′, column ‘f2′. The statement has been terminated.

With acceptable values

insert into test50 values(1500,’abcdefgh’) select * from test50 f1      f2 1500  abcd 1500  abcdefgh

• Meet Author

  

  S.Abhilash, started this blog in the later half of 2009 as a self hosting site for all his previous blogging attempts. , My way of writing and frequency of publishing may not be uniform as my resources are my projects nd issues i came across. For the last few years this blog is reacing 100 s of unique visitors each day, thanks wordpress and google for such a perfection in their software and services.

  Head to Authors Page | Abhilash on Facebook
  
  

  Submit Your Email For Free Updates on New Articles

  
  

MSSQL Server     2011, 2012, abcd, abcdefgh, abhilash, acceptable values, acceptableÂ, bind, Binding, conflict, conflicts, CREATE, Creatting, database study, efgh, error, exec, f1, Feature, field, int, kind, localhost, lt, msg, MSSQL, occured, query, query server, range, range rule, rdbms, Returned, rule, study, test, type, var, varchar

Comment on Facebook