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.
A filter cannot be created on a rolling date range, so C is not correct. It could still be the desired answer for this test of course 😉
https://www.brentozar.com/archive/2013/11/what-you-can-and-cant-do-with-filtered-indexes/
Of course you could drop and (re)create a filtered index every day after the data load so users can query yesterday’s transactions… Depends on how many times per day your users want to run this query.. If it’s more than once, creating a little index like that is justified.