Exam 70-762 All Questions

View all questions & answers for the 70-762 exam

Exam 70-762 topic 1 question 77 discussion

Actual exam question from Microsoft's 70-762

Question #: 77
Topic #: 1

HOTSPOT -
You create a database table named FactSales by running the following Transact-SQL statements:

You must optimize the indexes without making changes to the ix_FactSales_EmployeeKey index.
You need to implement a columnstore index for the table.
How should you complete the Transact-SQL code? To answer, select the appropriate Transact-SQL segments in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

Show Suggested Answer

Suggested Answer:

by lukadataowner at April 16, 2020, 9:04 a.m.

Comments

Submit Cancel

lukadataowner

Highly Voted 5 years, 2 months ago

sorry I didn't see the index on fact table, the answer is correct

upvoted 19 times

...

MML

Highly Voted 4 years, 11 months ago

The answer is correct

upvoted 10 times

...

TheDUdeu

Most Recent 4 years, 5 months ago

The answer is right. Your dropping the current clustered index and making it a columnstore and also you drop the other indexes and don't touch the one index because it says not to.

upvoted 2 times

...

raja1234567890

5 years ago

My bad, got to know of this. Actually, beginning with SQL Server 2016 (13.x) no special action is required; SQL Server automatically defines and rebuilds the nonclustered indexes on the new clustered columnstore index.

upvoted 2 times

...

raja1234567890

5 years ago

Also non-clustered index should be rebuild whenever there is a change in clustered index. Can someone please clarify this?

upvoted 2 times

...

Matt95

5 years, 1 month ago

Could someone explain the logic behind this answer? I don't understand why the CustomerKey index has to be dropped.

upvoted 1 times

LeonLeon

4 years, 11 months ago

It’s in the question. “must optimize indexes except...”. We do not want to much indexes, although they are rebuild by default. It’s just how you read it, because it also works without dropping, but I think they want us to cleanup by default. The exception is ment specific

upvoted 2 times

...

lukadataowner

5 years, 2 months ago

3 box is wrong. With(Drop_Existing=on) DROP_EXISTING is by default OFF, and it does not work if you are including this option while creating a new index for the first time. DROP_EXISTING = ON in SQL Server only works if you already have an index with the specified name on a table.

upvoted 3 times

delgadillo

4 years, 11 months ago

i agree with you.

upvoted 1 times

...

Mosufe

4 years, 10 months ago

If this is the case, the answer is correct, because the index ix_FactSales was created in the definition of the table ^^

upvoted 8 times

...