How to create a unique filtered index as a substitute for unique constraints in MSSQL

You can enforce unique constraints on a column in SQL using a basic ‘create constraint’ command:

…but doing so on a column that allows null values will yield an error like the one below:

In these instances, your best bet is a unique filtered index. There’s some pretty in-depth info from Redgate on the subject, but the syntax is pretty similar to creating a normal nonclustered index, just with the addition of a filter and unique clause:

And voila – success:

Share on Facebook