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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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:
Obviously things like data distribution and many other factors play a role, but that goes with both filtered and non-filtered indexes.