DRAG DROP
You have a database that contains three tables. The tables are configured as shown in the following table.
You have the following query:
The execution plan for the query is shown in the exhibit. (Click the Exhibit button.)
You need to create one index to minimize the amount of time it takes to execute the query.
What should you do?
To answer, drag the appropriate columns to the correct locations in the answer area.
(Answer choices may be used once, more than once, or not at all.)
Answer: See the explanation.
Explanation:
Box 1:
Box 2:
Box 3:
Note:
Covering index:A type of index that includes all the columns that are needed to process a
particular query. For example, your query might retrieve the FirstName and LastName
columns from a table, based on a value in the ContactID column. You can create a covering
index that includes all three columns.
I Think it should OrderDate + Last name and first name
You want to create one index on the columns from two diffrent tables?
I think it’s OrderDate + SalesPersonId and SubTotal.
Order date is the only selective field, which can filter orders older than 2012, and do not read them from disk.
SalesPersonId is not selective becouse you still need to read data of all orders (assumming that every order have Sales Person)
+1
agree – index must be on table that is filtered in the query to achieve the goal “to minimize the amount of time it takes to execute the query”
I agree.
Of course in indexing the where clause takes precedence over the other clauses.
For indexing we should choose table SalesOrderHeader soh
and cover all her columns:
The most important is OrderDate in where clause – it will be indexed column
salesPersonID and subTotal – included columns
INDEXED COLUMN:
SalesOrderheader.OrderDate
INCLUDED COLUMNS:
SalesOrderheader.SalesPersonID
SalesOrderheader.SubTotal
Yep, Vladimir is right
The operator with the highest cost is on salesOrderHeader (63%), therefore one should try to optimize it.
I believe: salespersonID include(OrderDate, Subtotal) would have the biggest impact in performance.