Exam 70-762 All Questions

View all questions & answers for the 70-762 exam

Exam 70-762 topic 1 question 81 discussion

Actual exam question from Microsoft's 70-762

Question #: 81
Topic #: 1

Note: This 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 questions in this series. Information and details provided in a question apply only to that question.
You have a database named DB1. There is no memory-optimized filegroup in the database.
You have a table and a stored procedure that were created by running the following Transact-SQL statements:

The Employee table is persisted on disk. You add 2,000 records to the Employee table.
You need to create an index that meets the following requirements:
✑ Optimizes the performance of the stored procedure.
✑ Covers all the columns required from the Employee table.
✑ Uses FirstName and LastName as included columns.
✑ Minimizes index storage size and index key size.
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: B 🗳️
References:
https://technet.microsoft.com/en-us/library/jj835095(v=sql.110).aspx

by eric0117 at Jan. 7, 2020, 7:12 p.m.

Comments

Submit Cancel

eric0117

Highly Voted 5 years, 5 months ago

what wrong with filtered nonclustered index? Isn't it a better option?

upvoted 34 times

...

Cageman

Highly Voted 5 years, 5 months ago

I would indeed go with Eric

upvoted 9 times

JohnFan

5 years, 3 months ago

This column could benefit from a filtered index, which is an index that has a WHERE clause. So if almost all rows were NULL, it could be that searching for a row with the value of NULL in the index would be useless.

upvoted 3 times

...

HA2020

Most Recent 4 years, 5 months ago

sorry..I was wrong "How do I choose between a rowstore index and a columnstore index? Rowstore indexes perform best on queries that seek into the data, when searching for a particular value, or for queries on a small range of values. Use rowstore indexes with transactional workloads because they tend to require mostly table seeks instead of table scans. Columnstore indexes give high performance gains for analytic queries that scan large amounts of data, especially on large tables. Use columnstore indexes on data warehousing and analytics workloads, especially on fact tables, because they tend to require full table scans rather than table seeks." https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview?view=sql-server-ver15

upvoted 1 times

...

HA2020

4 years, 5 months ago

..filtered nonclustered columnstore index

upvoted 1 times

...

HA2020

4 years, 5 months ago

I think it is nonclustered columnstore because the requirements state: minimize idex size AND KEY size. Nonclustered indexes do not have key, and the requirements listed all the needed key as 'included'.

upvoted 1 times

...

TheDUdeu

4 years, 5 months ago

How do you use a filter index on a variable in last name? You can't filter index needs a set value the answer is correct.

upvoted 1 times

...

Alex5x

4 years, 7 months ago

The answer is C - Create a nonclustered filtered index on the table. CREATE NONCLUSTERED INDEX IX1 on Employee (LastName) INCLUDE (FirstName, DepartmentId) WHERE Status='T';

upvoted 2 times

azskierphoto

4 years, 6 months ago

It is wrong, we have requirements "Uses FirstName and LastName as included columns"

upvoted 1 times

...

TheDUdeu

4 years, 5 months ago

And I would agree with you if it wasn't for azskierphoto and the requirement that he posted

upvoted 1 times

...

Luzix

4 years, 7 months ago

Consider using filtered indexes on columns that have well-defined subsets, for example sparse columns, columns with mostly NULL values, columns with categories of values, and columns with distinct ranges of values. A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce storage costs. https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-guides/jj835095(v=sql.110)?redirectedfrom=MSDN

upvoted 1 times

...

Cococo

4 years, 10 months ago

https://www.brentozar.com/archive/2013/11/what-you-can-and-cant-do-with-filtered-indexes/ scroll down: What You Can’t do in a Filtered Index… -> The query optimizer won’t consider filtered indexes if you’re using local variables or parameterized SQL for the predicate that matches the filter.

upvoted 2 times

Mosufe

4 years, 10 months ago

But you can put the status = 'T' in the filtered index, and that is a fixed value

upvoted 3 times

Cococo

4 years, 9 months ago

yes, you're right, filter can be done for the status only, first and last names are going to Include section anyway.

upvoted 1 times

...

stm22

4 years, 11 months ago

agree. we are given: - optimize this stored proc - status = 'T' using that as a fixed filter column will optimize this stored proc

upvoted 3 times

...

Chocho

4 years, 11 months ago

with filtered nonclustered index is not a better option because the value in the filter is not fixed. It's a variable ! so B is correct

upvoted 2 times

Chocho

4 years, 11 months ago

sorry right answer is C (Minimizes index storage size and index key size.) Filtered nonclustered index is smaller than a nonclustered index

upvoted 4 times

...