Applying UNIQUE indexes on columns with Multiple NULLS April 7, 2012Posted by fofo in SQL Server 2008.
Tags: filtered indexes, unique indexes
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!!!!