How Default Value and Nullable Column Works? – Interview Question of the Week #129

Interview question: What happens when a column is nullable and has a default, especially if it is added after rows already exist?

Answer: A default supplies a value when a new INSERT omits that column. For rows that already exist when you add a nullable column, SQL Server leaves them NULL unless you request WITH VALUES. A newly added NOT NULL column with a default gets the default for existing rows because those rows cannot remain NULL.

I first heard this from another interviewer while helping an organization hire DBAs and developers. Before the candidate answered, I asked the panel to clarify whether the table already had rows. That small detail changes the answer.

First test: insert after both columns exist

-- Open a fresh query window; do not replace an existing temporary table.
IF OBJECT_ID(N'tempdb..#CreatedFirst') IS NOT NULL
    THROW 50126, 'Temporary demo table exists. Open a fresh window.', 1;

CREATE TABLE #CreatedFirst
(
    ID int NOT NULL,
    NotNullCol int NOT NULL DEFAULT (1),
    NullCol int NULL DEFAULT (1)
);

INSERT INTO #CreatedFirst (ID) VALUES (1);
SELECT ID, NotNullCol, NullCol FROM #CreatedFirst;
DROP TABLE #CreatedFirst;

Both omitted columns receive 1. Nullable means an explicit NULL is allowed; it does not disable the default for an omitted value.

Second test: add columns after a row exists

IF OBJECT_ID(N'tempdb..#AddedLater') IS NOT NULL
    THROW 50129, '#AddedLater already exists. Open a fresh window.', 1;

CREATE TABLE #AddedLater (ID int NOT NULL);
INSERT INTO #AddedLater (ID) VALUES (1);

ALTER TABLE #AddedLater
ADD NotNullCol int NOT NULL DEFAULT (1);

ALTER TABLE #AddedLater
ADD NullCol int NULL DEFAULT (1);

SELECT ID, NotNullCol, NullCol FROM #AddedLater;
DROP TABLE #AddedLater;

The existing row now has NotNullCol = 1 and NullCol = NULL. The original comparison graphic captures the difference between the two tests:

If those existing rows should receive the default too, say so in the schema change:

ALTER TABLE dbo.YourTable
ADD NewNullableCol int NULL DEFAULT (1) WITH VALUES;

Plan that change carefully on a large production table because filling existing rows can take work and locks. Also remember that a default is used when the column is omitted, not when the application explicitly inserts NULL into a nullable column.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.