Change Column From NULL to NOT NULL in SQL Server

To change a column from NULL to NOT NULL, fill every NULL value first. Then run ALTER COLUMN with the full data type and NOT NULL. If a single NULL remains, SQL Server stops the change with an error.

What the Change Means

A column that allows NULL accepts a missing value. A column that doesn’t allow NULL demands a value in every row. Changing a column from NULL to NOT NULL moves that demand onto every existing row. SQL Server checks them all. One NULL is enough to stop the change.

Teams make the change for good reasons. A required value becomes a data rule that the database enforces, instead of a rule that every application must remember. Queries also stop needing IS NULL tests for that column. The cost is the work before the ALTER: finding the NULLs, and deciding what replaces them.

Why the First Attempt Fails

The demo uses a database named NotNullDemo and a small contacts table. Two columns allow NULL, a phone number and a visit count. Two rows have no phone, and two rows have no visit count.

IF DB_ID(N'NotNullDemo') IS NULL CREATE DATABASE NotNullDemo;
GO
USE NotNullDemo;
GO
DROP TABLE IF EXISTS dbo.Contacts;
CREATE TABLE dbo.Contacts (
    ContactID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    FullName  nvarchar(60) NOT NULL,
    Phone     varchar(20) NULL,
    Visits    int NULL
);
INSERT INTO dbo.Contacts (FullName, Phone, Visits)
VALUES (N'Maya Lopez', '512-555-0101', 4), (N'Noah Kim', NULL, 2), (N'Priya Shah', '617-555-0144', NULL), (N'Sam Rivera', NULL, NULL);

The catalog view sys.columns records whether each column allows NULL. Check it before the change, so you can compare it afterward. A primary key column can’t allow NULL, which is why ContactID shows 0.

SELECT name AS ColumnName, is_nullable AS IsNullable FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.Contacts') ORDER BY column_id;

Now try the obvious change.

ALTER TABLE dbo.Contacts ALTER COLUMN Phone varchar(20) NOT NULL;

SQL Server refuses, because two rows hold NULL in that column. The message reads as follows. It is output, not code to run.

Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'Phone', table 'NotNullDemo.dbo.Contacts'; column does not allow nulls. UPDATE fails.
The statement has been terminated.

The wording ends with UPDATE fails, and the table stays unchanged.

Decide What Replaces the NULLs

Count the NULL values before you touch anything. The query counts them for both columns.

SELECT SUM(CASE WHEN Phone IS NULL THEN 1 ELSE 0 END) AS NullPhones,
       SUM(CASE WHEN Visits IS NULL THEN 1 ELSE 0 END) AS NullVisits
FROM dbo.Contacts;

The count is the easy part. The replacement value is a decision. NULL means unknown, and a replacement says something definite. A visit count of 0 is honest for a new contact. A phone value of none is a placeholder that every report and screen must understand. Zeros also change averages. An average that skips NULL comes out higher than one that counts zeros. Check the reports that read the column.

An empty string is another tempting choice for text. It isn’t NULL, so the column accepts it, but a search for missing values then needs two tests. Pick a value for each column, then replace the NULLs. Each statement touches only the rows that need it.

UPDATE dbo.Contacts SET Phone = 'none' WHERE Phone IS NULL;
UPDATE dbo.Contacts SET Visits = 0 WHERE Visits IS NULL;

Change the Columns and Add a Default

Run ALTER COLUMN again for each column. State the full data type each time, with the same length, because the statement redefines the column. Then add a default for the visit count, so new rows stay valid when an application leaves that column out. The demo orders the steps for easy reading. On a live system, add the default first.

ALTER TABLE dbo.Contacts ALTER COLUMN Phone varchar(20) NOT NULL;
ALTER TABLE dbo.Contacts ALTER COLUMN Visits int NOT NULL;
ALTER TABLE dbo.Contacts ADD CONSTRAINT DF_Contacts_Visits DEFAULT 0 FOR Visits;
SELECT name AS ColumnName, is_nullable AS IsNullable FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.Contacts') AND name IN (N'Phone', N'Visits') ORDER BY column_id;

Both columns now refuse NULL. Test the rules with two inserts. The first leaves out Visits, and the default fills it in.

INSERT INTO dbo.Contacts (FullName, Phone) VALUES (N'Ravi Patel', '212-555-0170');
SELECT ContactID, FullName, Phone, Visits FROM dbo.Contacts ORDER BY ContactID;

The second insert tries to store NULL in Phone, and the column rejects it.

INSERT INTO dbo.Contacts (FullName, Phone, Visits) VALUES (N'Ana Cruz', NULL, 1);

Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'Phone', table 'NotNullDemo.dbo.Contacts'; column does not allow nulls. INSERT fails.
The statement has been terminated.

Check the Application First

The database isn’t the only place that must change. Search the application code for inserts and updates that send NULL to the column. After the change, each one fails with Msg 515. Add the default first, fix the code that sends NULL, and make the column required last.

Check imports and scheduled jobs as well. A nightly load that leaves out the column keeps working only if the column has a default. Otherwise it fails, and nobody sees it until morning.

The Trap That Makes It Nullable Again

ALTER COLUMN replaces the whole definition. If you leave out NOT NULL, SQL Server doesn’t keep the old setting. It applies the session default, which allows NULL. A change of length is one reason to run the statement, and it quietly undoes the constraint.

ALTER TABLE dbo.Contacts ALTER COLUMN Phone varchar(30);
SELECT name AS ColumnName, is_nullable AS IsNullable FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.Contacts') AND name = N'Phone';

The column accepts NULL again, and no error warned about it. Write NOT NULL in every ALTER COLUMN statement for a column that must stay required.

ALTER TABLE dbo.Contacts ALTER COLUMN Phone varchar(30) NOT NULL;

Is NOT NULL Always Right?

You could argue that a made-up value is worse than an honest NULL. That’s a fair point. If the business has no true default, a placeholder such as none spreads through every report. In that case leave the column nullable, and handle NULL in the queries.

Choose NOT NULL when a missing value is a data error, such as an order without a customer. On a large table, time the change on a copy first. SQL Server must confirm that every row passes the new rule.

Make the script safe to run twice. The UPDATE statements touch only rows that still hold NULL, so a second run changes nothing. If the NULLs carried meaning, copy the key and the old value into a side table first. Then the change can be undone.

What to Remember

To go from NULL to NOT NULL, count the NULLs, decide the replacement, update, then alter. Repeat the full data type and NOT NULL in every ALTER COLUMN. Add a default when applications skip the column. Drop the demo database when you finish.

USE master;
GO
ALTER DATABASE NotNullDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE NotNullDemo;

A NOT NULL column is not a clean table, it is a promise that someone decided what missing means.

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