CREATE DEFAULT Statement

Creates an object that, when bound to a column or a user-defined datatype, 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 datatype) when no value is explicitly supplied during an insert. An alternate and preferred method to creating defaults is to create DEFAULT constraints as described with the CREATE TABLE statement. Using constraints is the preferred method of restricting column data because the constraint definition is stored with the table and automatically dropped when the table is dropped. A default is beneficial, however, when the default will be used again and again for multiple columns.

Syntax

CREATE DEFAULT [owner.]default_name
AS constant_expression

where

default_name

Is the name of the default. Default names must conform to the rules for identifiers.

constant_expression

Specifies an expression that contains only constant values (it cannot include the names of any columns or other database objects). You can use any constant, built-in function, mathematical expression, or global variable. Enclose character and date constants in single quotation marks ('); money, integer, and floating-point constants do not require quotation marks. Binary data must be preceded by 0x, and money data must be preceded by a dollar sign ($). The default value must be compatible with the datatype of the column.

Remarks

You can create a default only in the current database. Within a database, default names must be unique by owner.

After you've created a default, use the sp_bindefault system stored procedure to bind it to a column or to a user-defined datatype.

You cannot use "N/A," for example, as a default for a numeric column. If the default is not compatible with the column to which you've bound it, SQL Server generates an error message when trying to insert the default value, but not when you bind it.

If the default value is too long for the column to which you've bound it, the value will be truncated.

CREATE DEFAULT statements cannot be combined with other SQL statements in a single batch.

To display definitions and binding information, execute the sp_helptext system stored procedure. You can find the value of a default by using the sp_helptext system stored procedure with the default name as the parameter.

You must drop a default before you create a new one of the same name, and you must unbind a default (with the sp_unbindefault system stored procedure) before you drop it.

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.

Once bound to a column, a default value is inserted when:

• You do not explicitly insert a value.
• You use the DEFAULT VALUES or DEFAULT keyword with INSERT. For details and examples, see the INSERT statement.

If you specify NOT NULL when you create a column and do not create a default for it, an error message is generated whenever a user fails to make an entry in that column. The following 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.

To rename a default, use the sp_rename system stored procedure. For a report on a default, use the sp_help system stored procedure.

Permission

CREATE DEFAULT permission defaults to the database owner, who can transfer it to other users.

Examples

A.    Simple Character Default

This example creates a character default of 'unknown'.

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.

sp_bindefault phonedflt, 'authors.phone'

See Also