Exam 70-762 topic 1 question 28 discussion

Actual exam question from Microsoft's 70-762

Question #: 28
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 os 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 run the following query:

The following image displays the execution plan the query optimizer generates for this query:

Users frequently run the same query with different values for the local variable @lastName. The table named Person is persisted on disk.
You need to create an index on the Person.Person table that meets the following requirements:
1. All users must be able to benefit from the index.
2. FirstName must be added to the index as an included column.
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 🗳️
By including nonkey columns, you can create nonclustered indexes that cover more queries. This is because the nonkeycolumns have the following benefits:
They can be data types not allowed as index key columns.
They are not considered by the Database Engine when calculating the number of index key columns or index key size.
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/create-indexes-with-included-columns?view=sql-server-2017

by Nickname17 at May 17, 2020, 4:06 a.m.

Comments

Submit Cancel

stm22

Highly Voted 4 years, 10 months ago

clue: "must be added to the index as an included column". Included columns point to a nonclustered index.

upvoted 13 times

...

Nickname17

Most Recent 5 years ago

I see .. because it says “ Users frequently run the same query with different values for the local variable @lastName”

upvoted 1 times

...