SQL Server - How Filter index works

Definition:

A filtered index is an optimized non-clustered index especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance as well as reduce index maintenance and storage costs compared with full-table indexes.

Filtered indexes can provide the following advantages over full-table indexes:

Improved query performance and plan quality

 Reduced index maintenance costs

 Reduced index storage costs

Let's take an example to understand the Filter Index.

We have a table called dbo. Customer and in the table total 38227 records exists. for better understanding I have added one dummy column called CountryCopy. Now we have two columns in this table Country and CountryCopy for this example.

Also added some dummy values in these columns INDIA and US. Below is the picture.



pic1

  

Initially there is no index created on the table.

pic2

Now I am going to create two NonClustered index on Country and CountryCopy columns.

-- Create a Non clustered index

CREATE NONCLUSTERED INDEX IDX_NonClusteredIndexCountry ON [dbo].[Customer] (Country);

GO

-- Create a Non clustered index with Filter condition (Filter Index)

CREATE NONCLUSTERED INDEX IDX_NonClusteredIndexCountryCopy ON [dbo].[Customer] (CountryCopy)

Where CountryCopy = 'INDIA';

GO

Now again check the pages distribution. See below the picture 3

pic3

In the above picture you can see the NC index on country column holding more no. of pages because that index have more records than NC index on CountryCopy column where this index holding very less no. of records and pages because of this filter condition.

So, from the above result it is clear that the number of records in ‘the filtered index is equal to the number records in the table which matches to the filter criteria, so the filtered indexes requires less storage space and they perform better from performance perspective.

Now lets check by running below two queries and see how filtered and non filter index perform from performance perspective:

--Regular Index

Select Count(1) from [dbo].[Customer] Where country = 'INDIA'

--Filtered Index

Select Count(1) from [dbo].[Customer] Where countrycopy = 'INDIA'

See below is the execution plan.

pic4

From the above result it is clear that the query which uses filtered index has a cost of 43% whereas the query which uses regular index has a cost of 57%. From this result it is clear that the query which uses filtered index performs better.

Drop all the index ..

DROP INDEX IDX_NonClusteredIndexCountry ON dbo.Customer

GO

DROP INDEX IDX_NonClusteredIndexCountryCopy ON dbo.Customer

GO

Now I am going to create a NonClustered index on different column using filter condition with CountryCopy = ‘INDIA’

-- Create a Non clustered index with Filter condition

CREATE NONCLUSTERED INDEX IDX_NonClusteredIndexName ON [dbo].[Customer] (FirstName,LastName)

Where CountryCopy = 'INDIA';

GO

pic5

Now lets execute the below query and see the execution plan.

Select * from [dbo].[Customer] Where FirstName = 'Eugene' and LastName = 'Huang'

And 

Select * from [dbo].[Customer] Where FirstName = 'Eugene' and LastName = 'Huang'

And CountryCopy = 'INDIA'

pic6

You can see the execution plan. From the above result it is clear that first query is not using the filtered index. And second query used the filter index because in the second query we have used filter condition in where clause same as while creating the filter index (Where CountryCopy = 'INDIA');

From the above examples it is clear that the filter index’s filter expression need to be part of the queries WHERE clause to force its usage.

That's all in this post. 

Thanks for reading.