How to Add a Column with a Default Value to an Existing Table in SQL Server

Let’s use the following table of Employees as an example:

CREATE TABLE Employee
(
    Id INT PRIMARY KEY,
    Name VARCHAR(255)
);

INSERT INTO Employee(Id, Name)
VALUES
    (1, 'Salim'),
    (2, 'Sara'),
    (3, 'John'),
    (4, 'Andy'),
    (5, 'Mohamed'),
    (6, 'Salma'),
    (7, 'Amir'),
    (8, 'Tayeb');

How do we add a new column, Gender, with no null values allowed and a default value of

If no null values are allowed for a new column, you can specify a default value for the column. The query below will add the new Gender column:

ALTER TABLE
    Employee
ADD
    Gender VARCHAR(20) NOT NULL DEFAULT 'Undefined';

However, it is better to name both the constraints so you can refer to them by name if you want to change them in the future:

ALTER TABLE
    Employee
ADD
    Gender VARCHAR(20)
        CONSTRAINT cnstrt_not_null_gender NOT NULL
        CONSTRAINT cnstrt_default_gender DEFAULT 'Undefined';

Here are the contents of the altered table:

SELECT * FROM Employee;

Id	Name	Gender
1	Salima	Undefined
2	Sara	Undefined
3	John	Undefined
4	Andy	Undefined
5	Mohamed	Undefined
6	Salma	Undefined
7	Amir	Undefined
8	Tayeb	Undefined

How To Allow Null Values

If you want to add a default value of Undefined to existing rows and new rows, but still want to allow entering NULL for gender if explicitly specified, you can do this:

ALTER TABLE
    Employee
ADD
   Gender VARCHAR(20)
   CONSTRAINT cnstrt_default_gender DEFAULT 'Undefined' WITH VALUES;

In this new ALTER statement, we no longer require Gender to be NOT NULL. We specify that a DEFAULT value is set only when no value is given, by the WITH VALUES phrase.

Now if we enter the following:

INSERT INTO Employee(Id, Name, Gender)
VALUES (20, 'Farid', NULL);

Farid will have NULL gender.

But if we enter:

INSERT INTO Employee(Id, Name)
VALUES (20, 'Sofia');

Sofia will have an Undefined gender.