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"