How to create a unique index on a NULL column?

Solution for SQL Server 2008 and Newer: Filtered Indexes

SQL Server 2008 introduced the concept of filtered indexes. These are particularly useful to enforce uniqueness among non-NULL values while keeping the NULL values intact.

-- I feel the need - the need for (uniqueness among non-null) speed!
CREATE UNIQUE INDEX ix_notnull ON YourTable(col) WHERE col IS NOT NULL;


Solutions for Other SQL Versions: Calculated Columns

Before SQL Server 2008, or in RDBMS that don't support filtered indexes, calculated columns can be used instead. This creates a new column that substitutes NULLs with unique values, preserving the unique constraint.

Consider the NULLbuster pattern:

-- It's NULL ... Not any more!
ALTER TABLE YourTable 
ADD SpecialColumn AS COALESCE(col, CAST((SELECT COUNT(*) FROM YourTable WHERE col IS NULL) AS UNIQUEIDENTIFIER));

CREATE UNIQUE INDEX SpecialIndex ON YourTable(SpecialColumn);


In this approach, NULL entries transform into unique values, ensuring the uniqueness constraint doesn't stumble over them.

Extending Knowledge: Additional Strategies

Triggers for Uniqueness Checks

If the filtered indexes or calculated columns aren't an option, triggers make a viable alternative. Be aware though - they often come with a performance overhead due to the checks they perform on every insert or update.

Tackling Unique Constraints on Nullable Columns

When a unique constraint across both NULL and non-NULL values is required, a composite key including a dummy value along with your target column could be a good workaround.

Support across SQL dialects

Partial indexes are supported across other RMDBS like PostgreSQL and SQLite. Syntax might vary, however, the usage remains the same.

Tips for a Smoother Experience

Long live simplicity!

Remember, combining methods like using a filtered index and a trigger might seem tempting, but it often ends in confusion. Opt for the simplest solution that serves the purpose.

Retouching the Nullbuster Method

For older versions of SQL Server or RMDBS without filtered index support, the nullbuster method comes handy. Here, a sequence or unique identifier map NULLs to unique values.

Stay clear of Collision Course

When applying Nullbuster or similar techniques, ensure the unique value generator for NULLs doesn't collide with the non-NULL data to maintain uniqueness.