Defaults are the objects that can be bound to one or more columns or user defined data type, making it possible to define them once and use them repeatedly. Defaults specify a value to add to a column when you do not insert a value into that column. Defaults are recorded in the sysobjects system table in each database.

Creating a Default

Defaults are created by using CREATE DEFAULT statement.

Syntax:

CREATE DEFAULT default_name AS constant_expression

Example

CREATE DEFAULT dept AS 'IT'

Binding a Default

A default is a standalone object in the database , hence to use defaults, they have to be bound to a column or a data type. To bind a default, we have to use sp_bindefault system stored procedure.

Syntax:

Sp_bindefault Default_Name, Alias_Name.Column_Name

where

·    Default_Name : Name of the default.

·    Alias_Name .Column_Name : refers to the table.column to which the defaults to be bound.

Example

sp_bindefault dept, 'EMP.DEPT'

 To see the effect of default, add a new row to the table and then view the table.

In this example, I’ve added two rows in first row I have not inserted the dept but in second I have inserted the dept. In first insert the dept value is coming from default dept coz we have bound it and in second insert dept value is coming from user input, you can see clearly in the output.

Unbind the Default

To unbind the default, we can use the sp_unbindefault system stored procedure.

Syntax:

Sp_unbindefault  Object_Name

Example

sp_unbindefault 'EMP.DEPT'

Dropping a Default

A default cannot be dropped until and unless it has been unbounded from all data types and columns. Once the default unbounded from all the columns and data types it can be dropped using the DROP DEFAULT statement.

Syntax:

DROP DEFAULT Default_Name

Example

Drop default dept