Each column in a record must contain a value, even if that value is NULL. There are situations when you need to 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 null values, you can load the row with a null value. But, because nullable columns may not be desirable, a better solution can be to define a DEFAULT definition for the column, where appropriate. 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 tell Microsoft® SQL Server™ to load a default value in the column when you do not specify a value for the column.
Note You can also explicitly instruct SQL Server to insert the default value for the column using the DEFAULT VALUES clause of the INSERT STATEMENT.
If a column does not allow null values and does not have a DEFAULT definition, you must specify a value for the column explicitly or SQL Server will return an error indicating that the column does not allow null values.
The value inserted into a column defined by the combination of the DEFAULT definition, the nullability of the column, and the value inserted into the column can be summarized.
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 |
A DEFAULT object is defined for a specific database and is shared by columns of different tables by being bound to each column to which the default applies. For example, if several of your tables have a quantity column, you can define a DEFAULT object in your database that inserts a value of 1 in the quantity column whenever the user leaves that column blank in any table.
If a DEFAULT object is bound to a column, you can specify a different default value for that column in a specific table. This unbinds the existing DEFAULT object from the column before the new default value is bound to the column.
| Allowing Null Values | Creating and Modifying DEFAULT Definitions |