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)