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 CustomerI
E . Add a nonclustered index for CreditValidation on CustomerI
G . Drop the IX_invoices_CustomerId_Filter_IsCreditValidatedIndex.
Answer: ABE
Explanation:
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
Leave a Reply