Imagine we have the following table

Users

Field Type
Id INT
Username NVARCHAR
Deleted BIT

Then imagine we want Username to be unique for non deleted users. Normally we would make a field unique by doing something like this…

ALTER TABLE dbo.Users ADD CONSTRAINT uq_username UNIQUE(Username)

This will fail as multiple deleted users can have the same username in our system. To make a constraint that only constrains a subset of data we need to use a filtered index and make that unique, in the example above that looks like this

CREATE UNIQUE INDEX ndx_non_deleted_username ON dbo.Users(username) WHERE Deleted = 0

We can then do this without any errors

INSERT INTO dbo.users(username,deleted)
VALUES('gavin',1),
        ('gavin',1),
        ('gavin',0)

It will also successfully error if we try to create a second non deleted user with the same name

INSERT INTO dbo.users(username,deleted)
VALUES('gavin',0)