MS SQL Server - Add a column with default value to an existing table

This post describes how to add new column to existing table in SQL Server by using Transact-SQL .

Syntax:

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

Example:

ALTER TABLE Item
        ADD StockQty INT NULL --Or NOT NULL.
 CONSTRAINT DF_Item_StockQty --default constraint name
    DEFAULT (0)--default value
WITH VALUES --Add if Column is Nullable and you want the Default Value for Existing Records.

Lets review the above statement:

• Item is the target table name, to which I want to add the column.
• StockQty is the new column name being added, with type INT, also you can specify it as Nullable or NOT NULL.
• DF_Item_StockQty is the optional constraint name given to the default constraint, if you do not specify name here, it will auto-generate a name like DF_Item_StockQty_6DF5C21B0A
• DEFAULT (0): 0 is the default value for new column in this example.
• WITH VALUES is only needed when your new column is Nullable and you want to update existing records with the provided default value (0 in this example). If your Column is NOT NULL, then it will automatically update existing records with the provided default value, whether you specify WITH VALUES or not.

How Inserts work with a Default-Constraint:

• If you insert a record into table Item and do not specify StockQty's value, then it will be set to the default value 0.
• If you insert a record and specify StockQty'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.

References:

• https://stackoverflow.com/questions/92082/add-a-column-with-a-default-value-to-an-existing-table-in-sql-server