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.
