Exam 70-762 All Questions

View all questions & answers for the 70-762 exam

Exam 70-762 topic 1 question 30 discussion

Actual exam question from Microsoft's 70-762

Question #: 30
Topic #: 1

Note: The question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other question in the series. Information and details provided in a question apply only to that question.
You have a database named DB1. The database does not use a memory-optimized filegroup. The database contains a table named Table1. The table must support the following workloads:

You need to add the most efficient index to support the new OLTP workload, while not deteriorating the existing Reporting query performance.
What should you do?

A. Create a clustered 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 hash index on the table.

Show Suggested Answer

Suggested Answer: C 🗳️
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.
References:
https://technet.microsoft.com/en-us/library/cc280372(v=sql.105).aspx

by gtc108 at April 17, 2020, 1:01 a.m.

Comments

Submit Cancel

gmu

Highly Voted 5 years, 1 month ago

C is the correct option, because one of the columns in the filter is constant.

upvoted 24 times

...

Oooo

Highly Voted 4 years, 9 months ago

The given answer is correct. C. because the question clearly says it needs to support new OLTP workload and columnstore index is not a healthy option in OLTP (moreover we already have a clustered columnsore index). So the answer is a nonclustered index with filter as one of the filters is constant.

upvoted 5 times

...

caeesz

Most Recent 4 years, 5 months ago

there is no time .so correct is B

upvoted 1 times

...

TheDUdeu

4 years, 5 months ago

You can't have two columnstore indexes on a table

upvoted 1 times

...

lh2607

4 years, 7 months ago

Correct answer is C due to one of the filters being constant.

upvoted 1 times

...

Angelcr

4 years, 8 months ago

The answare is correct, read the next url: The nonclustered columnstore index definition supports using a filtered condition. To minimize the performance impact of adding a columnstore index on an OLTP table, use a filtered condition to create a nonclustered columnstore index on only the cold data of your operational workload. https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-index-design-guide?view=sql-server-2017#Filtered

upvoted 3 times

...

delgadillo

4 years, 12 months ago

i guest i B, because the filter is variant from one of the two columns,so i go for B.

upvoted 2 times

Chocho

4 years, 11 months ago

no it's C because one of the columns in the filter is constant

upvoted 6 times

...

gtc108

5 years, 2 months ago

E. Create a nonclustered columnstore index on the table. Nonclustered Columnstore Index is good for OLTP and for data that is refreshed on a frequent basis.

upvoted 2 times

Vijayglobal

5 years, 1 month ago

Only one columnstore index per table. As the table already has clustered column stored index nonclustered columnstore index is not the right answer.

upvoted 11 times

...

stm22

4 years, 11 months ago

Not E, unless you filter it to cold data: To minimize the performance impact of adding a columnstore index on an OLTP table, use a filtered condition to create a nonclustered columnstore index on only the cold data of your operational workload. from: https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-index-design-guide?view=sql-server-ver15

upvoted 1 times

...