Filtered indexes are really interesting things. Just slap a where clause on your index definition, and you can do all sorts of helpful stuff:
– Isolate hot data
– Make soft delete queries faster
– Get a histogram specific to the span of data you care about
Among other things, of course. There are some annoying things about them though.
– They only work with specific ANSI options
– If you don’t include the filter definition columns in the index, it might not get used
– They only work when queries use literals, not parameters or variables
Click through for examples of them in action. I would definitely like to see improvements to filtered indexes along the lines that Erik mentions. They have so much potential, but are really held back by those limitations.