Filtered Index

A Filtered Index, which is an optimized non-clustered index, allows us to define a filter predicate, a WHERE clause, while creating the index. The B-Tree containing rows from the filtered index will contain only those rows which satisfy the filter criteria used while creating the index. This optimized index offers several benefits over a full table non-clustered index as follows:

•  The filtered index contains only those rows which satisfy the defined filter criteria. As a result it reduces the storage space requirement for the index. In the example below I will explain it more.
• The filtered statistics or statistics for a filtered index are more compact and accurate, because they consider only the rows in the filtered index and the smaller size of the filtered index reduces the cost/overhead of updating the statistics.
• The impact of data modification is less with a filtered index as it is updated only when the data of the index is impacted or a new record is inserted matching the filter criteria.
• Maintenance costs will be reduced as well since only a subset of rows will be in consideration while re-organizing or rebuilding the index.
• And most important, as it is an optimized non-clustered index, the query performance will improve if only a subset of data, which is covered by the filtered index criteria, is required.

Filtered Index	Indexed Views
A Filtered Index is created on column(s) of a particular table.	Index Views can be created on column(s) from multiple base tables.
A Filtered Index cannot use complex logic in its WHERE clause, for example the LIKE clause is not allowed, only simple comparison operators are allowed.	This limitation does not apply to indexed views and you can design your criteria as complex as you want.
A Filtered Index can be rebuilt online.	Indexed views cannot be rebuilt online.
You can create your Filtered Index as a non-unique index.	Indexed views can only be created as unique index.

Usage Scenario

•  When you have mostly NULL values (this column can be defined as SPARSE column in SQL Server 2008 to save space required by NULL storage) and you normally pull rows with non-NULL values.
• When you have several categories of data (multiple domain range) in a single column and often select rows for one or few categories. For example, let's consider the State column in the CustomerAddress table and often you query customers addresses by state. So you can create a filtered index on the State column for StateA, StateB, StateC values. If you execute a query to pull data where State = StateA, the filtered index where State = StateA will be used and so on.

WHAT YOU CAN DO IN A FILTERED INDEX…

• Use equality or inequality operators, such as =, >=, <, and more in the WHERE clause.
• Use IN to create an index for a range of values. (This can support a query that does an “OR” –read about “OR” and “IN” with filtered indexes here.)
• Create multiple filtered indexes on one column. In my order status example, I could have an index WHERE Status = ‘Open’, and I could have another index WHERE Status = ‘Shipping’.
• Create a filtered index for all NOT NULL values – or all NULL values.

WHAT YOU CAN’T DO IN A FILTERED INDEX…

• Create filtered indexes in SQL Server 2005.
• Use certain expressions, such as BETWEEN, NOT IN, or a CASE statement.
• Use date functions such as DATEADD for a rolling date range – the value in WHERE clause must be exact.
• The query optimizer won’t consider filtered indexes if you’re using local variables or parameterized SQL for the predicate that matches the filter.

CREATE NONCLUSTERED INDEX FI_Employee_DOJ

ON Employee(DOJ)

WHERE DOJ IS NOT NULL