SQL Server/T-SQL Tutorial/System Functions/sp bindefault

Define a default of zero for our Salary

9> CREATE TABLE Employees (
10>     EmployeeID int IDENTITY (1, 1) NOT NULL ,
11>     LastName nvarchar (20) NOT NULL ,
12>     FirstName nvarchar (10) NOT NULL ,
13>     Title nvarchar (30) NULL ,
14>     TitleOfCourtesy nvarchar (25) NULL ,
15>     BirthDate datetime NULL ,
16>     HireDate datetime NULL ,
17>     Address nvarchar (60) NULL ,
18>     City nvarchar (15) NULL ,
19>     Region nvarchar (15) NULL ,
20>     PostalCode nvarchar (10) NULL ,
21>     Country nvarchar (15) NULL ,
22>     HomePhone nvarchar (24) NULL ,
23>     Extension nvarchar (4) NULL ,
24>     Photo image NULL ,
25>     Notes ntext NULL ,
26>     Salary int NULL ,
27>     PhotoPath nvarchar (255) NULL
28>
29> )
30> GO
1>
2>    CREATE DEFAULT SalaryDefault
3>       AS 0
4> GO
1>    EXEC sp_bindefault "SalaryDefault", "Employees.Salary"
2>
3> GO
Default bound to column.
1>
2> drop DEFAULT SalaryDefault;
3>
4> drop table Employees;
5> GO
Msg 3716, Level 16, State 3, Server J\SQLEXPRESS, Line 2
The default "SalaryDefault" cannot be dropped because it is bound to one or more column.

The system procedure sp_bindefault is used to bind the defined default value to specific columns.

3>
4> CREATE DEFAULT zip_default AS 94710
5> GO
1>
2> CREATE TYPE zip
3>   FROM CHAR(5) NOT NULL
4> GO
1>
2>
3> sp_bindefault "zip_default", "zip"
4>
5> --sp_unbindefault removes the connection between the default and the data type of the corresponding column.
6>
7> sp_unbindefault "zip_default", "zip"
8>
9> drop DEFAULT zip_default;
10>
11> drop type zip;
12> GO