SQL Server's Filtered Indexes: The "Third" Index Dimension

One perk to filtered indexes that may not be obvious at first is that you get an extra “dimension”– almost an extra kind of key column. This can be useful for some queries that can be tricky to index.

Let’s say we have a query with two inequalities in the predicate, like this:

SELECT COUNT(*) FROM dbo.Table WHERE ProductStatus > 5 and ProductsInStock > 1; 
GO 

We can create the following index, but it isn’t perfect:

CREATE INDEX ix_nofilterhere on dbo.Table (ProductStatus, ProductsInStock); 
GO

Our data is first sorted by ProductStatus, so we can seek to the 6 and read the data thereafter, but we have a problem: the data at that point looks like this:

We have to check every single row and see if the ProductsInStock is greater than 1. If we have a lot of products with nothing in stock, we might be reading a lot of rows uselessly.

If the index was filtered, like this, it would have a secret superpower:

CREATE INDEX ix_filtered on dbo.Table (ProductStatus) WHERE (ProductsInStock > 1); 
GO

In this case, SQL Server can match the filter to the query with an exact match, so it probably doesn’t even have to think about the ProductsInStock predicate anymore: it knows everything in the filtered nonclustered index meets that criterion.

Now all that’s left is for it to solve for the predicate ProductStatus > 5, and that’s the leading key column in our filtered index! We can go straight to the right starting point and we don’t have to read any “wasted” rows.

Essentially, our filtered index predicate offloaded one of the predicates in our query and made solving the rest simpler.

Most of the time, non-filtered indexes work well enough. But if there are important queries with multiple inequalities, or important queries with top/order by and predicates on different columns, sometimes a filtered index can get pretty spectacular results. (Assuming you can get SQL Server to use it. It’s complicated.)

Remember: always test filtered indexes carefully before using them. (See “required set options for filtered indexes” here.)

Disclaimer: this post is full of pseudo-code which may not be syntactically perfect– just using snippets as an example to make notes on a point, etc.