PrepAway - Latest Free Exam Questions & Answers

You need to create an index that: – improves the query …

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?

PrepAway - Latest Free Exam Questions & Answers

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.

14 Comments on “You need to create an index that: – improves the query …

      1. 113 says:

        there is already a non clustered index, so we can add a clustered index (row or colum store) or many non clustered index we want. in this case my opinion is “Create a nonclustered columnstore index on the table” on the TransactionDate column. (one billion of rows is a good candidate to a columnstore index)




        1



        4
  1. ERod says:

    The need to create an index that: improve the query performance and minimizes storage, are possibly the most important reasons to select, create a nonclustered filtered index on the table(B). Because, filtered indexes offer great benefits in terms of query execution performance and index storage savings. Because traditional nonclustered indexes are so necessary, but can rack up high maintenance and storage costs, the filtered index optimization should be considered wherever possible.




    0



    3
    1. 113 says:

      and what should the filter be ?
      each day the query would search a different date.

      i trust the answer is non cluster columnstore on the TransactionDate(that is the only column queried) : Columnstore indexes benefit of 10X (more or less) compress ratio




      1



      0
  2. Viktor says:

    I think correct answer is F. Detail information for bucket count supplied, Excellent when the predicate in the WHERE clause specifies an exact value for each column.




    0



    8
  3. bistabil says:

    Multiple columnstore indexes are not supported so D and E are out.
    It’s not memory optimised so F is out.
    Value in where clause is not exact so can’t create filtered index based on where clause; C is out.

    Between A and B, I’d go with A because of requirement “minimizes storage size of the table (inclusive of index pages)” since clustered index will reorganize existing data without increasing storage size.




    20



    0

Leave a Reply