Note: This question is part of a series of questions that use the same answer choices. An answer choice may
be correct for more than one question on the series. Each question is independent of the other questions in this
series. Information and details provided in a question apply only to that question.
You work on an OLTP database that has no memory-optimized file group defined.
You have a table names tblTransaction that is persisted on disk and contains the information described in the
following table:
Users report that the following query takes a long time to complete.
You need to create an index that:
– improves the query performance
– does not impact the existing index
– minimizes storage size of the table (inclusive of index pages).
What should you do?
A.
Create aclustered index on the table.
B.
Create a nonclustered index on the table.
C.
Create a nonclustered filtered index on the table.
D.
Create a clustered columnstore index on the table.
E.
Create a nonclustered columnstore index on the table.
F.
Create a hashindex on the table.
Explanation:
A filtered index is an optimized nonclustered index, especially suited to cover queries that select from a welldefined 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.
I think the answer should be “B, nonclustered index on the table”, there is no need to filter.
Never mind, we need to use filtered non clustered index because of the number of record in the table is one billion.
I think nonclustered columnstore index because we have a group by so we need to scan all table
for aggregation, ideal index is columnstore index