Exam 70-762 All Questions

View all questions & answers for the 70-762 exam

Exam 70-762 topic 1 question 146 discussion

Actual exam question from Microsoft's 70-762

Question #: 146
Topic #: 1

You are optimizing the performance of a batch update process. You have tables and indexes that were created by running the following Transact-SQL statements:

The following query runs nightly to update the isCreditValidated field:

You review the database and make the following observations:
✑ Most of the IsCreditValidated values in the Invoices table are set to a value of 1.
✑ There are many unique InvoiceDate values.
✑ The CreditValidation table does not have an index.
✑ Statistics for the index IX_invoices_CustomerID_Filter_IsCreditValidated indicate there are no individual seeks but multiple individual updates.
You need to ensure that any indexes added can be used by the update query. If the IX_invoices_CustomerId_Filter_IsCreditValidated index cannot be used by the query, it must be removed. Otherwise, the query must be modified to use with the index.
Which three actions should you perform? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.

A. Add a filtered nonclustered index to Invoices on InvoiceDate that selects where IsCreditNote= 1 and IsCreditValidated = 0.
B. Rewrite the update query so that the condition for IsCreditValidated = 0 precedes the condition for IsCreditNote = 1.
C. Create a nonclustered index for invoices in IsCreditValidated, InvoiceDate with an include statement using IsCreditNote and CustomerID.
D. Add a nonclustered index for CreditValidation on CustomerID.
E. Drop the IX_invoices_CustomerId_Filter_IsCreditValidatedIndex.

Show Suggested Answer

Suggested Answer: ABE 🗳️
A filtered index is an optimized nonclustered index especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance as well as reduce index maintenance and storage costs compared with full-table indexes.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/create-filtered-indexes

by lukadataowner at April 17, 2020, 5:45 p.m.

Comments

Submit Cancel

lukadataowner

Highly Voted 5 years, 1 month ago

the correct answer is: ADE

upvoted 28 times

stm22

4 years, 10 months ago

that is my opinion too

upvoted 1 times

Mosufe

4 years, 9 months ago

I think that it is better, because the order on the where clausule doesnt matter

upvoted 1 times

Mosufe

4 years, 9 months ago

The optimizer reviews the query & determines the best means of getting the data based on indexes and such. Even if there were a covering index on the category_id and author columns - either would satisfy the criteria to use it (assuming there isn't something better).

upvoted 1 times

...

Alex5x

Most Recent 4 years, 7 months ago

Let's go step by step here. 1. Since the questions says: "Statistics for the index IX_invoices_CustomerID_Filter_IsCreditValidated indicate there are no individual seeks but multiple individual updates.", it means we should drop that useless index. So the option E is part of the answer. Hey, we already got one point:) 2. Since we decided to drop the existing index, it would be a good idea to create another one which will work for the UPDATE quiery. The query uses two tables, so if we really want to optimize the update we should create two indexes: one for each table (pay attention that the Q says "The CreditValidation table does not have an index.")

upvoted 3 times

Alex5x

4 years, 7 months ago

3.Let's start from table CreditValidation. The search is done by two columns: CustomerID and ValidationDate. So, the ideal index for that seach will be: CREATE INDEX IX1 on CreditValidation (CustomerID, ValidationDate); Another, less efficient option could be: CREATE INDEX IX2 on CreditValidation (CustomerID) INCLUDE (ValidationDate); Anyway, if we don't include ValidationDate into the index (either into keys or to include), we will get key lookup operation in the execution plan because the query should work with ValidationDate column which will be taken from the clustered index. Unfortunately, the only option offering to create an index on the CreditValidation - D does not say anything about ValidationDate column. That option means: CREATE INDEX IX3 on CreditValidation (CustomerID); This index is still OK because it will be used, but it is not ideal. We take the option D. Now we have two points.

upvoted 2 times

Alex5x

4 years, 7 months ago

4. The second index is for Invoices table. Ideally it should look like this: CREATE INDEX IX4 on Invoices (InvoiceDate) INCLUDE (CustomerID) WHERE IsCreditNote = 1 AND IsCreitValidated = 0; This is because we search only by InvoiceDate. CustomerID is just passed to search in the CreditValidation table. Unfortunately, none of the options (A, C) mean this index. So again we should find the closest to IX4. From the Q: "Most of the IsCreditValidated values in the Invoices table are set to a value of 1.", "There are many unique InvoiceDate values." It means that the index should have the order of the key columns: InvoiceDate, IsCreditValidated and not IsCreditValidated, InvoiceDate. This means that we can cross out the option C. The remaining option is A: CREATE INDEX IX5 on Invoices (InvoiceDate) WHERE IsCreditNote = 1 AND IsCreitValidated = 0; This index is not covering because it does not have CustomerID, and as a result we produce key lookup operation, but it will work here. The option B is absolute nonsense. We can ignore it immediately. As a result we have: A, D, E.

upvoted 3 times

...

tesc40

4 years, 11 months ago

Why not CDE? Isn't option C better when compared to option A?

upvoted 1 times

gmu

4 years, 10 months ago

The value for IsCreditNote and IsCreditValidated are always 0 and 1. A filtered index works better.

upvoted 4 times

...

Nickname17

5 years, 1 month ago

Can we really create an index on sth not existed? Such as InvoiceDate?

upvoted 2 times

...