NOT NULL and DEFAULT in SQL Server: Your First Line of Data Defense

Two lightweight rules you can put directly on a column, before the full constraint system (primary keys, foreign keys, CHECK) enters the picture in Chapter 6. Small as they look, they prevent an enormous share of real-world data-quality bugs — the kind that surface as a mysterious blank field in a report three months later.

What NULL Actually Means

NULL isn’t zero, an empty string, or “false” — it specifically means unknown / not applicable / not yet provided. This has real, non-obvious consequences: NULL = NULL evaluates to unknown, not true, which is why you can’t write WHERE middle_name = NULL and must instead write WHERE middle_name IS NULL. Any arithmetic or string concatenation touching a NULL also produces NULL — 5 + NULL is NULL, not 5. This single fact explains a large share of “why is my total wrong” bugs beginners hit later with SUM and string building.

The Two Rules

• NOT NULL — forces every row to have a real value in that column; rejects the insert otherwise
• DEFAULT — auto-fills a value when one isn’t provided in the INSERT (a literal, or the result of a function call)

Seeing Both in Action

CREATE TABLE dbo.Support_Ticket (
    ticket_id    INT IDENTITY(1,1) PRIMARY KEY,
    subject      NVARCHAR(200) NOT NULL,
    status       NVARCHAR(20)  NOT NULL DEFAULT 'open',
    priority     TINYINT       NOT NULL DEFAULT 2,
    created_at   DATETIME2     NOT NULL DEFAULT SYSDATETIME(),
    ticket_guid  UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID()
);

-- status, priority, created_at, and ticket_guid all fill themselves in automatically:
INSERT INTO dbo.Support_Ticket (subject) VALUES ('Cannot log in');
SELECT * FROM dbo.Support_Ticket;

DEFAULT isn’t limited to literal values — SYSDATETIME() and NEWID() above are function calls, evaluated fresh at insert time for every row. This is the standard way to auto-timestamp a row or generate a GUID without the application needing to supply either.

Adding NOT NULL to a Column That Already Has Data

This trips up almost everyone the first time: you can’t simply tighten an existing nullable column if any row already has NULL in it.

-- Table already has rows, some with NULL priority
ALTER TABLE dbo.Support_Ticket ALTER COLUMN priority TINYINT NOT NULL;
-- Msg 515: Cannot insert the value NULL into column 'priority' ...

-- The real fix: backfill first, then tighten
UPDATE dbo.Support_Ticket SET priority = 2 WHERE priority IS NULL;
ALTER TABLE dbo.Support_Ticket ALTER COLUMN priority TINYINT NOT NULL;

This exact two-step pattern — backfill, then constrain — is how real migrations tighten a loosely-defined column once you’ve decided it should never be empty going forward.

Handling NULLs You Already Have: ISNULL and COALESCE

Sometimes a column legitimately should allow NULL (a customer’s optional middle name), but you still need a sensible display value when querying it:

SELECT subject, ISNULL(status, 'unknown') AS status FROM dbo.Support_Ticket;

-- COALESCE takes any number of arguments, returns the first non-NULL one
SELECT COALESCE(preferred_name, first_name, 'Guest') AS display_name FROM dbo.Customer;

ISNULL is SQL-Server-specific and takes exactly two arguments; COALESCE is ANSI-standard, works across database engines, and accepts any number of fallback values — generally the better default choice unless you have a specific reason to use ISNULL.

What Happens Without Them

Without these two simple rules, incomplete or nonsensical rows slip in silently — a support ticket with no subject, an order status that’s blank instead of a real state. You end up writing defensive checks in application code that the database could have enforced for free, and that check is only as good as every application and every developer remembering to write it, every single time. A NOT NULL constraint never forgets.