Unique Indexes on Nullable Columns: The NULL Behavior Trap

The Surprise

CREATE TABLE dbo.Employees (
    EmployeeId INT PRIMARY KEY,
    BadgeNumber VARCHAR(20) NULL
);

CREATE UNIQUE INDEX UX_Employees_BadgeNumber
    ON dbo.Employees (BadgeNumber);

INSERT INTO dbo.Employees VALUES (1, 'B-1001');
INSERT INTO dbo.Employees VALUES (2, NULL);
INSERT INTO dbo.Employees VALUES (3, NULL);   -- succeeds — no violation

SELECT * FROM dbo.Employees;
-- EmployeeId 2 and 3 BOTH have NULL BadgeNumber, and the unique index allowed it

To anyone coming from a mental model of "unique means no duplicates," this looks like a bug. It isn't — it's SQL Server implementing the ANSI SQL standard's three-valued logic consistently. NULL means "unknown," and by that logic, one unknown value is never equal to another unknown value — including for the purposes of a uniqueness check. SQL Server allows any number of NULLs in a unique index for exactly the same reason NULL = NULL evaluates to unknown rather than TRUE in a WHERE clause.

Why This Actually Matters

The badge number example above is realistic: a nullable "external reference" column — badge number, external customer ID, an optional SSO subject identifier — that's supposed to be unique whenever it's populated, but is legitimately absent for many rows. A plain UNIQUE index does not enforce "unique whenever present." It enforces "unique among non-NULL values, with no limit on how many rows can share NULL."

If your business rule is genuinely "every employee must have exactly one badge number, full stop, no exceptions," that's a NOT NULL UNIQUE column and this article doesn't apply to you. The trap only bites when the column is legitimately nullable and you also need uniqueness among the values that do exist — a very common combination that a plain unique index does not actually enforce the way most people assume.

The Fix: A Filtered Unique Index

SQL Server's filtered index feature lets you scope a unique index to only the rows where the column is populated — which is precisely "unique among non-NULL values, and I don't care how many NULLs there are" made explicit and enforced:

DROP INDEX UX_Employees_BadgeNumber ON dbo.Employees;

CREATE UNIQUE INDEX UX_Employees_BadgeNumber
    ON dbo.Employees (BadgeNumber)
    WHERE BadgeNumber IS NOT NULL;

INSERT INTO dbo.Employees VALUES (4, 'B-1001');
-- Msg 2601: Cannot insert duplicate key row... the duplicate key value is (B-1001)
-- This one now correctly fails

Rows where BadgeNumber IS NULL are simply outside the index's filter — they aren't indexed at all, so they can't violate anything. Rows where it's populated are fully enforced for uniqueness. This is exactly "at most one" for the values that matter, with unlimited NULLs for the rows where the value doesn't apply.

Filtered Index vs. a CHECK Constraint Workaround

Before filtered indexes existed (pre-SQL Server 2008), the common workaround was a computed column trick or application-level checking. Neither is worth reaching for now — the filtered index is simpler, is enforced by the engine itself (not by application discipline), and the optimizer can use it like any other index for queries that include the same filter predicate.

The Reverse Trap: Composite Unique Constraints

The same three-valued logic applies to multi-column unique indexes, and it's easier to miss there. A unique index on (TenantId, ExternalRef) treats a row with ExternalRef = NULL as never conflicting with any other row for that TenantId — even a second, third, or hundredth row with the same TenantId and a NULL ExternalRef. If the actual requirement is "unique per tenant, whenever an external ref exists," the fix is the same filtered pattern, just with the composite key:

CREATE UNIQUE INDEX UX_Mapping_TenantExternalRef
    ON dbo.ExternalMapping (TenantId, ExternalRef)
    WHERE ExternalRef IS NOT NULL;

Checking What You Already Have

If you suspect an existing unique index on a nullable column might already have accumulated duplicate NULLs (it will have, if the column is nullable and populated over time), check before adding a filtered index — you can't add a unique filtered index over data that already violates it:

-- Does this column already have more than one NULL?
SELECT COUNT(*) AS NullCount
FROM dbo.Employees
WHERE BadgeNumber IS NULL;

-- Which INDEXES on this table are unique but NOT filtered — candidates to review
SELECT
    i.name AS index_name,
    i.is_unique,
    i.has_filter,
    i.filter_definition
FROM sys.indexes AS i
WHERE i.object_id = OBJECT_ID('dbo.Employees')
  AND i.is_unique = 1;

If duplicates already exist and the business rule genuinely requires closing the gap, you'll need to resolve the existing duplicates (assign real values, or a decision on which row keeps which value) before the filtered unique index can be created — it will fail to build over data that already violates it, which is exactly the safety check you want before enforcing a rule retroactively.

The Bottom Line

A plain unique index on a nullable column in SQL Server does not prevent duplicate NULLs — this is standard-compliant three-valued-logic behavior, not a bug, but it surprises almost everyone the first time they hit it. If the real requirement is "unique whenever a value is present," say so explicitly with a filtered unique index (WHERE column IS NOT NULL) rather than relying on a plain unique constraint to do something it was never designed to do.