jump to navigation

Applying UNIQUE indexes on columns with Multiple NULLS April 7, 2012

Posted by fofo in SQL Server 2008.
Tags: ,
trackback

In this demo I would like to show you with a hands-on example how to apply unique  indexes on column that contains multiple NULL values.

Some people believe that this is not possible and think that if you want to enforce a unique index on a column, that column must have only one NULL value.I will show you that with the help of filtered indexes we can have unique indexes on columns with multiple NULL values.

Have a look in this post if you want to learn about the possible dangers of over indexing your tables.

We will need an instance of SQL Server running in our machine.You can download and install the free SQL Server Express edition from here.

In this example I will use tempdb.Launch SQL Server Management Studio and log in  SQL Server. In an new query window type

USE tempdb
 GO

CREATE TABLE MyTable
 (
 id INT IDENTITY(1, 1)
 PRIMARY KEY ,
 team VARCHAR(15)
 )

INSERT  MyTable
 VALUES  ( 'Liverpool' ),
 ( 'Barca' ),
 ( 'Real' ),
 ( 'Man Utd' )

SELECT * FROM MyTable

CREATE UNIQUE INDEX UNQ_team_MyTable ON MyTable(team)
 GO

INSERT MyTable Values (NULL)
 GO

Select * from MyTable

--this will create an error
 INSERT MyTable Values (NULL)
 GO

I create a table and I insert some values. Then I create a unique index on a column and insert a NULL value in it. So far so good. If I insert a second NULL value in the same table I get an error.In the same query window type


DROP INDEX  MyTable.UNQ_team_MyTable
 GO

--filtered index
 CREATE UNIQUE INDEX UNQ_team_MyTable ON MyTable(team)
 WHERE team IS NOT NULL
 GO
 --this will not fail
 INSERT mytable Values (NULL)
 GO
 --this will not fail
 INSERT mytable Values (NULL)
 GO
 --this will not fail
 INSERT mytable Values (NULL)
 GO
 --this will not fail
 INSERT mytable Values (NULL)
 GO

Select * from mytable
 GO

--this will fail
 INSERT mytable
 VALUES ('Barca')

--this will succeed

INSERT mytable Values (NULL)
 GO

I drop the index and create a filtered index on the column where the value is different than NULL.Then I insert multiple NULL values in the team column with no problem at all. If I try to insert a value in the team column of the table that already exists I will get an error from SQL Server. Note that filtered indexes are introduced in SQL Server 2008.Filters can only be applied to non-clustered indexes.

Hope it helps!!!!

Comments»

1. Graeme - July 18, 2012

Hi, I just came across this. Brilliant. I didn’t even know filtered indexes existed.

Thanks for an elegant solution to having null’s in unique indexes.

I’m also going to be investigating filtered indexes, I can think of quite a few existing scenario’s where they might be quite useful.

2. Miguel - August 28, 2015

Excellent, just what I wanted.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: