Using Allow Nulls Flag

A NULL value means a column's value is unknown, hasn't been specified, or simply doesn't apply to that row, not zero, not an empty string, and not any other placeholder standing in for "nothing." Whether a given column is allowed to hold NULL at all is itself an attribute you specify, right alongside its datatype and size, and SQL Server 2025 gives you two ways to control it: the NULL/NOT NULL keywords on the column itself, and a session- or database-level default that applies when those keywords are left off entirely. The first option is the one you'll use on almost every column you ever define; the second exists mainly so you understand what happens on the rare occasion someone forgets to use the first.

It's tempting, especially for anyone coming from a programming background where a missing value sometimes has to be represented some other way, to reach for a sentinel instead of NULL: storing -1 for an unknown quantity, an empty string for a middle name nobody provided, or a placeholder date like 1900-01-01 for an event that hasn't happened yet. This almost always causes more problems than it solves. A sentinel value is indistinguishable from a real one unless every single query against that column remembers to filter it out specially, and nothing in the column's own definition documents that -1 is secretly meaningless. NULL exists precisely so "no value here" has its own unambiguous representation, one that IS NULL, aggregate functions, and join logic all already understand correctly without special-casing. Reach for NULL whenever a column's value is genuinely unknown or doesn't apply, and reserve actual data values, including zero, for data that's actually known.

The most direct, and most reliable, way to control nullability is to state it explicitly on the column, the same way an earlier lesson in this module recommended always specifying a datatype's length, precision, and scale rather than leaning on a default.

When creating a new table:

CREATE TABLE dbo.Employees (
    EmployeeID INT NOT NULL PRIMARY KEY, -- does not allow NULLs
    FirstName  NVARCHAR(50) NOT NULL,    -- does not allow NULLs
    MiddleName NVARCHAR(50) NULL,        -- explicitly allows NULLs
    HireDate   DATE NOT NULL
);

When adding a new column to an existing table:

ALTER TABLE dbo.Employees
ADD PhoneNumber VARCHAR(20) NULL;

When changing an existing column's nullability: ALTER COLUMN requires you to restate the column's full datatype along with the new NULL/NOT NULL setting, not just the nullability keyword on its own.

-- allow HireDate to be NULL going forward
ALTER TABLE dbo.Employees
ALTER COLUMN HireDate DATE NULL;

-- tighten MiddleName to require a value
ALTER TABLE dbo.Employees
ALTER COLUMN MiddleName NVARCHAR(50) NOT NULL;

That second statement matters in practice: SQL Server won't let you tighten a column to NOT NULL while existing rows still hold NULL in it. If even one row in Employees currently has no MiddleName on file, the statement fails:

Msg 515, Level 16, State 2
Cannot insert the value NULL into column 'MiddleName', table 'YourDatabase.dbo.Employees';
column does not allow nulls. UPDATE fails.
The statement has been terminated.

You'll need to either supply real values for those rows first or decide on a default to backfill them with, which is exactly what the next lesson in this module covers.

In the meantime, a simple two-step pattern handles the common case where you're willing to fill the gaps with a single placeholder value rather than researching each row's true value individually: update the existing NULLs first, then tighten the column.

-- Step 1: backfill existing NULLs with a known placeholder
UPDATE dbo.Employees
SET MiddleName = N'(none)'
WHERE MiddleName IS NULL;

-- Step 2: now the column can safely be tightened
ALTER TABLE dbo.Employees
ALTER COLUMN MiddleName NVARCHAR(50) NOT NULL;

This only makes sense when a single shared placeholder genuinely represents every row's situation correctly; if different rows are missing a value for different reasons, each one probably deserves its own real value rather than being folded into one generic stand-in.

If you'd rather use the graphical designer instead of T-SQL, SQL Server Management Studio exposes the same setting directly, labeled Allow Nulls:

1. In Object Explorer, expand your database and the Tables folder.
2. Right-click the table and choose Design, or right-click Tables and choose New > Table... for a new one.
3. In the column grid, check or uncheck the Allow Nulls box for the column you're editing.
4. Press Ctrl+S, or click the Save icon, to apply the change.

One SSMS quirk is worth knowing before you hit it unexpectedly: unchecking Allow Nulls on a table that already has data, or inserting a new column into the middle of an existing table's column list, sometimes requires SSMS to drop and recreate the entire table behind the scenes to apply the change. If that triggers a "Saving changes is not permitted" error, the fix is under Tools > Options > Designers > Table and Database Designers, unchecking Prevent saving changes that require table re-creation. For a production database, though, running the equivalent ALTER TABLE statement yourself in T-SQL is the safer route: you can see exactly what it's going to do before it does it, rather than trusting the designer to rebuild a live table correctly behind the scenes.

If a column definition leaves off both NULL and NOT NULL, SQL Server falls back to a default, controlled by a session or database setting rather than a fixed rule:

Scope	Statement	Effect
Session	SET ANSI_NULL_DFLT_ON ON;	omitted columns default to NULL
Session	SET ANSI_NULL_DFLT_OFF ON;	omitted columns default to NOT NULL
Database	ALTER DATABASE YourDatabaseName SET ANSI_NULL_DEFAULT ON;	sets the database's own default to NULL, used by any session that hasn't overridden it
Database	ALTER DATABASE YourDatabaseName SET ANSI_NULL_DEFAULT OFF;	sets the database's own default to NOT NULL

Relying on either of these is asking for trouble, though, for the same reason an earlier lesson gave for always specifying a datatype's length, precision, and scale explicitly: the behavior depends on a setting that lives somewhere else entirely, invisible at the point where you're actually reading the CREATE TABLE statement, and it can differ from one connection, tool, or environment to the next. A table scripted on one developer's machine can end up with different nullability than the same script run against a server with a different session default, with nothing in the script itself showing why. Writing NULL or NOT NULL explicitly on every column removes the ambiguity entirely, regardless of what any session or database default happens to be set to.

One rule here isn't optional: no column that's part of a PRIMARY KEY can allow NULL. SQL Server enforces NOT NULL on every primary key column automatically, since a primary key exists specifically to uniquely identify a row, and an unknown value can't do that. Every other column, including a foreign key, is free to allow NULL if the data genuinely calls for it. A nullable foreign key is a normal, common design choice: it represents an optional relationship rather than a required one.

CREATE TABLE dbo.Employees (
    EmployeeID INT NOT NULL PRIMARY KEY,         -- primary key: always NOT NULL
    FirstName  NVARCHAR(50) NOT NULL,
    LastName   NVARCHAR(50) NOT NULL,
    ManagerID  INT NULL                          -- nullable foreign key: optional relationship
        CONSTRAINT FK_Employees_Manager
        FOREIGN KEY REFERENCES dbo.Employees(EmployeeID)
);

Here, ManagerID references another row in the same Employees table, letting NULL represent an employee who doesn't report to anyone, the company's most senior person, say, rather than forcing every employee to point to a manager that doesn't exist. Had this been a required relationship instead, every employee reporting to someone without exception, ManagerID would be declared NOT NULL instead, making the requirement visible directly in the table definition rather than left to application-level validation.

That last point is really the whole case for nullability as a design decision rather than an afterthought. An earlier lesson in this course covered domain integrity, the idea that a column's declared attributes should restrict it to only the values that are genuinely valid for it. Nullability is a domain-integrity rule like any other: deciding whether NULL belongs in a column's domain is exactly as deliberate a choice as deciding its datatype or its length, and it's enforced by SQL Server the same way, at the moment data is written, rather than something application code has to remember to check on its own every time.

Allowing NULL on a column changes how you have to query it. NULL doesn't equal anything, not even another NULL, so WHERE ManagerID = NULL never matches a single row, no matter how many employees genuinely have no manager on file; IS NULL and IS NOT NULL exist specifically to test for this case, since ordinary comparison operators can't.

-- wrong: matches nothing, ever
SELECT * FROM dbo.Employees WHERE ManagerID = NULL;

-- correct
SELECT * FROM dbo.Employees WHERE ManagerID IS NULL;

Aggregate functions behave differently around NULL too: COUNT(*) counts every row regardless of its contents, while COUNT(ManagerID) counts only the rows where ManagerID actually has a value, silently skipping the NULL ones. Keeping this in mind when a column is first declared nullable saves a surprise later, when a report's totals don't match row counts the way you expected.

When you need to display or calculate with a nullable column without a raw NULL showing up in the result, COALESCE() substitutes a fallback value in its place without changing anything in the underlying table:

SELECT EmployeeID, FirstName, LastName,
       COALESCE(MiddleName, N'(none)') AS MiddleNameDisplay
FROM dbo.Employees;

COALESCE() only affects what the query returns; the stored value in MiddleName is still genuinely NULL until something explicitly updates it, which is an important distinction from the backfill pattern described above, that one permanently changes the stored data, while COALESCE() only changes how a particular query presents it.

Before moving on to the next lesson, click the Quiz link below to check your knowledge of the material covered so far on datatypes, length, precision, scale, and nulls, with a short, multiple-choice quiz.
Datatypes Length Precision Scale Nulls - Quiz

In the next lesson, you'll learn how to specify a default value for a column, useful both for new rows that don't supply one and for backfilling a column you're about to tighten to NOT NULL.