Filtered indexes in SQL Server 2008

Ok, a great feature as it is... a great performance booster when applied correctly. I'm not gonna repeat what others have written, google it!

But what I really like about it is that is solves the age old problem of applying a uniqueness requirement to all but (and usually that is all but null values or something similar).

The classic solution to this problem is of course to use a trigger to verify uniqueness, to make that trigger run fast it requires an index. Instead of the mess of a complete index (larger than we need) and extra code in the form of a trigger that adds complexity and steals more performance we simply apply a filtered index.

Example, we want to make sure that socialid is unique if it is not null:
create table Person (id int identity(1,1) primary key, name nvarchar(100), socialid nvarchar(20))

insert into Person (name, socialid) values ('Albert', '1234'), ('Bengt', null), ('Christian', '2341'), ('Dan', null)

create unique index idx_socialid on Person (socialid) where socialid is not null

select * from Person

insert into Person (name, socialid) values ('Eric', '1234')
Inserting the person named Eric will fail since the socialid is not unique.