Answers by Sentry

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

Richard C.

The Problem

You want to add a column to an existing table in Microsoft SQL Server. How do you do it?

If you want the column to disallow null values, the ALTER TABLE statement will fail because all the existing rows will have no values for the new column.

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

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

INSERT INTO Person(Id, Name)
VALUES
    (1, 'Amir'),
    (2, 'Sofia'),
    (3, 'Aya'),
    (4, 'Mateo'),
    (5, 'Leila'),
    (6, 'Yara'),
    (7, 'Ndidi'),
    (8, 'Santiago');

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

The Solution

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

ALTER TABLE
    Person
ADD
    Gender VARCHAR(20) NOT NULL DEFAULT 'Unspecified';

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

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

Here are the contents of the altered table:

SELECT * FROM Person;
IdNameGender
1AmirUnspecified
2SofiaUnspecified
3AyaUnspecified
4MateoUnspecified
5LeilaUnspecified
6YaraUnspecified
7NdidiUnspecified
8SantiagoUnspecified

How To Allow Null Values

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

ALTER TABLE
    Person
ADD
   Gender VARCHAR(20)
   CONSTRAINT cnstrt_default_gender DEFAULT 'Unspecified' 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 Person(Id, Name, Gender)
VALUES (20, 'Bob', NULL);

Bob will have NULL gender.

But if we enter:

INSERT INTO Person(Id, Name)
VALUES (20, 'Bob');

Bob will have an Unspecified gender.

Loved by over 4 million developers and more than 90,000 organizations worldwide, Sentry provides code-level observability to many of the world’s best-known companies like Disney, Peloton, Cloudflare, Eventbrite, Slack, Supercell, and Rockstar Games. Each month we process billions of exceptions from the most popular products on the internet.

learn more about sentryjoin our discord
Share on Twitter
Bookmark this page
Ask a questionJoin the discussion

Related Answers

A better experience for your users. An easier life for your developers.

Try Sentry For FreeRequest a Demo
    TwitterGitHubDribbbleLinkedinDiscord
© 2024 • Sentry is a registered Trademark
of Functional Software, Inc.