Multiple nulls in a unique constraint

Today I tried to earn some points at http://www.sqlservercentral.com/Questions by answer the “question of the day” and I learned something new, or at least I haven’t thougt about it before. In SQL Server 2008 and later you can actually create a unique constraint that can contain multiple NULL values. This can be useful if you need to have unique values as long as the value is know.

It can be solved by filtered index, try the sample below.

USE [TEST] GO
CREATE TABLE [dbo].[tbl_test]( [id] [int] ) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX UNCI_test ON [dbo].[tbl_test](id)
WHERE id IS NOT NULL
GO
–=======================================
insert into [dbo].[tbl_test] values(1)–success
insert into [dbo].[tbl_test] values(2)–success
insert into [dbo].[tbl_test] values(NULL)–success
insert into [dbo].[tbl_test] values(NULL)–success
insert into [dbo].[tbl_test] values(2)–error [Cannot insert duplicate key row in object ‘dbo.tbl_test’ with unique index ‘NCI_test’.]
GO
select * from [dbo].[tbl_test]