Imagine we have the following table

Users

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)