Default Column Values

Before you define any column-level integrity constraints, you can use the default clause to assign a default value to a column as part of the create table statement. When you do not enter a value for a column, the default value is inserted.

You can use the following values with the default clause:

constant_expression – specifies a constant expression to use as a default value for the column. The constant expression cannot include the name of any columns or other database objects, but you can include built-in functions that do not reference database objects. This default value must be compatible with the datatype of the column.
user – specifies that the user name is inserted as the default. The datatype of the column must be either char(30) or varchar(30) to use this default.
null – specifies that the null value is inserted as the default. You cannot use the not null keyword to define this default for columns that do not allow null values.

For example, this create table statement defines two column defaults:

create table my_titles
(title_id        char(6),
title            varchar(80),
price            money       default null,
total_sales      int         default 0)

You can include only one default clause per column in a table.

Using the default clause to assign defaults is simpler than the two-step Transact-SQL method. In Transact-SQL, you can use create default to declare the default value, and then use sp_bindefault to bind it to the column.