When you write a query, you (usually) don’t want to return all the rows from the table(s) involved – so you add a WHERE clause to the statement. This ensures that fewer rows are returned to the client – but doesn’t reduce the amount of I/O done to get the results.

When you create a nonclustered index, you can add a WHERE clause to reduce the number of rows that are stored at the leaf level – a filtered index. By having fewer rows in an index, less I/O is done when that index is used.

Filtered indexes are great performance boosts if you have a value that is used in a predicate very frequently, but that value is only a small amount of the total values for that table. (Say that ten times, fast.) For example: I have an orders table that contains a Status column. Valid statuses are Open, Processing, Packing, Shipping, Invoicing, Disputed, and Closed. When the business first starts using this table, there’s a good chance there is a fairly even distribution of orders across these statuses. However, over time, a majority of orders should be in Closed status – but the business wants to query for Open, or Disputed, which are only a small percentage.

This is where a filtered index can come in.

The coffee is your data; the filter is the WHERE clause

The coffee is your data; the filter is the WHERE clause

When you add a WHERE clause to the index creation statement, you’re limiting which rows are stored in the index. The index is smaller in size and more targeted. It can be trial and error to get the query optimizer to use the filtered index, but when you do, gains can be significant. In one case study I have, logical reads dropped from 88 to 4 – a 95% improvement! What are some of the things you can – and can’t – do with them?

You Can…

Use equality or inequality operators, such as =, >=, <, and more in the WHERE clause.

Use IN to create an index for a range of values.

Create multiple filtered indexes on one column. In my order status example, I could have an index WHERE Status = ‘Open’, and I could have another index WHERE Status = ‘Shipping’.

You can have multiple items in the same where clause. I could have an index WHERE Status = ‘Open’ OR Status = ‘Shipping’.

Create a filtered index for all NOT NULL values – or all NULL values.

You Can’t…

Create filtered indexes in SQL Server 2005.

Use certain expressions, such as BETWEEN, NOT IN, or a CASE statement.

Use date functions such as DATEADD for a rolling date range – the value in WHERE clause must be exact.

The query optimizer won’t consider filtered indexes if you’re using local variables or parameterized SQL. This is one of the hardest limitations to work around – so I usually reference Tim Chapman’s The Pains of Filtered Indexes for help with this!

Filter All The Things!

Using either a WHERE clause in a query to limit the rows returned or a WHERE clause in a filter to reduce the rows stored is beneficial to performance. You want to reduce the amount of data read and the amount of data returned to clients to improve performance. Filtered indexes can help!

A great read for more information is Tim Chapman’s The Joys of Filtered Indexes. You can also read Introduction to SQL Server Flitered Indexes.

↑ Back to top
  1. “The query optimizer won’t consider filtered indexes if you’re using local variables or parameterized SQL” That’s not true in all cases. You just have to make it statically known that the filtered index can apply. Like this:

    For a filtered index WHERE SomeCol > 1234 you can query it using:

    DECLARE @var INT = …;
    SELECT *
    FROM T
    WHERE SomeCol = @var AND SomeCol > 1234

  2. Ahhh filtered indexes, how I love thee. I’m constantly amazed that so few people (I come across) know about them. Great writeup Jes.

  3. Glad to see filtered indexes getting some much needed attention. One thing worth pointing out–and perhaps this is old hat to many readers–is that INSERTS and UPDATES to a table with a filtered index will *fail* (Msg 1934, Level 16, State 1, Line 1) unless they are made from a connection with the following options properly set:

    set quoted_identifier on
    set ansi_nulls on

    those should be the default for most connections, but a word to the wise if you’re considering adding a filtered index to an application (i.e. 3rd party) where you don’t control the entire codebase.

    i gotta think there’s some extended events magic one could setup to monitor one’s application for such bad behavior before deploying filtered indexes (i.e. throw an event when an inserts/updates occurs to a table of interest with either option OFF), but this has proved beyond my present knowledge of EE (nil), interest level, free time, and/or googling prowess.

    if anybody out there has such a thing (or would be willing to check it out), that’d be righteous.

    • Mike, just want to reemphasize your point…I ran into this issue yesterday. We had a…errr…problematic load query running that was running slower than we wanted, and in an effort to help it along, we tried a filtered index that -should- have made it much, much faster.

      But of course, we immediately see the progress count stall, and go and look and sure enough the query failed (it included an UPDATE)…as soon as we added the filtered index. Scanning up through the query, there it was… “SET QUOTED_IDENTIFIER OFF”.

      Luckily we were able to drop the index and restart without too much pain, but it was one of those somewhat obscure, irritating errors. I was at first wondering why SQL Server wouldn’t just ignore the index and not use it if incompatible with that setting, but I guess the update still had to hit that index anyway. So, Caveat Indexor, or however the expression goes.

      • I ran into the same issue: I created a simple filtered index “Status 2″. After that all inserts failed with “.INSERT failed because the following SET options have incorrect settings: ‘ANSI_NULLS, QUOTED_IDENTIFIER,CONCAT_NULL_YIELDS_NUL( ²Û”…..and all Updates failed with a similar error.

        Can someone explain why this happened?

  4. Pingback: (SFTW) SQL Server Links 15/11/13 • John Sansom

  5. Pingback: My links of the week – November 17, 2013 | R4

  6. Just wanted to point out that OR is not allowed in the grammar. So this clause:

    WHERE Status = ‘Open’ OR Status = ‘Shipping’

    could only be used if it were changed to:

    WHERE Status IN (‘Open’,‘Shipping’)

  7. Aaron, thanks for the clarification!

  8. Create index doesn’t accept like clause such as

    col like ‘ABC%’

    but this can easily be re-written as

    col >= ‘ABC’ and col < 'ABD'

Leave a Reply

