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

Using Non-NULL columns

When you're adding non-null columns, SQL Server is your best mate! It automatically fills them up with the default value. For any nullable column needing the default value, don't forget to tack on the WITH VALUES toolkit:

ALTER TABLE YourTable ADD NewColumn DataType DEFAULT 'DefaultValue' WITH VALUES;


Named constraints for your sanity

Maintain your sanity by giving your constraints descriptive names. This will help you modify or remove them in the future:

ALTER TABLE YourTable ADD NewColumn DataType 
CONSTRAINT DF_YourTable_NewColumn DEFAULT 'DefaultValue';


The DF_ is a legacy from naming convention of ye olden times - feel free to replace it with a prefix of your liking.

Insert action after adding new column

Doing an insert operation after adding a new column doesn't require its mention; SQL Server's default values have your back:

INSERT INTO YourTable (ExistingColumn1, ExistingColumn2) VALUES ('Value1', 'Value2');


In case the new column is nullable and you insert NULL, SQL Server will understand that you like to live dangerously and override the default.

Considerations during "Table Surgery"

Certain generations of SQL Server (2000 and 2005 to be exact) need the GO statement to split up operations:

ALTER TABLE YourTable ADD NewColumn DataType;
GO
ALTER TABLE YourTable ADD CONSTRAINT DF_YourTable_NewColumn DEFAULT 'DefaultValue' FOR NewColumn;


Entering a non-nullable column? You might need to plan a strategy to fill your existing rows:

ALTER TABLE YourTable ADD NewColumn DataType NOT NULL DEFAULT 'IntermediateValue' WITH VALUES;


Your data type needs to be spot on, like VARCHAR(200) or INT, depending on the room your data needs.