Missing identifiers need a different uniqueness rule from supplied identifiers. SQL Server needs a filtered unique index to express that rule with many NULLs in one column.
State the Uniqueness Rule Precisely
For a single nullable column, a normal SQL Server unique constraint permits only one NULL key. That behavior surprises applications expecting every missing identifier to remain outside uniqueness enforcement. Write the intended rule before selecting the database object.
The requirement here is uniqueness only among supplied values. NULL means the identifier has not been provided. An empty string is a supplied string unless your application explicitly normalizes it to NULL.
I separate optionality from uniqueness when reviewing a schema. I also test the second missing value instead of checking only the first insert. The first NULL gets a warm welcome; the next one discovers the actual policy.
Composite unique keys need their own explanation. Uniqueness applies to the complete key combination, including its NULL positions. Do not extend the single-column rule into a claim that every composite index allows only one NULL-containing row.
Demonstrate the Ordinary Constraint Behavior
Run the example in a separate test database. The second insert is meant to fail, and the CATCH block shows the error so the next demonstration can continue. The sample does not disable constraints or alter application tables.
DROP TABLE IF EXISTS dbo.SingleNullCode;
CREATE TABLE dbo.SingleNullCode
(
PersonId int NOT NULL PRIMARY KEY,
ExternalCode varchar(30) NULL,
CONSTRAINT UQ_SingleNullCode_Code UNIQUE (ExternalCode)
);
INSERT dbo.SingleNullCode(PersonId, ExternalCode) VALUES (1, NULL);
BEGIN TRY
INSERT dbo.SingleNullCode(PersonId, ExternalCode) VALUES (2, NULL);
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;The second insert fails with error 2627, and the message names NULL as the duplicate key value. That rejection is expected behavior, rather than evidence of a damaged index.
Observe which rows remain after the failed statement. In this standalone example, the first committed insert remains. Transaction ownership and error handling need separate attention when an application performs a larger multi-statement operation.
A pre-insert application query cannot replace database uniqueness enforcement. Another session can insert the same value between the check and the write. Keep the database rule responsible for rejecting competing duplicates.
Create a Filtered Unique Index for Many NULLs
A filtered index includes only rows satisfying its filter. WHERE ExternalCode IS NOT NULL excludes missing identifiers from this index. UNIQUE then enforces uniqueness across the remaining keys. The last insert repeats A100 on purpose, so expect it to fail.
SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET QUOTED_IDENTIFIER ON;
SET NUMERIC_ROUNDABORT OFF;
DROP TABLE IF EXISTS dbo.OptionalCode;
CREATE TABLE dbo.OptionalCode
(
PersonId int NOT NULL PRIMARY KEY,
ExternalCode varchar(30) NULL
);
CREATE UNIQUE INDEX UX_OptionalCode_NonNull
ON dbo.OptionalCode(ExternalCode)
WHERE ExternalCode IS NOT NULL;
INSERT dbo.OptionalCode(PersonId, ExternalCode)
VALUES (1, NULL), (2, NULL), (3, 'A100');
BEGIN TRY
INSERT dbo.OptionalCode(PersonId, ExternalCode)
VALUES (4, 'A100');
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;The two NULL rows lie outside the filtered index. The repeated A100 fails with error 2601 because it conflicts with a key inside it. You have expressed the intended rule while allowing many NULLs in the base table.
The object is a unique index rather than a filtered UNIQUE constraint. SQL Server does not support adding a filter to a UNIQUE constraint definition. Describe the implementation accurately when documenting the schema or reviewing generated definitions.
Creating the index over existing data can fail if supplied codes already contain duplicates. Resolve those conflicts according to business ownership before deployment. Do not delete arbitrary rows just to make the index creation succeed.
Preserve the Required Connection Settings
Filtered indexes require the displayed SET options during creation and relevant data modifications. Application connections must maintain the required settings, not only the administrator session that created the index. Incompatible settings can cause data modification errors.
Inspect an actual application-equivalent connection rather than assuming every client uses SSMS defaults. The following query reports several session options. The creation block lists the full required set explicitly.
SELECT SESSIONPROPERTY('ANSI_NULLS') AS AnsiNulls,
SESSIONPROPERTY('ANSI_WARNINGS') AS AnsiWarnings,
SESSIONPROPERTY('ARITHABORT') AS ArithAbort,
SESSIONPROPERTY('QUOTED_IDENTIFIER') AS QuotedIdentifier,
SESSIONPROPERTY('NUMERIC_ROUNDABORT') AS NumericRoundAbort;SET ANSI_NULLS OFF is deprecated, but SQL Server 2025 still accepts it on a session. Explicitly documenting the contract remains useful for mixed client environments. Check the installed version rather than relying on remembered legacy defaults.
Some required settings affect optimizer eligibility as well as modification behavior. A query with unsuitable settings can miss an otherwise useful filtered index. Diagnose that separately from whether the index continues enforcing the stored data rule.
Put connection-setting validation into the real application test. Include insert, update, and delete paths that touch indexed rows. A successful read-only administrator query does not prove that the application's writes will succeed.
Write Queries That Describe the Indexed Subset
A selective equality predicate on ExternalCode naturally restricts results to supplied values. An explicit IS NOT NULL predicate can make the subset especially clear. Matching data types also avoid unnecessary conversion complications.
DECLARE @Code varchar(30) = 'A100';
SELECT PersonId, ExternalCode
FROM dbo.OptionalCode
WHERE ExternalCode = @Code
AND ExternalCode IS NOT NULL;
SELECT PersonId, ExternalCode
FROM dbo.OptionalCode
WHERE ExternalCode IS NULL;The first query can use the filtered index when the optimizer chooses that access path. The second needs access to rows excluded from the index. A scan for missing identifiers does not show that filtered uniqueness has failed.
A tiny sample table can still justify a scan for the equality query. Review representative plans and reads instead of demanding a seek from every demonstration. The integrity benefit exists independently of the chosen read operator.
Avoid optional-filter predicates that mix supplied-value searches with all-row searches in one unclear expression. Different query shapes can require different plans. Separate those requirements when predictable index eligibility matters.
Test Many NULLs Through Updates and Collation
Uniqueness uses the indexed column's comparison semantics. A case-insensitive collation can treat A100 and a100 as equal. Decide whether case differences identify distinct business values before selecting or preserving the collation.
Test changing a NULL row to an existing code and changing a supplied code back to NULL. The first must be rejected, while the second leaves the filtered subset. Also test two simultaneous attempts to claim the same new code.
Should whitespace-only or empty identifiers become NULL, or should they be rejected? Define that rule in the application and database contract. The filtered index cannot guess what an empty string means to the business.
A filtered unique index also has limitations as a referenced uniqueness target for foreign-key design. Review that relationship separately when another table references the optional code. A stable mandatory surrogate key can provide a clearer parent relationship.
Finish with a schema test showing duplicate supplied values rejected and multiple missing values accepted. Keep many NULLs as an explicit allowed condition rather than an accidental loophole. That makes the optional identifier rule both understandable and enforceable.
Related reading on this blog: Unique Indexes on Nullable Columns and NOT IN With a NULL in the List Returns No Rows.
Optional uniqueness is not ordinary uniqueness with an exception, it is uniqueness over a clearly defined subset of rows.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
