Applies to SQL Server 2019, 2022 and 2025.

Getting an add column with default change right comes down to one word: NULL or NOT NULL. When you add a column with a default to a table that already has rows, that word decides what ends up in the existing rows. I’ve seen release scripts go out on the assumption that the default would be applied everywhere, only for a report the next morning to show blanks in every old row. The rule is simple once you know it, but it isn’t the one most people guess.

How add column with default behaves on existing rows

A DEFAULT constraint is about future inserts. It tells SQL Server what to put in the column when an INSERT doesn’t supply a value. Whether it also touches the rows already in the table depends on the column’s nullability:

• NOT NULL column with a DEFAULT: every existing row gets the default value. It has to, because the column can’t be left empty. If you try to add a NOT NULL column without a default to a table with rows, the statement fails.
• Nullable column with a DEFAULT: existing rows get NULL. The default only applies to rows inserted afterwards.
• Nullable column with a DEFAULT … WITH VALUES: existing rows get the default value, and the column still allows NULL from then on.

So WITH VALUES only matters for nullable columns. On a NOT NULL column it changes nothing, because the existing rows are filled anyway.

A quick demonstration

Here’s a small script I use to show this to developers. Run it in a test database.

CREATE TABLE dbo.DefaultDemo
(
    DemoID   int IDENTITY(1, 1) PRIMARY KEY,
    ItemName varchar(20) NOT NULL
);

INSERT INTO dbo.DefaultDemo (ItemName)
VALUES ('Existing row 1'), ('Existing row 2');

-- 1. Nullable, default only: old rows get NULL
ALTER TABLE dbo.DefaultDemo
    ADD StatusA char(1) NULL
    CONSTRAINT DF_DefaultDemo_StatusA DEFAULT 'N';

-- 2. Nullable, default WITH VALUES: old rows get 'N'
ALTER TABLE dbo.DefaultDemo
    ADD StatusB char(1) NULL
    CONSTRAINT DF_DefaultDemo_StatusB DEFAULT 'N' WITH VALUES;

-- 3. NOT NULL with a default: old rows get 'N'
ALTER TABLE dbo.DefaultDemo
    ADD StatusC char(1) NOT NULL
    CONSTRAINT DF_DefaultDemo_StatusC DEFAULT 'N';

-- A new row picks up the default in all three columns
INSERT INTO dbo.DefaultDemo (ItemName)
VALUES ('New row');

SELECT DemoID, ItemName, StatusA, StatusB, StatusC
FROM dbo.DefaultDemo
ORDER BY DemoID;

DROP TABLE dbo.DefaultDemo;

The two existing rows come back with NULL in StatusA and ‘N’ in StatusB and StatusC. The new row has ‘N’ in all three. Same default, three different results for the old data.

The common mistake

The mistake is adding a nullable column with a default and assuming the default has been applied to every row. It reads as though it should: you’ve said “the default is N”, so surely the column is N? But the existing rows were never inserted with that column missing, so the default never fired for them. They simply have nothing in the new column, which means NULL.

It usually comes to light later, when someone filters on WHERE StatusA = 'N' and the count is far too low, because NULL doesn’t equal anything. If you want the old rows filled, either make the column NOT NULL (my usual choice when the data model allows it) or add WITH VALUES.

The opposite slip is worth knowing too. People sometimes add WITH VALUES to a NOT NULL column “to be safe”. It does no harm, but it also does nothing; the rows were going to be filled regardless.

What it costs on a big table

On a table with millions of rows, the method matters. In Enterprise edition (and Developer, which has the same features), adding a NOT NULL column whose default is a runtime constant, such as a literal or GETDATE(), is a metadata-only change. SQL Server stores the default in the table’s metadata and doesn’t rewrite the rows, so it finishes almost instantly. The value is written to a row the next time that row is updated or the table is rebuilt.

Defaults that produce a different value per row, such as NEWID(), can’t work that way, so SQL Server updates every row while holding a schema modification (Sch-M) lock. That can block a busy system for a long time and generate a lot of log. Even the quick metadata-only version needs a brief Sch-M lock, so it can still queue behind a long-running query. I covered how those locks behave in Schema Locks and Online Index Maintenance.

Microsoft’s ALTER TABLE documentation lists the exceptions, including the LOB types (such as varchar(max) and xml) that are never added online.

Watch out for

• Unnamed constraints. If you write DEFAULT 'N' without CONSTRAINT name, SQL Server makes up a name like DF__DefaultDe__Statu__3B75D760. It differs on every server, which makes later DROP CONSTRAINT scripts painful. Always name it.
• WITH VALUES on large tables. On a nullable column it writes the value into every existing row, which is a size-of-data operation. Test the timing on a copy first.
• Standard edition. Microsoft documents the metadata-only add as an Enterprise feature. On Standard, test how long the change takes on a realistic copy of the table before you plan the release window.
• Dropping the column later. You must drop the default constraint before you can drop the column.

For more of my checklists and scripts, see the SQL Server DBA Handbook.

This topic came up in the SQLServerCentral Question of the Day: Adding new column with DEFAULT II.

Clive Roberts DBA