Exam 70-762 All Questions

View all questions & answers for the 70-762 exam

Exam 70-762 topic 1 question 63 discussion

Actual exam question from Microsoft's 70-762

Question #: 63
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. Determine whether the solution meets the stated goals.
The Account table was created using the following Transact-SQL statement:

There are more than 1 billion records in the Account table. The Account Number column uniquely identifies each account. The ProductCode column has 100 different values. The values are evenly distributed in the table. Table statistics are refreshed and up to date.
You frequently run the following Transact-SQL SELECT statements:

You must avoid table scans when you run the queries.
You need to create one or more indexes for the table.
Solution: You run the following Transact-SQL statement:
CREATE CLUSTERED INDEX PK_Account ON Account(ProductCode);
Does the solution meet the goal?

A. Yes
B. No

Show Suggested Answer

Suggested Answer: B 🗳️
We need an index on the productCode column as well.
References:
https://msdn.microsoft.com/en-us/library/ms190457.aspx

by neko at Dec. 28, 2019, 8:32 p.m.

Comments

Submit Cancel

neko

Highly Voted 5 years, 6 months ago

Since it creates a clustered Index for ProductCode, I think the answer meant by "We need an index on the AccountNumber column as well." instead of "ProductCode"

upvoted 16 times

kiri2020

4 years, 7 months ago

why do we need an index on AccountNumber??? The answer is Yes, the clustered index on ProductCode will have index seek (no table scan) for both queries.

upvoted 4 times

...

mrn0107

Highly Voted 5 years ago

The Answer is 'YES'. It is easy to test this. Create table, and start query with execution plan, then create index and run it again.

upvoted 6 times

kiri2020

4 years, 7 months ago

yes, I tested too

upvoted 5 times

...

MML

4 years, 11 months ago

the answer is Yes because with these indexes you have Index seeks and no table scans..

upvoted 6 times

...

Hoglet

Most Recent 4 years, 6 months ago

The answer is No You could create that index, non-unique clustered index. You have a table of 1 billion rows and 100 Product Codes, giving an average of 10million rows per Product Code If you SELECT ... FROM Accounts WHERE ProductCode = 'CD', SQL Server will seek to the first entry with that Product Code and then SCAN the Clustered Index (ie the Table) for the next 10 million rows. The question is to avoid scan of the Tables

upvoted 2 times

Andy7622

4 years, 5 months ago

Yes, it's easier to scan 900000000 rows for query optimizer than to seek

upvoted 1 times

...

giuPigna

4 years, 7 months ago

Guys read carefully each sentence. The answer is NO because of this: "The Account Number column uniquely identifies each account".

upvoted 2 times

TheDUdeu

4 years, 6 months ago

You can still have a unique constraint on the table doesn't necessarily mean it is a primary key.

upvoted 1 times

...

Gocsan

4 years, 9 months ago

I've tested this by creating the table and the index. It certainly does avoid table scan. So the answer is yes.

upvoted 5 times

databasejamdown

4 years, 8 months ago

Does it avoid the table scan for the first query also?

upvoted 1 times

...

Cococo

4 years, 9 months ago

Clustered index on ProductCode will not help the first query where ProductCode filter <> 'CD', the optimizer needs to look for the 99 (out of 100) other codes which are evenly distributed.. full scan brothers.. amen

upvoted 3 times

kiri2020

4 years, 7 months ago

not true, I tested and got Clustered Index Seek

upvoted 3 times

Braindripper

4 years, 5 months ago

you have tested on 1 billion records? query plan will.. change his mind maybe to find this answers!

upvoted 1 times

eggzamtaker

4 years, 5 months ago

the goal is to eliminate a 'table scan'. the fact that there's a clustered index "scan" is irrelevant.

upvoted 1 times

...

delgadillo

4 years, 11 months ago

But in this case, the only thing that matters is avoid avoid table scans when you run the queries, so for me is yes, because the operator change to index seek.

upvoted 3 times

...

9078990

5 years ago

agree with neko

upvoted 1 times

...