Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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"
share|improve this question
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 '11 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 Whitezel Aug 2 '11 at 19:01
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 '11 at 5:02

1 Answer 1

up vote 3 down vote accepted


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.

share|improve this answer
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 '11 at 11:39
I'm sorry you don't feel that my contributions here are worthwhile. Feel free to downvote anytime. –  SQLRockstar Aug 8 '11 at 15:36
A good link is a good start. But I would like to get more information. –  bernd_k Aug 8 '11 at 16:09

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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