CREATE DEFAULT
Creates an object called a
Syntax
CREATE DEFAULT default
AS constant_expression
Arguments
default
Is the name of the default. Default names must conform to the rules for identifiers. Specifying the default owner name is optional.
constant_expression
Is an expression that contains only constant values (it cannot include the names of any columns or other database objects). Any constant, built-in function, or mathematical expression can be used. Enclose character and date constants in single quotation marks ('); monetary, integer, and floating-point constants do not require quotation marks. Binary data must be preceded by 0x, and monetary data must be preceded by a dollar sign ($). The default value must be compatible with the column data type.
Remarks
A default name can be created only in the current database. Within a database, default names must be unique by owner. When a default is created, use sp_bindefault to bind it to a column or to a user-defined data type.
If the default is not compatible with the column to which it is bound, Microsoft® SQL Server™ generates an error message when trying to insert the default value. For example, N/A cannot be used as a default for a numeric column.
If the default value is too long for the column to which it is bound, the value is truncated.
CREATE DEFAULT statements cannot be combined with other Transact-SQL statements in a single batch.
A default must be dropped before creating a new one of the same name, and the default must be unbound by executing sp_unbindefault before it is dropped.
If a column has both a default and a rule associated with it, the default value must not violate the rule. A default that conflicts with a rule is never inserted, and SQL Server generates an error message each time it attempts to insert the default.
When bound to a column, a default value is inserted when:
- A value is not explicitly inserted.
- Either the DEFAULT VALUES or DEFAULT keywords are used with INSERT to insert default values.
If NOT NULL is specified when creating a column and a default is not created for it, an error message is generated when a user fails to make an entry in that column. This table illustrates the relationship between the existence of a default and the definition of a column as NULL or NOT NULL. The entries in the table show the result.
| Column definition | No entry, no default | No entry, default | Enter NULL, no default | Enter NULL, default |
|---|---|---|---|---|
| NULL | NULL | default | NULL | NULL |
| NOT NULL | Error | default | error | error |
Note Whether SQL Server interprets an empty string as a single space or as a true empty string is controlled by the sp_dbcmptlevel setting. If the compatibility level is less than or equal to 65, SQL Server interprets empty strings as single spaces. If the compatibility level is equal to 70, SQL Server interprets empty strings as empty strings. For more information, see sp_dbcmptlevel.
To rename a default, use sp_rename. For a report on a default, use sp_help.
Permissions
CREATE DEFAULT permissions default to members of the sysadmin fixed server role and the db_ddladmin and db_owner fixed database roles. Members of the sysadmin, db_owner and db_securityadmin roles can transfer permissions to other users.
Examples
A. Create a simple character default
This example creates a character default called unknown.
USE pubs
GO
CREATE DEFAULT phonedflt AS 'unknown'
B. Bind a default
This example binds the default created in example A. The default takes effect only if there is no entry in the phone column of the authors table. Note that no entry is not the same as an explicit null value.
Because a default named phonedflt does not exist, the following Transact-SQL statement fails. This example is for illustration only.
USE pubs
GO
sp_bindefault phonedflt, 'authors.phone'