SQL Server Filtered Index Basics

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, 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 of 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) your 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

Filtered Index Basics

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.