What is a SQL Server Filtered Index
A SQL Server Filtered index is a nonclustered indexes that can be used to increase query performance when selecting records from a table. This type of index can be especially useful when there are a small count of distinct values in a column such as gender that can contain null values. By created a filtered indexed on Male or Female the index will ignore null rows. This has three benefits. First, the performance of the index is faster because it has less rows to use for retrieval. Second, it reduces the cost to maintain the index on the table. Finally there is less storage required to hold the index because it is only storing the values defined for the index on the table.
Design Considerations
When selecting the columns to include in the filtered index you should be aware of these design points.
- Filtered indexes are defined on one table and support simple comparison operations in queries.
- A column in the filtered should be a key or included column if the column is in the query result set
As with anything in SQL Server your mileage may vary. If you have not tried using Filtered indexes then you should find an appropriate table and query that could benefit from their use. Execute the code prior to the changes and review the execution plan. Then make the changes, clear the buffer cache and try your code and execution plan again.
For more information on SQL Server Filtered Indexes see Technet
For an example on how to use SQL Server Filtered Index Performance see my post
