SQL Server 2008 Filtered Indexes vs Index Views
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.
Subscribe in a reader" />
