Creating and Modifying DEFAULT Definitions
DEFAULT definitions can be:
- Created when the table is created, as part of the table definition.
- Added to an existing table. Each column in a table can contain a single DEFAULT definition.
- Modified or deleted if DEFAULT definitions already exist. For example, you can modify the value inserted in a column when no value is entered.
Note To modify a DEFAULT definition using Transact-SQL or SQL-DMO, 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:
- 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, Microsoft® SQL Server™ 2000 by default applies the new default only to new rows of data added to the table; existing data inserted using the previous DEFAULT definition is unaffected. However, when adding a new column to an existing table, you can specify that SQL Server insert the default value (specified by the DEFAULT definition) rather than a null value into the new column for the existing rows in the table.
When you delete a DEFAULT definition, SQL Server inserts a null value rather than 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 creating a table
Transact-SQLTo create or delete a DEFAULT definition on a column of an existing table
To create a DEFAULT object