Syntax:
ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
WITH VALUES
Example:
ALTER TABLE SomeTable
ADD SomeCol Bit NULL --Or NOT NULL.
CONSTRAINT D_SomeTable_SomeCol --When Omitted a Default-Constraint Name is autogenerated.
DEFAULT (0)--Optional Default-Constraint.
WITH VALUES --Add if Column is Nullable and you want the Default Value for Existing Records.
Notes:
Optional Constraint Name:
If you leave out CONSTRAINT D_SomeTable_SomeCol
then SQL Server will autogenerate
a Default-Contraint with a funny Name like: DF__SomeTa__SomeC__4FB7FEF6
Optional With-Values Statement:
The WITH VALUES
is only needed when your Column is Nullable
and you want the Default Value used for Existing Records.
If your Column is NOT NULL
, then it will automatically use the Default Value
for all Existing Records, whether you specify WITH VALUES
or not.
How Inserts work with a Default-Constraint:
If you insert a Record into SomeTable
and do not Specify SomeCol
's value, then it will Default to 0
.
If you insert a Record and Specify SomeCol
's value as NULL
(and your column allows nulls),
then the Default-Constraint will not be used and NULL
will be inserted as the Value.
Notes were based on everyone's great feedback below.
Special Thanks to:
@Yatrix, @WalterStabosz, @YahooSerious, and @StackMan for their Comments.
Best Answer
2000 and 2005 can exist side-by-side, you just have to use a named instance for at least one of them (only one can be the default instance).
If you are talking about upgrading, here are some good starting points:
http://searchsqlserver.techtarget.com/generic/0,295582,sid87_gci1269983,00.html
http://www.microsoft.com/sqlserver/2005/en/us/upgrading.aspx
Also get the 2005 upgrade advisor, which should highlight any potential issues in your specific environment:
http://www.microsoft.com/downloads/details.aspx?FamilyID=1470e86b-7e05-4322-a677-95ab44f12d75