How to add a column with a default value to an existing table in SQL Server?

Question

How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?

Accepted Answer

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.

How to add a column with a default value to an existing table in SQL Server?

Solution

Problem

Related problems