Category: Development

In earlier versions of SQL Server when you created a unique constraint on a nullable column, NULL would count as a value and you would only be able to use NULL once.  The workaround for this was to create a trigger that enforced uniqueness for only the non-null values, but that is not necessarily an ideal solution.  Luckily there is a cleaner solution offered in SQL Server 2008 that is also very flexible.   The following is referred to as “filtered indexes” by Microsoft:

CREATE UNIQUE NONCLUSTERED INDEX User_PinNr_IUC
ON [User] (pinNr)
WHERE pinNr IS NOT NULL

That will create a unique index on the User table’s pinNr column but will allow any number of NULL values.  We can extend this further by enforcing uniqueness only for active users:

CREATE UNIQUE NONCLUSTERED INDEX User_PinNr_IUC
ON [User] (pinNr)
WHERE pinNr IS NOT NULL AND isActive = 1

More information on filtered indexes can be found @ Microsoft.