Home » Microsoft » MB6-886 » Which type of index should you use?
You have a table that contains 5 million rows. The table has a clustered index. You plan to add an additional index on a column that contains 80 percent null values.
The column is used extensively in WHERE clauses.
You need to implement an index strategy for the column. The solution must minimize the amount of storage space required for the index.
Which type of index should you use?
A. clustered
B. filtered
C. nonclustered
D. unique
Correct Answer: B
Explanation/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.
When a column only has a small number of relevant values for queries, you can create a filtered index on the subset of values. For example, when the values in a column are mostly NULL and the query selects only from the non-NULL values, you can create a filtered index for the non-NULL data rows. The resulting index will be smaller and cost less to maintain than a full-table nonclustered index defined on the same key columns.
