create default


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.


create default [owner.]default_name 
	as constant_expression



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.


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.


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, ""

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()



Datatype compatibility

Getting information about defaults

Defaults and rules

Defaults and NULLs

Specifying a default value in create table


SQL92 – Compliance level: Transact-SQL extension.

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


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