SQL Server 2008 Filtered Indexes

I have seen the filtered indexing in SQL Server 2008 mentioned a couple of times over the last couple of weeks but I only got it properly when I got to try it myself in ctp6.

I have often needed to constrain a column to a only allow unique values but also allow the column to allow nulls and with filtered indexes you can put in a where clause like this ...

create unique index production.nullidx

on production.product(code) 

where production.product(code) is not null 

So the filter takes out the nulls.  Apparently this is also really useful if you are migrating your database from another provider to SQL Server.

Technorati Tags: indexing,SQL Server 2008