Shane O’Neill answers one of my favorite interview questions:
I used to think that this would be a complex requirement, possibly requiring a
TRIGGERor two to check the inserted value against whatever is already there; but there is a way to have this functionality and have it the way that SQL Server normally would enforce a uniqueness on a column; by using aUNIQUE INDEX.In case you’re thinking…
“Oh, a unique index doesn’t check what’s already there, is that it?”
I’m afraid that’s not the case.
This is one of my favorite uses of filtered indexes: “limited” uniqueness. In other words, I’m okay with an unlimited number of NULL values but all non-NULL values need to be unique.