Creating an index always has trade-offs. Whenever you insert, update or delete, it has to perform those operations on the index as well. This is all pretty commonly known.
But with the advent of filtered indexes, I'm wondering if there is a minimum (or maximum) row count that you should consider too much of a performance hit.
For example, if you have 100,000 rows in a table that gets, say, 75% read operations, would it be wise to add a filtered index that only covers 500 rows? Or 100 rows? What if the filter covers 85,000 of those rows?
I think the performance implications of adding a filtered index to a read-heavy table are pretty clear. I'm just wondering if there's a natural limit to this performance.
Another way of looking at this question: If 90% of your updates aren't fitting within the filter, is the performance degraded less than it would be for a non-filtered index?