You work as the database developer for Domain.com. The Domain.com network contains a database server named Certkiller -DB01. Certkiller -DB01 hosts a database named CK_Sales that stores sales data for the company. Domain.com Sales Representatives should be allowed to check the current commissions due to them.
You need to optimize the indexing strategies for the CK_Sales database. You need to design the indexes for the Orders table. The following query is frequently executed, though it is not the most commonly executed query.
SELECT Salesrepresentative, SUM(Commission)
FROM Orders
WHERE Date BETWEEN @startDate AND @endDate
GROUP BY Salesrepresentative
ORDER BY Salesrepresentative
You need to use the appropriate statement to create the best index to accommodate this query.
What statement should you use?
A.
CREATE INDEX ix_Commission
ON Orders(Salesrepresentative, Date, Commission)
B.
CREATE CLUSTERED INDEX ix_Commission
ON Orders(Salesrepresentative, Date)
C.
CREATE INDEX ix_Commission
ON Orders(Date)
INCLUDE (Salesrepresentative);
D.
CREATE INDEX ix_Commission
ON Orders(Date, Salesrepresentative)
INCLUDE (Commission);
Explanation:
The Date column is used to select the records and the Salesrepresentative column is used to group and order the records. This means that both these columns have to be key columns. The Date column is used in a BETWEEN comparison, thus it should also be the first in the query. Furthermore, it has higher selectivity than the Salesrepresentative column.
Incorrect Answers:
A: The column used for equality or BETWEEN comparisons should be listed first. This must then be followed by the most selective column, then the rest of the predicate columns in order of decreasing selectivity. And, although it is possible that you can create an index by using a computed column as the key column, it is recommended that key columns be kept as narrow as possible. This means that making use of an included column for Commission is a better option.
B: A Clustered index should have high selectivity. The Salesrepresentative column does not have high selectivity. This means that this clustered index will not be appropriate for any other queries done against the table like retrieving order information for instance. A table can only have one unique index.
C: The Salesrepresentative column is used in the
GROUP BY clause. Thus it would be better suited as the key column rather than an included column. Furthermore, this is not a covering index as it does not include Commission as an included column.