SQL Server 2008 Filtered Indexes vs Index Views

  • Article

SQL Server 2008 has a new feature called filtered indexes which are like normal table indexes only they have a simple where clause,  meaning that the index will only cover rows specified in the where clause. As I have mentioned before an obvious example is where you can filter out nulls and still create a unique index.

So how do filter indexes compare with index views, well index views are more complex , you can index across a view containing multiple tables for a start and the where and case clauses can be very sophisticated.  This sophistication comes at a price as the index can be larger (and the B+ Trees will be deeper)  and is will be updated during an insert whereas a filtered index will only be updated during insert if the new row meets the where clause in the filter.

So when to use what?

  • The where clause in a filtered indexes has be pretty simple – i.e. it has to conform to these this format:

<filter_predicate> ::=

    <conjunct> [ AND <conjunct> ]

<conjunct> ::=

    <disjunct> | <comparison>

<disjunct> ::=

        column_name IN (constant ,…)

<comparison> ::=

        column_name <comparison_op> constant

<comparison_op> ::=

    { IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !< }

For more information on the rules check SQL Server 2008 books online here 

  • The rule eliminates a good portion of the table from being covered by the index, I would caveat this by reiterating the benefits of a unique index on a column with nulls in.
  • The  rest of the time use a regular index or in indexed view.

Technorati Tags: SQL Server 2008,Indexes