patternsqlMinor
Index against a subset of rows?
Viewed 0 times
againstindexsubsetrows
Problem
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?
If it is possible how does one ensure the index is used?
Solution
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.
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:
- A well-designed filtered index correlated with
- 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.
Context
StackExchange Database Administrators Q#62772, answer score: 3
Revisions (0)
No revisions yet.