Introducton to SQL Server Filter Indexes

 
Filter Indexes are non-clustered indexes with WHERE clause. Filter Indexes can help you If you have a large table and your queries always looks for a specific part of this large table. For example one of the columns in this large table might have a lot of NULL values, you can create filter index for non-null values in that situation.

   Filter Indexes can improve the query performance and the execution plan quality. Since Filter Indexes contains a well defined subset of data by using WHERE clause, their size should be smaller than full-table indexes. That means execution plan which uses the smaller size can find the data faster than full-table indexes.

Limitations of Filter Indexes

• You can not create a filter Index for a View.
• All Filter Indexes are Non-Clustered, you can not create a Clustered Filter Index. 
• You can still use INCLUDE option with Filter Indexes

   To Demo Filter Indexes, Let's say we have Products table which contains all products we sell. Our web application queries the products table only for active products. We want to create a Filter Index for available products, Here is the table schema

Here is my filter Index for active products.

You can force your queries to use this filter Index by using WITH statement in your query.