Updated: November 25, 2015
Binds a default to a column or to an alias data type.
sp_bindefault [ @defname = ] 'default' , [ @objname = ] 'object_name' [ , [ @futureonly = ] 'futureonly_flag' ]
[ @defname= ] 'default'
Is the name of the default that is created by CREATE DEFAULT. default is nvarchar(776), with no default.
[ @objname= ] 'object_name'
Is the name of table and column or the alias data type to which the default is to be bound. object_name is nvarchar(776) with no default. object_name cannot be defined with the varchar(max), nvarchar(max), varbinary(max), xml, or CLR user-defined types.
If object_name is a one-part name, it is resolved as an alias data type. If it is a two- or three-part name, it is first resolved as a table and column; and if this resolution fails, it is resolved as an alias data type. By default, existing columns of the alias data type inherit default, unless a default has been bound directly to the column. A default cannot be bound to a text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml, timestamp, or CLR user-defined type column, a column with the IDENTITY property, a computed column, or a column that already has a DEFAULT constraint.
[ @futureonly= ] 'futureonly_flag'
Is used only when binding a default to an alias data type. futureonly_flag is varchar(15) with a default of NULL. When this parameter is set to futureonly, existing columns of that data type cannot inherit the new default. This parameter is never used when binding a default to a column. If futureonly_flag is NULL, the new default is bound to any columns of the alias data type that currently have no default or that are using the existing default of the alias data type.
0 (success) or 1 (failure)
You can use sp_bindefault to bind a new default to a column, although using the DEFAULT constraint is preferred, or to an alias data type without unbinding an existing default. The old default is overridden. You cannot bind a default to a SQL Server system data type or a CLR user-defined type. If the default is not compatible with the column to which you have bound it, the SQL Server Database Engine returns an error message when it tries to insert the default value, not when you bind it.
Existing columns of the alias data type inherit the new default, unless either a default is bound directly to them or futureonly_flag is specified as futureonly. New columns of the alias data type always inherit the default.
When you bind a default to a column, related information is added to the sys.columns catalog view. When you bind a default to an alias data type, related information is added to the sys.types catalog view.
User must own the table, or be a member of the sysadmin fixed server role, or the db_owner and db_ddladmin fixed database roles.
A default named
today has been defined in the current database by using CREATE DEFAULT, the following example binds the default to the
HireDate column of the
Employee table. Whenever a row is added to the
Employee table and data for the
HireDate column is not supplied, the column gets the value of the default
USE master; GO EXEC sp_bindefault 'today', 'HumanResources.Employee.HireDate';
A default named
def_ssn and an alias data type named
ssn already exists. The following example binds the default
ssn. When a table is created, the default is inherited by all columns that are assigned the alias data type
ssn. Existing columns of type ssn also inherit the default def_ssn, unless futureonly is specified for futureonly_flag value, or unless the column has a default bound directly to it. Defaults bound to columns always take precedence over those bound to data types.
USE master; GO EXEC sp_bindefault 'def_ssn', 'ssn';
The following example binds the default
def_ssn to the alias data type
ssn. Because futureonly is specified, no existing columns of type
ssn are affected.
USE master; GO EXEC sp_bindefault 'def_ssn', 'ssn', 'futureonly';
The following example shows using delimited identifiers,
[t.1], in object_name.
USE master; GO CREATE TABLE [t.1] (c1 int); -- Notice the period as part of the table name. EXEC sp_bindefault 'default1', '[t.1].c1' ; -- The object contains two periods; -- the first is part of the table name, -- and the second distinguishes the table name from the column name.