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

To add a column with a default value to an existing table in SQL Server, use the ALTER TABLE statement with the ADD clause and a DEFAULT constraint. The approach depends on whether the column allows NULL values and whether existing rows should inherit the default value.

1. Add a Column with a Default Value (Basic Syntax)

Scenario 1: Non-Nullable Column (NOT NULL)

All existing rows will automatically receive the default value because the column cannot be NULL.

ALTER TABLE [TableName]
ADD [ColumnName] [DataType] NOT NULL
CONSTRAINT [ConstraintName] DEFAULT [DefaultValue];

Example:
Add a non-nullable EmploymentStatus column with a default value of 'Active'.

ALTER TABLE Employees
ADD EmploymentStatus NVARCHAR(50) NOT NULL
CONSTRAINT DF_Employees_EmploymentStatus DEFAULT 'Active';

Scenario 2: Nullable Column (NULL)

By default, existing rows will have NULL for the new column. To assign the default value to existing rows, use WITH VALUES.

ALTER TABLE [TableName]
ADD [ColumnName] [DataType] NULL
CONSTRAINT [ConstraintName] DEFAULT [DefaultValue] WITH VALUES;

Example:
Add a nullable EmploymentStatus column with a default of 'Active' and populate existing rows.

ALTER TABLE Employees
ADD EmploymentStatus NVARCHAR(50) NULL
CONSTRAINT DF_Employees_EmploymentStatus DEFAULT 'Active' WITH VALUES;

2. Key Notes

• Naming the Constraint: Always name the default constraint (e.g., DF_TableName_ColumnName) for easier management.
• Existing Rows:
• For NOT NULL columns, the default is applied automatically.
• For NULL columns, use WITH VALUES to apply the default to existing rows.
• Performance: Adding a default to a NOT NULL column updates all existing rows, which may take time for large tables.

3. Verify the Result

Check if the column and default constraint are added:

-- View column details
EXEC sp_columns 'Employees';

-- View constraints
EXEC sp_helpconstraint 'Employees';

Examples

Add a CreatedDate Column with Default Current Time

ALTER TABLE Orders
ADD CreatedDate DATETIME NOT NULL
CONSTRAINT DF_Orders_CreatedDate DEFAULT GETDATE();

Add a Discount Column with Default 0 for Existing Rows

ALTER TABLE Products
ADD Discount DECIMAL(5,2) NULL
CONSTRAINT DF_Products_Discount DEFAULT 0 WITH VALUES;

Summary

Scenario	SQL Syntax
Add a non-nullable column	ALTER TABLE Table ADD Column DataType NOT NULL DEFAULT Value
Add a nullable column (existing rows get default)	ALTER TABLE Table ADD Column DataType NULL DEFAULT Value WITH VALUES
Add a nullable column (existing rows get NULL)	ALTER TABLE Table ADD Column DataType NULL DEFAULT Value

By following these steps, you can safely add columns with default values to existing tables in SQL Server.