SQL Server: Filtered Indexes – Improved the query performance

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

SQL Server 2008 introduced the Filtered Indexes.
In my current organization, I am taking many interviews of Database Developers and Database Administrators.

Most of the people don’t know about the Filtered Indexes, so here, I would like to share a small note about Filtered Indexes and how it improves the query performance.

The Filtered Indexes are recommended to create on a well defined subset of data because it is highly optimized non-clustered index.

You can create a non-clustered with desired filters which improve the query and index performance by storing and manipulating indexes only for a subset of the data.

The Filtered Indexes can improve the execution plan quality, can reduce the index maintenance cost, can reduce the storage cost.

For example, I have one sample Students table which contains millions of data.

If I have only non-clustered index including Gender column and I am selecting records bases on Gender, it scans full index pages to find data for particular Gender.

If I create two separate Filtered Indexes for Male and Female, it scans and uses only one Gender index bases on a filter which improves the query performance.

Another example is, If your table column is allowing NULL and table has some NULL values, then you can create Filtered Indexes with NOT NULL on that table column which reduce the unnecessary cost to manage NULL values.

Another example is, There is one process table which contains 0 to 9 different process_id data, but a very less number of records with process_id 0 (10% only) and you need to fetch record for process_id 0.

In this situation, we can create Filtered Index for process_id 0 instead of search all the process_id.

Example to create Filtered Indexes:

Create a sample table:

Create a Filtered Index for Male – Gender:

Create a Filtered Index for Femail – Gender: