Is it possible to create an index against a subset of rows fulfilling a certain condition?

If it is possible how does one ensure the index is used?


Yes, what you're talking about is a Filtered Index. It works just like you think, by creating an index off of a subset of data. Below is an excerpt from the above reference:

A filtered index is an optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.

To your other question:

how does one ensure the index is used?

A few things will dictate this index being used:

  1. A well-designed filtered index correlated with
  2. A well-designed query

Obviously things like data distribution and many other factors play a role, but that goes with both filtered and non-filtered indexes.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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