Share via


DEFAULT Definitions

Each column in a record must contain a value, even if that value is NULL. There may be situations when you must load a row of data into a table but you do not know the value for a column, or the value does not yet exist. If the column allows for null values, you can load the row with a null value. Because nullable columns may not be desirable, a better solution could be to define, where appropriate, a DEFAULT definition for the column. For example, it is common to specify zero as the default for numeric columns, or N/A as the default for string columns when no value is specified.

When you load a row into a table with a DEFAULT definition for a column, you implicitly instruct the SQL Server 2005 Database Engine to insert a default value in the column when a value is not specified for it.

Note

You can also use the DEFAULT VALUES clause of the INSERT STATEMENT to explicitly instruct the Database Engine to insert a default value for a column.

If a column does not allow for null values and does not have a DEFAULT definition, you must explicitly specify a value for the column, or the Database Engine returns an error that states that the column does not allow null values.

The value inserted into a column that is defined by the combination of the DEFAULT definition and the nullability of the column can be summarized as shown in the following table.

Column definition No entry, no DEFAULT definition No entry, DEFAULT definition Enter a null value

Allows null values

NULL

Default value

NULL

Disallows null values

Error

Default value

Error

See Also

Concepts

Allowing Null Values
Creating and Modifying DEFAULT Definitions

Other Resources

CREATE TABLE (Transact-SQL)
ALTER TABLE (Transact-SQL)
DROP TABLE (Transact-SQL)
INSERT (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance