Exam 70-761 topic 1 question 149 discussion

Actual exam question from Microsoft's 70-761

Question #: 149
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 clustered index on the primary key column.
Does this meet the goal?

A. Yes
B. No

Show Suggested Answer

Suggested Answer: A 🗳️

by raja1234567890 at Nov. 13, 2019, 4:23 a.m.

Comments

Submit Cancel

raja1234567890

Highly Voted 5 years, 7 months ago

If primary key is created, then clustered index is already created. if report is running slow while joining with primary key. How does performance improve when index is recreated.

upvoted 12 times

Anette

5 years ago

A primary key is a logical concept - it's the unique identifier for a row in a table. As such, it has a bunch of attributes - it may not be null, and it must be unique. Of course, as you're likely to be searching for records by their unique identifier a lot, it would be good to have an index on the primary key. A clustered index is a physical concept - it's an index that affects the order in which records are stored on disk. This makes it a very fast index when accessing data, though it may slow down writes if your primary key is not a sequential number. Yes, you can have a primary key without a clustered index - and sometimes, you may want to (for instance when your primary key is a combination of foreign keys on a joining table, and you don't want to incur the disk shuffle overhead when writing). Yes, you can create a clustered index on columns that aren't a primary key. So. I think the answer is YES (A).

upvoted 13 times

...

Andy7622

4 years, 6 months ago

Primary can be nonclustered

upvoted 2 times

...

sssshhhh

Most Recent 4 years, 5 months ago

I think they want you to realize that the bookmark lookup indicates a need for a non-clustered index with some INCLUDE columns. A primary key column automatically has a clustered index already, rebuilding it wouldn't change the current (too slow) performance. So i think B (no).

upvoted 2 times

...

VIKU

5 years, 2 months ago

in this case covering index would be useful. hence answer is B.

upvoted 2 times

...

fabzo

5 years, 7 months ago

Needs to be nonclustered

upvoted 1 times

...