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

Page History: Allow Duplicate Nulls in a Unique Index - SQL Server

Compare Page Revisions



« Older Revision - Back to Page History - Newer Revision »


Page Revision: Wed, Jul 30, 2014, 11:08 AM


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 index.

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-2024, Patrick Jasinski.