SQL Server : Defaults and Rules

Defaults :

Default is a constant value assigned to a columns=, into which the user need not insert values. A default can be bound to a column or a user-defined datatype.
The Syntax of the CREATE DEFAULT statement is :

Syntax :

?

1	CREATE DEFAULT default_name AS constant_expression

Any constant, built-in function, mathematical expression or a global variable can be used in the constant expression. The character and date constants must be included in single quotation marks ('), whereas money, ineger, and floating-point constants can be specified without quotation marks.

Binding Defaults :

The sp_binddefault system stored procedure is used for binding a default.
The Syntax of sp_bindefault is :

?

1 2 3 4 5

Sp_bindefault default _name, 'object_name.ColName' For Example :  1. CREATE DEFAULT city_default AS 'Mumbai'  2. Sp_default city_default, 'authors.city'

Create a default, city_default, and binds the default value, Mumbai, to city column of the authors table.
The Statement

?

1	Sp_bindefault city_default, city_datatype.

Binds the default city_default to the user-defined datatype, city_datatype.

Unbinding Defaults :

Defaults can be unbound from a column or user defined datatype using thesp_unbindefault system stored procedure.
The Syntax of sp_unbindefault is :

?

1 2 3 4

SP_unbindefault object_name For Example :  Sp_unbindefault 'authors.city'

Unbinds the default specified on the city column of the authors table.

Renaming Rules and Defaults :

The sp_rename system stored procedure can be used for renaming rules and defaults.
The Syntax of sp_rename is :

?

1	sp_rename old_object_name, new_object_name

Dropping Rules and Defaults :

The DROP RULE and DROP DEFAULT statement can be used to drop a rule and default respectively. A rule or default must be unbound from the column or the user-defined data type before it is dropped.

?

1 2 3 4 5

DROP RULE rule_name         (or)  DROP DEFAULT default_name Rules :   A rule provides a mechanism for enforcing domain constraints for columns or user defined data types. The rule is applied before any modification is to be done on the table. In other words, a rule specifies the restriction on the values for a column or a user defined data type. The Syntax of the CREATE RULE statement : ? 1 2 3 4 5 CREATE RULE rule_name AS conditional_expression example: 1.  CREATE RULE type_rule AS @typerule IN('business', 'mod_cook', 'trad_cook', 'popular_comp', 'psychology') 2.  CREATE RULE dept_name_rule AS @deptname NOT IN ('accounts', 'stores') 3.  CREATE RULE max_price_rule AS @maxprice >=5000 Iformation on a rule can be obtained using the sp_help system stored procedure. The text of a rule can be displayed using the sp_helptext system stored procedure with the name of the rule as its parameter. Binding Rules : A rule can be bound using the sp_bindrule system stored procedure. Syntax : ? 1 2 3 4 Sp_bindrule rule_name, object_name.ColName For Example :  Sp_bindrule type_rule, 'titles.type' Binds the rule, type_rule, to the type column of the titles table The restrictions on the use of the rules are as follows: Only one rule can be bound to a column or a user defined datatype. A rule cannot be bound to system datatypes. If a new rule is bound to a column or datatype that is already been inserted in the table, the existing values in the tables do not have to meet the criteria specified by the rule. A rule cannot be defined for a system-defined datatype Unbinding Rules : A rule can be unbound from a column or user-defined datatype using thesp_unbindrule system stored procedure. The Syntax of the sp_unbindrule is : ? 1 2 3 4 Sp_unbindrule object_name For Example :  Sp_unbindingrule 'titles.type'

SQL Server : Synonyms

Synonyms :

A Synonyms is a database object that serves the following purposes:

1. It provides an alternative name for another database object, referred to as the base object, that can exist on a local or remote server.
2. It provies a layer of abstraction that protects a client application from changes made to the name or location of the base object.

Synonyms can be created for the following types of objects:

Assembly (CLR) Stored Procedure	Assembly (CLR) Table_valued Function
Assembly (CLR) Scalar Function	Assembly Aggregate (CLR) Aggregate Function
Replication-filter-procedure	Extended Stored Procedure
SQL Scalar Function	SQL Table-Valued Function
SQL Inline table valued Function	SQL Stored Procedure
View	Table (User-Defined)

Creating a Synonyms :

Syntax :

?

1 2 3 4 5

CREATE SYNONYM [schema_name_1.] sunonum_name FOr < object . < object > :: =   {      [ server_name. [ databse_name ] . [ schema_name_2 ] . | database_name . [ schema_name_2 ] . | schema_name_2. ]     object_name   }

Creating a Synonyms for a Local Object :

The following example first creates a synonyms for the base object, Product in the AdventureWorks database, and then queries the synonym.

?

1 2 3 4 5 6 7 8 9 10 11 12

USE tempdb;   GO -- Create a synonym for the Product table in AdventureWorks.   CREATE SYNONYM MyProduct FOR AdventureWorks.Production.Product;   GO -- Query the Product table by using the synonym.  USE tempdb;   GO   SELECT ProductID, Name FROM MyProduct WHERE ProductID < 5;