Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

SQL Server 2008 Filtered Indexes vs Index Views

SQL Server 2008 Filtered Indexes vs Index Views

  • Comments 1
  • Likes

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: ,
Comments
  • I have had to amend this post as the orignal version had a link to an internal Microsoft site. Thanks to Bob Beachemin (http://sqlskills.com/blogs/bobb/) for pointing this out and Srini Acharya on the relational product team for pointing me in the right direction.

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment