What is a Filtered Index?

Filtered Index

Filtered Indexes are one of the best performance boosters of SQL Server. This was introduced in SQL Server for reducing the maintenance and storage cost. Filtered Indexes are the Non Clustered Indexes which are created on a column(s) to filter and store the data in Index.

              There will be few situations where we need to filter the same data every time through the query. In this kind of scenarios we need a filtered index in which we keep the same where clause as in the original query. So that when we are executing the query, the index which we created need not process all the records of table. The index processes only few records based on the where clause.

We can simply say that Index with a where condition is called as a ‘Filtered Index’.

————————————————————————-
USE [AdventureWorks2012]
GO
————————————————————————-
SELECT * FROM [Person].[Person]
WHERE [Suffix] IS NOT NULL
————————————————————————-
CREATE NONCLUSTERED INDEX NCI_Person_Suffix
ON [Person].[Person](lastname)
WHERE [Suffix] IS NOT NULL
————————————————————————-
SELECT * FROM [Person].[Person]
WHERE [Suffix] IS NOT NULL
————————————————————————-

Execution Plan:

Filtered-Index

Leave a Reply

Your email address will not be published. Required fields are marked *