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 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 one non clustered index including Gender column and I am selecting records bases on Gender, it scans full index pages to find a 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 actually improve 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 this 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 require to fetch record for process_id 0.
In this situation, we can create Filtered Index for process_id 0 instead of searching all the process_id.
Example to create Filtered Indexes:
Create a sample table:
CREATE TABLE dbo.tbl_Students
StudID INT IDENTITY(1,1)
,CONSTRAINT pk_tbl_Students_StudID PRIMARY KEY(StudID)
Create a Filtered Index for Male – Gender:
CREATE NONCLUSTERED INDEX idx_tbl_Students_Gender_Male
WHERE Gender = 'M'
Create a Filtered Index for Femail – Gender:
CREATE NONCLUSTERED INDEX idx_tbl_Students_Gender_Female
WHERE Gender = 'F'
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.