Filtered Indices in SQL Server 2008

I’ve got a business requirement that values must be unqiue, but are not required, like a drivers license number. I would like to enforce this rule in the database server. How do we solve this?

CREATE NONCLUSTERED UNIQUE INDEX IX_Unique_DriversLicenseNumber
ON [User] (DriversLicenseNumber)
WHERE [User].[DriversLicenseNumber] IS NOT NULL

Check out the filtered index design guidelines from Microsoft.

However, there is a problem with this solution. If you attempt to add a column to the table, you need to remove the index, add the column, and replace the index.

Prior to SQL Server 2008, the solution would have been to create a schemabound view, then put a unique index on the view. To create a schemabound view…

CREATE VIEW VW_DriversLicenseNumbers
WITH SCHEMABINDING
AS
SELECT DriversLicenseNumber
FROM [dbo].[User]
WHERE DriversLicenseNumber IS NOT NULL

This doesn’t have the same problems when adding a column, but it does run into various issues with replication environments. Microsoft recommends using a filtered index, as opposed to an indexed view, in almost all situations. Check out the design guidelines for more details.

Special thanks to Remus Rusanu at stackoverflow.com for this one.