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.
CREATE DEFAULT default_name AS constant_expression
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.
Sp_bindefault Default_Name, Alias_Name.Column_Name
· Default_Name : Name of the default.
· Alias_Name .Column_Name : refers to the table.column to which the defaults to be bound.
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.
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.
DROP DEFAULT Default_Name
Drop default dept