How about Filtered Indexes instead of Partitioning?

Question: I've been reading your Q&As on partitioned tables and views and I'm wondering how filtered indexes fit in. Couldn't you get some of the same benefits of partitioning by using filtered indexes?

Answer:  This is a really interesting question, even a bit challenging to answer. Filtered indexes are an incredibly powerful feature (one of my favorites) so I don't want to dissuade you from using them. However, just like many things in SQL Server - it depends.

A table can be partitioned using the partitioned tables feature. A table can be broken into multiple tables and then unioned (UNION ALL) in a view using the "partitioned views" feature. And, any of these base tables - either a partitioned table or a table that makes up a partitioned view - can have filtered indexes. For me, filtered indexes supplement your partitioning strategy they don't replace it.

What could/should you use filtered indexes for?

Smaller sets of data that you query and/or manipulate differently are great to filter. For example, imagine you have active/inactive data within a set. If you were to create a filtered index where active = 1 then only those rows would be present in the index. By having a smaller set in the index you have more accurate statistics (remember, you'll need to make sure that [filtered stats get updated regularly] ) and you'll take up less storage and have less maintenance. Having said all of this, if the inactive data is really going to become obsolete then partitioning might be a better option as you might be able to complete eliminate maintenance for the obsolete data.

Imagine salespeople working with their sales data. The salesperson value of 1 is actually a special value and it represents over-the-counter (OTC) sales. For the values 2 through 27, these salespeople are specific folks in the company. There might be queries for OTC sales that are different than what the salespeople query. You could create a filtered index WHERE Salesperson = 1. For a query that salespeople run you could create a filtered index WHERE Salesperson > 1. And, for any specific queries that an individual salesperson wants (and ONLY they want), you can create a filtered index WHERE Salesperson = 6. But, for things like sales, I also (generally) recommend this in addition to partitioning. So, you might end up with one table per year where each might be partitioned by month. Then, each of these partitioned tables will have filtered indexes. You can create on filtered index over the entire set (the entire year) and fast-switching is still allowed.

Some folks have mentioned that filtered indexes can replace partitioning by allowing you to create an index WHERE SalesDate >= ‘20120101’ AND SalesDate < ‘20120201’ to represent January. You could then do the same for February, March, April, etc. However, they are not exactly the same.

With filtered indexes each index only applies to a subset of the base table. As a result, fast switching is not allowed.

More specifically, this filtered index will not impact fast switching:

CREATE INDEX ActiveItemsON Table (col1, col2)WHERE Status = 1ON PartitionScheme(PartitionColumn)

However, this filtered index will not allow fast switching:

CREATE INDEX JanuaryItemsON Table (col1, col2)WHERE SalesDate >= '20110101'AND SalesDate < '20110201'

You could create this second index on the PartitionScheme but then you’d have to create this same index for every new partition. Yes, it’s possible. However, there’s a bigger problem. The problem is tied to a concept known as interval subsumption. Interval subsumption is not a concern with partitioned views; however, it is a problem with filtered indexes. To explain this I’ll use two scenarios.

Scenario 1 – Single large table with filtered indexes

Scenario 2 – Partitioned view (individual base tables unioned together in a view: SalesView)

Now, what happens with queries?

Both of the following queries benefit from these indexes:

SELECT *FROM SalesWHERE SalesDate BETWEEN ‘20120110’ AND ‘20120120’ 

SELECT *FROM SalesViewWHERE SalesDate BETWEEN ‘20120110’ AND ‘20120120’

However, the following WHERE clause presents a problem for the filtered case:

SELECT *FROM SalesWHERE SalesDate BETWEEN ‘20120125’ AND ‘20120205’ 

SELECT *FROM SalesViewWHERE SalesDate BETWEEN ‘20120125’ AND ‘20120205

So, by truly breaking your data sets down into separate tables, you are more likely to get the indexes to be used!

Keep those questions coming! I’m having great fun with this Q&A series!!

Thanks!
kt