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.