Which three actions should you perform?

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.

microsoft-exams

One thought on “Which three actions should you perform?

  1. ADE

    B does not make sense as the optimizer determines the condition order. The ordering of columns in the where clause does not matter in this case.

Leave a Reply

Your email address will not be published. Required fields are marked *


The reCAPTCHA verification period has expired. Please reload the page.