SQL Server 2008 filtered indexes

indexing, sql, sql-server-2008

Solution

It's WHERE clause on your index.

My real life plans for a million row table:

I need an index on a column in a table, but 90% of the values are NULL. I need the index for the 10% non-null, but the index is bloated with the 90% NULL. So I add filter and save space.

I want to unique the same column (eq unique on NON-null value only). Pre SQL Server 2008, I had to use an indexed view or code. Now, the "WHERE" allows me to enforce unique via the index

Problem

I'd like to understand the SQL Server 2008 Filtered Index Feature. How does the Database Engine deal wih this concept ? And how is it going to help me get faster results than standard indexes while querying large tables ? Can anybody help me on this ? Thanks.

Original source