Oct 1, 2015

Add a column, with a default value, to an existing table in SQL Server

You can do it by edit of table design, if you want do it with query see following.

Syntax:

ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
[WITH VALUES]

Note: Use WITH VALUES to update existing null-able rows.

Example:

ALTER TABLE MyTable
ADD MyTableTypeID INT NOT NULL
CONSTRAINT Constraint_MyTableTypeID DEFAULT 0
GO

Note: WITH VALUES handles the NOT NULL part

ALTER TABLE MyTable
ADD MyTableTypeID INT
CONSTRAINT Constraint_MyTableTypeID DEFAULT 0 WITH VALUE
GO