Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

Allow Duplicate Nulls in a Unique Index - SQL Server

RSS
Modified on Fri, Nov 14, 2014, 3:17 PM by Administrator Categorized as 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

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2018, Patrick Jasinski.