Filtered Indexes

On several occasions, I have seen new SQL Server features implemented with little thought as to the impact of the feature. I will discus the use of Filtered indexes to illustrate this point.

Filtered indexes were introduced in SQL Server 2005. They allow a developer with a good understanding of the data and the application to tune data access. For example, if a table has a large number of null columns and a small number of non-nulls, then a filtered index may improve performance. In this example, the filtered index can be created  through SQL Server management Studio or via TSQL.

CREATE NONCLUSTERED INDEX [IX_Filter_JournalID_Nonnull]  ON [dbo].[tblJournals]
(
[JournalID] ASC
)
WHERE journalID is not null

In my experience, the main problem with these indexes is their indiscriminate use. For example, a table with only a small range of data items in an indexed column will not be appropriate to use filtered indexes.  In the following example, a table contains over  25,000 rows (journal postings). Each posting has a category (the post type). There are 100 types of journal posting. The developer created an index on the postID, and included a note column (to meet an application query).  The developer also created a filtered index for postIDs where postId=4. Partial SQL to create the table is shown below.

CREATE TABLE [dbo].[tblJournals](
[JournalID] [int] IDENTITY(1,1) NOT NULL,
[PostID] [int] NULL,
[Notes] [varchar](50) NULL,
CONSTRAINT [PK_tblJournals] PRIMARY KEY CLUSTERED
(
[JournalID] ASC
) ON [PRIMARY] ) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_Journal_Nonfiltered] ON [dbo].[tblJournals]
(
[PostID] ASC
)
INCLUDE ([Notes]) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_Journal_Filter4] ON [dbo].[tblJournals]
(
[PostID] ASC
)
INCLUDE ([Notes])
WHERE ([POSTID]=(4)) ON [PRIMARY]

The filter was intended to improve performance for queries in a given journal posting category. These queries were of the form

SELECT column1, column2

FROM tblJournals where POSTID = 34 and Notes=’J34′

or – the query anticipated by the developer for journal posting 4 –

SELECT column1, column2

FROM tblJournals where POSTID = 4 and Notes=’J34′

In both cases, the execution plan used the non-filtered index [IX_Journal_Nonfiltered]. The query execution plan is sown below:

In this example, the use (or rather unuse) of the filtered index was identified during tuning and it was removed. My own testing suggests that a filtered index will improve query performance, but only if the filter reflects a small number of rows on the table. Further, the index must be maintained, which may in turn add overhead.

I have some doubts as to the utility of filtered indexes. they can add some speed to retrievals in a small number of situations, at the cost of additional complexity. Some writers suggest using them to enforce constraints – this is valid, but other methods (e.g. triggers) can be used for this.