Using Filtered Indexes to Improve Performance

A Filtered Index is a new index type introduced in SQL Server 2008. In simple terms, rather than adding a non-clustered index to the entire table, you can add an index to a filtered data set.

The syntax for creating a filtered index is :

CREATE NONCLUSTERED INDEX <Index_Name>
ON <Table Name>(<Column(s)>)
WHERE <Filter Condition>

So, the only addition to the standard non-clustered index is the filter condition. 

Let us start with an example.

In the AdventureWorks2012 database, there is a table called Production.WorkOrder . If you closely examine the data, you will notice that there are 72,591 rows and only 729 of them have values.  There is an index on this column called IX_WorkOrder_ScrapReasonID .

Let add another column (newScrapReasonID) populate from the ScrapReasonID.

ALTER TABLE [Production].[WorkOrder]
ADD [newScrapReasonID] INT;
GO
UPDATE [Production].[WorkOrder]
SET [newScrapReasonID] = [ScrapReasonID]

Let us create an index for the newScrapReasonID column but this time we will create it as a filtered index.

CREATE NONCLUSTERED INDEX [NCI_WorkOrder_ScrapReasonID_Filtered] ON [Production].[WorkOrder]
(
       [newScrapReasonID] ASC
)
WHERE ([newScrapReasonID] IS NOT NULL)

In the above filtered index, the index will be created only for the columns where ScrapReasonID is not null.

Before analyzing any performance, let us verify index sizes by using following query :

SELECT
 i.name IndexName,
 i.index_id ,
 8 * SUM(a.used_pages) AS 'Index Size(KB)'
 FROM sys.indexes AS i
 INNER JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
 INNER JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
 WHERE  OBJECT_NAME(i.OBJECT_ID) = 'WorkOrder'
 GROUP BY i.index_id,i.name
 ORDER BY i.index_id

Output for the above query is :

Note that the index size has been dramatically reduced from 880 KB to 32 KB.

Now let’s run two queries :

SELECT [ScrapReasonID]
FROM [Production].[WorkOrder]
WHERE [ScrapReasonID] IS NOT NULL
SELECT [newScrapReasonID]
FROM [Production].[WorkOrder]
WHERE [newScrapReasonID] IS NOT NULL

Here is the execution plan for both queries.  So, query which uses filtered index has less cost.

Using Filtered Index

Having seen the above behavior, you might be tempted to think that a filtered index can be used in most cases. Let us see the following example.

First create a fresh filtered index.

CREATE INDEX NCI_SalesOrderDetail_ProductID_FLT
ON Sales.SalesOrderDetail (ProductID)
WHERE ProductID = 900

Now, run following two queries.

SELECT ProductID
FROM Sales.SalesOrderDetail
WHERE ProductID = 900
--------------
DECLARE @ProductID INT = 900
SELECT ProductID
FROM Sales.SalesOrderDetail
WHERE ProductID = @ProductID
 

The two queries are very similar, the only minor different is the second query uses a parameter.  Though the result is same, let us examine the query plan :

Note the first query uses the filtered index whereas second query uses standard index :

Thus it is crucial to note that if your query has parameters it will not use filtered indexes. Therefore, when selecting a filtered index, it should be a column where it is not used for parameter.  Most appropriate columns are, status columns, gender columns etc.

Creating Filtered Unique keys

Let us say we have a table like following.

CREATE TABLE Data
  (ID INT IDENTITY,
  PersonID INT,
  NAME VARCHAR(50) )

In this table, PearsonID is nullable but if you have a value it should be unique.  So, let us create a unique index.

CREATE UNIQUE INDEX IDX_Data_PersonID
ON Data(PersonID)

This statement will fail since there are multiple null values for PearsonID. :

Msg 1505, Level 16, State 1, Line 2

The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name ‘dbo.Data’ and the index name ‘IDX_Data_PersonID’. The duplicate key value is (<NULL>).

The statement has been terminated.

So the option is creating a filtered index :

CREATE UNIQUE INDEX IDX_Data_PersonID
ON Data(PersonID)
WHERE PersonID IS NOT NULL

Filtered Index Compared with Index View

You might think that index view also can be used for the same purpose. There are pros and cons to using both options. A major advantage in using an index view is that you have the option of creating the index for a view where you have multiple tables, whereas a filtered index can be created only on one table.

The filtered index does have a few definite advantages over index views.

• Index view has to be a unique clustered key whereas a filtered index need not to be unique
• You can build a filtered index online which cannot be done for an indexed view.

New Sp_helpindex

For retrieving index information, we tend to use sp_helpindex. However, sp_helpindex does not provide other index attributes such as include columns and filtered conditions.  There are many scripts available if you google. Out of all, this script suited me most.  This script will create procedure called sp_helpindexall.

SELECT ScrapReasonID
FROM WorkOrder WITH (INDEX (NCI_WorkrOrder_ScrapReasonID_Filtered))
WHERE ScrapReasonID = 3

So this will provide you all the indexes with included columns and filter definitions.

Using FilteredIndex with Index Hint

What if you are using a filtered index and your filter condition (or Where clause) contradicts the filter index like following query.

SELECT ScrapReasonID
FROM WorkOrder WITH (INDEX (NCI_WorkrOrder_ScrapReasonID_Filtered))
WHERE ScrapReasonID = 3

You might tend to believe that query will ignore the index clause and will continue to execute, however this is not the case and the output will be :

Msg 8622, Level 16, State 1, Line 2
Query processor could not produce a query plan because of the hints
defined in this query.
Resubmit the query without specifying any hints and
without using SET FORCEPLAN

Finally note that, NOT IN , LIKE, SUBSTRING, LEFT are will not work work for the filter condition in Filtered index.

]]>