SQL Server 2008 – Unique Constraint that allows NULLs


Hi Everyone, since I’m off to Sydney from Monday until Wednesday next week (and unlikely to be publishing anything new during that time) here is a bonus post.  See you after I return.. /R


One of the like-to-haves with SQL Server has been the enforcement of column uniqueness but allowing multiple null values (should the column support nulls).  This had been logged on Microsoft Connect and it appears you can achieve this kind of functionality (although not strictly standards compliant).

If you are running SQL Server 2008 (unsure but I don’t believe this will work on SQL Server 2005 or previous), you can create a filtered index by following the following syntax:

CREATE UNIQUE [NON]CLUSTERED INDEX [(index name)]
ON [Schema].[Table]
(
   [Column(s)]
)
WHERE [Column] IS NOT NULL;

For example, give a table called Customer with a column called EmailAddress, to enforce only unique values or NULL, you would create an index like so:

CREATE UNIQUE NONCLUSTERED INDEX IX_Customer_EmailAddress
ON dbo.Customer
(
   EmailAddress
)
WHERE EmailAddress IS NOT NULL;

If you are looking for a workaround for SQL Server 2005 you can try out the one offered on the Connect post which is located here.


About Rob Sanders

IT Professional and TOGAF 9 certified architect with nearly two decades of industry experience, 18 years in commercial software development and 11 years in IT consulting. Check out the "About Rob" page for more information.

Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>