Chad Callihan takes us through filtered indexes:
We would all be happy if tables in our databases only contained the data we need. In reality, we tend to have data that needs kept for legal purposes or “just in case” something happens. Are you querying a table where queries only care about a fraction of the data?
When optimizing a query that is written to return a specific set of data, you may improve performance by creating a filtered index. Let’s look at an example of a filtered index and a warning that you may come across when using it.
I have a distinct love-hate relationship with filtered indexes. They’re so useful but so much of that utility comes with significant strings attached.