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, which 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 can include global variables and 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

Creates a default called D1 that uses the @@spid global variable:

create default D1 as @@spid

Example 2

Defines a default value, then binds it to the appropriate column or user-defined datatype:

create default phonedflt as "UNKNOWN"

Example 3

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 4

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

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 cannot be inserted. Adaptive Server generates an error message each time it attempts to insert such a default.

Defaults and nulls

If a column does not allow nulls, and you do not create a default for the column, when a user attempts to insert a row but does not include a value for that column, the insert fails and Adaptive Server generates an error message.

Table 1-3 illustrates the relationship between the existence of a default and the definition of a column as NULL or NOT NULL.

Table 1-3: Relationship between nulls and column defaults

Column null type

No entry, no default

No entry, default exists

Entry is null, no default

Entry is null, default exists


Null inserted

Default value inserted

Null inserted

Null inserted


Error, command fails

Default value inserted

Error, command fails

Error, command fails

Specifying a default value in create table

You can define column defaults using the default clause of the create table statement as an alternative to using create default. However, these column defaults are specific to that table; you cannot bind them to other tables. See create table and alter table for information about integrity constraints.


ANSI SQL – Compliance level: Transact-SQL extension.

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


create default permission defaults to the database owner, who can transfer it to other users.


Values in event and extrainfo columns of sysaudits are:


Audit option

Command or access audited

Information in extrainfo



create default

  • Roles – current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Other information – NULL

  • Current value – NULL

  • Proxy information – original login name, if set proxy is in effect

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