Creating and Modifying DEFAULT Definitions
You can create a DEFAULT definition as part of the table definition when you create a table. If a table already exists, you can add DEFAULT definition to it. Each column in a table can contain one DEFAULT definition.
If a DEFAULT definition already exists, you can modify or delete it. For example, you can modify the value that is inserted in a column when no value is entered.
Note
To modify a DEFAULT definition, you must first delete the existing DEFAULT definition and then re-create it with the new definition.
DEFAULT definitions cannot be created on columns defined with the following:
A timestamp data type.
An IDENTITY or ROWGUIDCOL property.
An existing DEFAULT definition or DEFAULT object.
Note
The default value must be compatible with the data type of the column to which the DEFAULT definition applies. For example, the default value for an int column must be an integer number, not a character string.
When a DEFAULT definition is added to an existing column in a table, by default, the SQL Server 2005 Database Engine applies the new default only to new rows of data that are added to the table. Existing data that was inserted by using the previous DEFAULT definition is unaffected. However, when you add a new column to an existing table, you can specify that the Database Engine insert the default value (specified by the DEFAULT definition) instead of a null value, into the new column for the existing rows in the table.
When you delete a DEFAULT definition, the Database Engine inserts a null value instead of the default value when no value is inserted into the column for new rows. However, no changes are made to the existing data in the table.
To create a DEFAULT definition on a column when you create a table
To create or delete a DEFAULT definition on a column of an existing table
How to: Specify Default Values for Columns (Visual Database Tools)
Table Column Properties (SQL Server Management Studio)
To delete a DEFAULT object
To obtain information about a DEFAULT definition
sys.default_constraints (Transact-SQL)