A unique constraint that allows for nulls is exactly as it sounds. Essentially we allow any number of rows to have a value of null for a column unless a column has a value, then we want to enforce uniqueness. This is different than a regular constraint in that normally you would only be allow one row with a null value in that column
Say you are tracking VIS (Vaccine Information Statement) in your database. For the last couple of years or so VIS have had an associated bar code. In your database you likely want to ensure that a unique value is stored in the database for a bar code, but only for more recent VIS records where a bar code is available.
Starting in SQL Server 2008 you can apply a filter to a constraint where it only applies in certain cases. In the case of a VIS example described above we would only want to make sure the bar code is unique if it is a non-null value.
--Create a unique constraint on a column that allows null
CREATE UNIQUE NONCLUSTERED INDEX idx_vis_bar_code
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Ensure that if a bar code is entered that it is unique.',
For more information check this stackoverflow post.