Allow Duplicate Nulls in a Unique Index - SQL Server

Overview¶

Within a unique index in SQL Server, duplicate null values are not allowed. However, you may have a legitimate business reason for wanting to have duplicate NULL values in an index. For example, a Person table might have a SSN column that's nullable. You might have the rule that if a Social Security number is specified it must be unique. This article explains how to implement this by using a filtered unique index. (Using this technique with a unique KEY/CONSTRAINT has not been investigated, but probably is not supported.)

Procedure¶

1. Assuming you have an existing index that needs to be adjusted, script it for DROP and CREATE.

2. Edit the script to insert a "filter" between the column list and the WITH clause, as shown below.

drop index UQ_Person_SSN on dbo.Person
go
CREATE UNIQUE NONCLUSTERED INDEX UQ_Person_SSN ON dbo.Person
(
	[SSN] ASC
) 
/* Manually add this next line */
where SSN is not null

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
    DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO