Filtered Index Basics

In this post, we continue with another beginner’s blog of database features that may be unknown to many. Let’s take a look at filtered indexes. Many database administrators are fully aware of the power of indexes and know how to create them. However, I find that some have yet to dive into fully optimizing their indexes and taking advantage of what filtered indexes has to offer.

What is a filtered index?

Simply it’s an index with a where clause. It is an optimized non clustered index that can be narrowed down in scope to better fit a subset of data. Example being date ranges, years, non NULLs or specific product types.

Benefits

Using filtered indexes can improve query performance and plan quality over full table indexes. Statistics are more accurate since they only cover the rows of the filtered index resulting in better execution plans. It can reduce your index maintenance due to the decreased index size and you only maintain the data in the index that is changed; not an entire table of data. Lastly, since it is smaller in size, it will consume less storage. Why have an index full of rows that are never accessed?

Let’s see it in action

First create one using the GUI.

In your database under tables, migrate to indexes. Right click on indexes and choose New Index then Non-Clustered Index.

Like a normal index choose your columns and included columns where needed.

Then choose FILTER and type in your expression. Here we are telling the index to filter out all NULL values. Note I did not include the word WHERE here, when you script it out the WHERE is already included.

USE [AdventureWorks2014]
GO
CREATE NONCLUSTERED INDEX [FIIDX_ComponentID_StartDate] 
ON [Production].[BillOfMaterials]
(       [ComponentID] ASC,       [StartDate] ASC)
WHERE EndDate IS NOT NULL
GO

Let’s look at a couple execution plans now to see the results. This is a common query in environments where you want to look for items that have a specific end date. Instead of reading all the rows including the NULL (active products, represented by the EndDate column) you index is already scoped down to non-active products. If we had an index just on End Date the NULL would also be included and depending on how many products exist in your table those rows could be significant.

USE AdventureWorks2014; 
GO 
SELECT ProductAssemblyID, ComponentID, StartDate  
FROM Production.BillOfMaterials 
WHERE EndDate IS NOT NULL      
AND ComponentID = 5      
AND StartDate > '01/01/2008' ; 
GO

If you find for some reason the optimizer is not choosing to use your filtered index you can also force its use with a query hint. Caution using query hints are not always the best course of actions, this is simply an example of what you can do.

USE AdventureWorks2014; 
GO 
SELECT ComponentID, StartDate 
FROM Production.BillOfMaterials    
 WITH ( INDEX ( FIIDX_ComponentID_StartDate ) )  
WHERE EndDate IN ('20000825', '20000908', '20000918');  
GO

 Summary

As a DBA it’s important to consider all options when creating indexes. We tend to think in terms of predicates and included column, but don’t dive deeper into actual uses of the indexes and how can we better eliminate unneeded results within our index rather than with a query where clause. If you find that your index is only needed for the current’s years data then filter it at the index level. Don’t read through or store  years of data when you don’t have to. Start taking advantage of these.