Sql Server Interview Qustion:Rule

What is the create rule statement?
Creates an object called a rule. When bound to a column or a user-defined data type, a rule specifies the acceptable values that can be inserted into that column.

How many constraint a single column can have?
However, a column can have both a rule and one or more check constraints associated with it. When this is true, all restrictions are evaluated
Check constraint, default constraint, rule

What conditional expression you can use in rule?
A rule can be any expression valid in a WHERE clause and can include such elements 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.

What variable you should use when you create Rule?
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 (@).

When you create the rule, whether the rule may apply for existing data?
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. A rule can be created only in the current database.

How do you bind the rule to the column?
After creating a rule, execute sp_bindrule to bind the rule to a column or to a user-defined data type.

Which are the datatypes not supported for rule?
The rule must be compatible with the column data type. A rule cannot be bound to a text, image, or timestamp column. Be sure to enclose character and date constants with single quotation marks (') and to precede binary constants with 0x. For example, "@value LIKE A%" cannot be used as a rule for a numeric column

How do you get report on your Rule?
To get a report on a rule, use sp_help.


How do you display your rule text?
To display the text of a rule, execute sp_helptext with the rule name as the parameter.


How do you rename a rule?
To rename a rule, use sp_rename.

How do you drop a rule?
A rule must be dropped (using DROP RULE)

When dropping rule what you must do?
the rule must be unbound (using sp_unbindrule) before it is dropped. Use sp_unbindrule to unbind a rule from a column.

What will happen when binding new rule to the old rule?
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 user-defined data types. Binding a rule to a column replaces a rule already bound to the user-defined data type of that column. But binding a rule to a data type does not replace a rule bound to a column of that user-defined data type. The table shows the precedence in effect when binding rules to columns and to user-defined data types where rules already exist.
Old rule bound to
New rule bound to user-defined data type Column
User-defined data type Old rule replaced No change
Column Old rule replaced Old rule replaced

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. SQL Server generates an error message each time it attempts to insert such a default.


How do drop rule?
DROP RULE
Removes one or more user-defined rules from the current database.
Syntax
DROP RULE { rule } [ ,...n ]