CREATE DEFAULT

Creates an object called a default. When bound to a column or a user-defined data type, a default specifies a value to be inserted into the column to which the object is bound (or into all columns, in the case of a user-defined data type) when no value is explicitly supplied during an insert. Defaults, a backward compatibility feature, perform some of the same functions as default definitions created using the DEFAULT keyword of ALTER or CREATE TABLE statements. Default definitions are the preferred, standard way to restrict column data because the definition is stored with the table and automatically dropped when the table is dropped. A default is beneficial, however, when the default is used multiple times for multiple columns.

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.

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'

See Also

ALTER TABLE

Batches

CREATE RULE

CREATE TABLE

DROP DEFAULT

DROP RULE

Expressions

INSERT

sp_bindefault

sp_help

sp_helptext

sp_rename

sp_unbindefault

Using Identifiers