create default

Description

Specifies a value to insert in a column (or in all columns of a user-defined datatype) if no value is explicitly supplied at insert time.

Syntax

create default [owner.]default_name 
	as constant_expression

Parameters

default_name

is the name of the default. It must conform to the rules for identifiers and cannot be a variable. Specify the owner’s name to create another default of the same name owned by a different user in the current database. The default value for owner is the current user.

constant_expression

is an expression that does not include the names of any columns or other database objects. It cannot include global variables, but can include built-in functions that do not reference database objects. Enclose character and date constants in quotes and use a “0x” prefix for binary constants.

Examples

Example 1

Defines a default value. Now, you need to bind it to the appropriate column or user-defined datatype with sp_bindefault:

create default phonedflt as "UNKNOWN"

Example 2

sp_bindefault phonedflt, "authors.phone"

The default takes effect only if there is no entry in the phone column of the authors table. No entry is different from a null value entry. To get the default, issue an insert command with a column list that does not include the column that has the default.

Example 3

Creates a default value, todays_date, that inserts the current date into the columns to which it is bound:

create default todays_date as getdate()

Usage


Restrictions


Datatype compatibility


Getting information about defaults


Defaults and rules


Defaults and NULLs


Specifying a default value in create table

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Use the default clause of the create table statement to create ANSI SQL-compliant defaults.

Permissions

create default permission defaults to the Database Owner, who can transfer it to other users.

See also

Commands alter table, create rule, create table, drop default, drop rule

System procedures sp_bindefault, sp_help, sp_helptext, sp_rename, sp_unbindefault