Filters aren't just for instagram photos.

Filters aren’t just for instagram photos.

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.

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?

What You Can do in a Filtered Index…

  • Use equality or inequality operators, such as =, >=, <, and more in the WHERE clause.
  • Use IN to create an index for a range of values. (This can support a query that does an “OR” – read about “OR” and “IN” with filtered indexes here.)
  • 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’.
  • Create a filtered index for all NOT NULL values – or all NULL values.

What You Can’t do in a Filtered Index…

  • 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 for the predicate that matches the filter. Jeremiah explains how dynamic SQL can help.

Learn More About Indexes

Read all about it: We’ve got tons of free articles. Check out our top articles on indexes.

Free tools: Get our free tool to diagnose index insanity, sp_BlitzIndex®

Free online quiz: Take our quiz to test your index knowledge

Online video training: Check out our online course on How to Tune Indexes in SQL Server

In person advanced training: Learn from us in person in our Advanced Querying and Indexing course


Brent Ozar Unlimited Team
Brent Ozar Unlimited is a boutique consulting firm focused on understanding your environment and strategy. We partner with you to objectively identify pain points and develop solutions that align to your business goals. Your experience comes first; we share our knowledge and expertise to help you.
↑ 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?

      • One tricky case is if you create a Stored procedure with the wrong settings, then it doesn’t matter if you current connection has the correct settings the insert/update will be executed using the settings of “quoted_identifier and ansi_nulls” at the time the stored procedure was created.

  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'

  9. Looks like you can use local variables if you have Option(recompile) on the query. It will take advantage of the filtered index.

    SQL Server 2012

  10. Hi
    Iam trying to find a way to compare 2 columns i the same table in the filtered index, ie
    setting the Where ValidFrom < ValidTo. Is it only premitted to have hardcoded values in the comparision?
    Thanks for great site.

Leave a Reply

Your email address will not be published. Required fields are marked *