Exam 70-761 topic 1 question 167 discussion

Actual exam question from Microsoft's 70-761

Question #: 167
Topic #: 1

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You are creating indexes in a data warehouse.
You have a dimension table named Table1 that has 10,000 rows. The rows are used to generate several reports.
The reports join a column that is the primary key.
The execution plan contains bookmark lookups for Table1.
You discover that the reports run slower than expected.
You need to reduce the amount of time it takes to run the reports.
Solution: You create a nonclustered index on the primary key column that does NOT include columns.
Does this meet the goal?

A. Yes
B. No

Show Suggested Answer

Suggested Answer: A 🗳️
References:
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described?view=sql-server-2017

by mlourinho at Nov. 20, 2019, 11:25 a.m.

Comments

Submit Cancel

mlourinho

Highly Voted 5 years, 7 months ago

I think the answer is B, because Primary Keys should have a clustered Index .

upvoted 12 times

mlourinho

5 years, 7 months ago

when creating a primary key automatically creates a corresponding unique clustered index, or a nonclustered index if specified as such, therefore, if the table has a Primary Key an index already exists. If the Index already exists, it may be : » Clustered, therefore changing the index to a non-clustered index will not help » non-clustered, therefore the change will no be much

upvoted 6 times

...

eggzamtaker

4 years, 5 months ago

The answer is B not because of the reason you have given. The answer is B because for starters the suggested solution does not solve the problem of bookmark lookups.

upvoted 1 times

...

Billybob0604

Most Recent 4 years, 5 months ago

https://stackoverflow.com/questions/820714/what-is-a-bookmark-lookup-in-sql-server

upvoted 1 times

...

Kisima

4 years, 5 months ago

so which is best answer?

upvoted 1 times

...

HA2020

4 years, 7 months ago

https://askinglot.com/open-detail/559990 From it: No is correct, explaination: " Lookup Table in SQL Server. It can be basically considered as a reference to link the related tables with the help of a unique primary key. " " Consider a situation, where an organization needs to maintain a database to store its employee information. A SQL table is created with the fields (Emp ID, Name, Membership). Only four membership options are present in the organization, so the membership field can hold a value out of the four different options. In case of thousand records, the membership field will be repeating among all the records and this field is a kind of static field, which does not change frequently. So, the user can choose to create a child table i.e., lookup table containing Emp id and membership fields of the main table. So, we can easily reference the fields of the child table with the main table of the database. "

upvoted 1 times

...

stm22

4 years, 11 months ago

B. i agree with the other comments in this discussion.

upvoted 2 times

...

Marshy

5 years, 2 months ago

The question states "The execution plan contains bookmark lookups for Table1. " Adding an index "without any columns" will not fix this and additionally as already mentioned a clustered index already exists...answer should be B

upvoted 3 times

...