Exam 70-762 All Questions

View all questions & answers for the 70-762 exam

Exam 70-762 topic 1 question 62 discussion

Actual exam question from Microsoft's 70-762

Question #: 62
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:

Does the solution meet the goal?

A. Yes
B. No

Show Suggested Answer

Suggested Answer: B 🗳️
Create a clustered index on the AccountNumber column as it is unique, not a non nonclustered one.
References:
https://msdn.microsoft.com/en-us/library/ms190457.aspx

by MML at July 7, 2020, 6:34 p.m.

Comments

Submit Cancel

MML

Highly Voted 4 years, 11 months ago

No is the the right answer.. Also there is an error on create PK_Account index, it should be : create nonclustered index PK_Account on Account(AccountNumber);

upvoted 6 times

Hiken90

4 years, 11 months ago

Creating those index the table scan should be substitute with index scan or not?

upvoted 1 times

...

Barbedx

Most Recent 4 years, 5 months ago

Test it. Index will work only for first query as index seek, in second query we will have Table Scan anyway, SO ANSWER IS NO

upvoted 1 times

Barbedx

4 years, 5 months ago

create table testAccount ( accN int not null, productCode char(2) not null, status tinyint not null, Balance decimal(15,2), ) insert into testAccount values ( FLOOR(RAND()*10000000), FLOOR(RAND()*100), 0, RAND()*10 ) go 1000000 create nonclustered index pk_account_pc on testAccount (accN) CREATE NONCLUSTERED INDEX ix_account_pc ON [dbo].[testAccount] ([productCode]) INCLUDE ([Balance]) select Productcode, sum(balance) as t from testAccount where productCode <> '10' group by productCode select accN,balance from testAccount where productCode = '10'

upvoted 1 times

...

eggzamtaker

4 years, 5 months ago

From briefmenow.com: "A Yes. The second index will avoid table scans. There was no requirement related to clustered index or uniqueness however stupid it would be that one would create these 2 indexes. The goal is to avoid table scans."

upvoted 1 times

...

HA2020

4 years, 5 months ago

it is a no, the percentage of 100/1B is too small that the optimizer would ignore a non-clustered index that is also not covering..it is cheaper to do scans than to bother with look-ups. I think the solution needs to be a filtered index or columnstore filtered index.

upvoted 1 times

...

geekeek1

4 years, 5 months ago

So what is the correct answer?

upvoted 1 times

...

CharlieBrownIsGreat

4 years, 7 months ago

I just created the tables. The first query <> 'CD' works without a table lookup. What it does is an index seek and a RID Lookup (which makes sense since no PK is defined). The second query would probably work if the table had 1 million rows; however it does a table scan I'm quite certain because of statistics.

upvoted 1 times

CharlieBrownIsGreat

4 years, 7 months ago

Just to be clear the answer is YES.

upvoted 1 times

...

giuPigna

4 years, 7 months ago

The answer is NO, because NCI on AccountNumber is useless. The only NCI on ProductCode avoids table scans (it uses index seeks) and requires RID Lookup operations. The best solution is NCI on ProductCode INCLUDE Balance and CI on AccountNumber. In this case NCI on ProductCode has the reference to CI in its structure, so no key lookup are needed for the second statement (only index seek).

upvoted 2 times

...

kiri2020

4 years, 7 months ago

no need for index on AccountNumber column, but the second index on ProductCode column and including Balance will meet the goal - index seek (no table scan) for both select statements.

upvoted 1 times

...

SoupDJ

4 years, 8 months ago

I think the answer is yes: (1) Both clustered and nonclustered indexes can be unique - the fact that the PK index has unique accountid doesn't automatically require that it be made a clustered index - maybe there already is one! An index seek CAN be used by the optimizer on a non-clustered index - it just indicates that the optimizer recognizes that an index is helpful in filtering the rows.

upvoted 2 times

...

Andy7622

4 years, 8 months ago

There is an aggregate function, and billion rows. Isn't columnstore index a better option here?

upvoted 4 times

Hoglet

4 years, 6 months ago

Yes, but the question is does it meet the requirement. Yes. Is it the best solution? No

upvoted 3 times

...

Lukis92

4 years, 9 months ago

WHERE ProductCode <> 'CD' - always index scan

upvoted 1 times

Lukis92

4 years, 9 months ago

When we have a nonclustered index. Index seek is only on the clustered index.

upvoted 1 times

databasejamdown

4 years, 8 months ago

Unless I am misunderstanding, I have to disagree. The optimizer can use the non clustered index for an index scan operation because each page in the leaf level of the index stores less columns and more data. As a result, you have less pages to travers. In this case, an index seek on the non-clustered index is faster than a table scan

upvoted 3 times

...