UNIQUE constraint és több NULL érték

Na ez egy igazán egy érdekes probléma: képzeljük el azt az esetet, amikor egy tábla oszlopa engedi a NULL értékeket, de azt is akarjuk, hogy minden egyéb érték egyedi legyen, pl.:

1USE tempdb;
2GO
3 
4CREATE TABLE [dbo].[CheckConstraintTest]
5(
6    [id] int NULL,
7    [name] char(1) NULL
8);

Első körben a UNIQUE constraint jutott eszembe, de ez nem lesz jó. Miért is? Persze, a UNIQUE constraint nem enged egynél több NULL értéket sem, ahogy ez az alábbi példán is látszik:

1ALTER TABLE [dbo].[CheckConstraintTest]
2ADD CONSTRAINT [UQ_ID] UNIQUE ([id])

Majd próbáljunk meg adatokat beletenni a táblába:

1INSERT INTO [dbo].[CheckConstraintTest] VALUES (NULL, 'a');
2INSERT INTO [dbo].[CheckConstraintTest] VALUES (NULL, 'a');

Persze a második insert nem fog megtörténni, hanem kapunk egy szép hibaüzenetet:

1(1 row(s) affected)
2Msg 2627, Level 14, State 1, Line 2
3Violation of UNIQUE KEY constraint 'UQ_ID'. Cannot insert duplicate key in object 'dbo.CheckConstraintTest'. The duplicate key value is (<NULL>).
4The statement has been terminated.

Na jó, de akkor most mit lehet tenni? A megoldás igen egyszerű (és ez nem az egyetlen megoldás):

A táblára nem kell UNIQUE constraint, hanem egy filtered index-el oldom meg. Az alábbi kóddal módosítom a táblát, majd ismét megpróbálok adatokat beletenni a táblába:

1ALTER TABLE [dbo].[CheckConstraintTest]
2DROP CONSTRAINT [UQ_ID];
3 
4CREATE UNIQUE NONCLUSTERED INDEX [NCU_ID] ON [dbo].[CheckConstraintTest] ([id])
5WHERE [id] IS NOT NULL;
6 
7INSERT INTO [dbo].[CheckConstraintTest] VALUES (NULL, 'a');
8INSERT INTO [dbo].[CheckConstraintTest] VALUES (NULL, 'a');

Így már sikerült több NULL értéket bevinni, és az ID oszlopomban is garantált, hogy nem ismétlődik ugyan az az érték.