I'm just learning about the new filtered indexes in MS SQL 2008 and I'm trying to understand where they would hurt and where they would help.

I can see that adding a filtered index to an employees table to index only the current employees would be a good idea.

But what about applying a couple filtered indexes to a large table based on time. For example, say I have a table that has an enteredOn date/time field and the table has many years worth of data. If queries are typically done based on date and some other field such as enteredby would it be good to have a few filtered indexes that have a where clause limiting the index to a timespan? In my example the query would be something like

select enteredOn, Description, ...
from myTable
where enteredOn > '2011/01/01' and enteredBy = "My, User"

Would having an filtered index for each year be reasonable or would that cause to much of a problem when the below was queried for instead?

select enteredOn, Description, ...
from myTable
where enteredOn > '2009/06/01' and eneteredOn < '2010/06/01' and enteredBy = "My, User"
  • OK. What is the answer to your question? Does the filtered index help or not? I think you better had not accepted an answer which main part is just a link to some Microsoft paper. Now we will have a hard time asking questions about filtered indexes without risking being closed as duplicate.
    – bernd_k
    Aug 2, 2011 at 11:49
  • It answered the question to the extent that I needed at that moment. I agree that it is not a full extended answer with examples to boot but It was all I needed. The wonderful thing is that, if you know the answer, you could add it and it would win with votes. My question, and title are pretty specific and you shouldn't have to worry about being closed if you are asking about filtered indexes in general or a different specific question about filtered indexes. If you are having trouble with that happening then this type of comment may be better as a question on meta.
    – Beth Lang
    Aug 2, 2011 at 19:01
  • 1
    I see your point, but I don't find that your problem is rather specific, it is the one first problem everyone encounters who has a grown database structure with data entered with relation to time, when thinking about filtered indexes. OK it is possible to ask in different ways.
    – bernd_k
    Aug 3, 2011 at 5:02

1 Answer 1



It depends on a few factors, there is no one answer for every scenario. For example, you may want to partition and archive some old data onto a dedicated set of disks becvause it is accessed infrequently. I doubt you would consider swapping such a partition for a filtered index. And I doubt you want to create a partition for all the values of NULL in a specific column as opposed to using a filtered index.

You should read the partitioning whitepaper by Kimberley Tripp at http://msdn.microsoft.com/en-us/library/ms345146%28v=sql.90%29.aspx, and the filtered index design guidelines found at http://technet.microsoft.com/en-us/library/cc280372.aspx.

Those two links should help point you in the right direction as far as all the design considerations you need to take into account.

  • The people who came here don't want to get only pointers to that papers. They and I want independent simple examples to get a feeling where this feature might help. They want to be able to deal with arguments like we do not like spending money in Enterprise Editions, perhaps filtered indexes helps us save the money.
    – bernd_k
    Aug 2, 2011 at 11:39
  • 1
    I'm sorry you don't feel that my contributions here are worthwhile. Feel free to downvote anytime. Aug 8, 2011 at 15:36
  • A good link is a good start. But I would like to get more information.
    – bernd_k
    Aug 8, 2011 at 16:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.