You are a database developer on an instance of SQL Server 2008. Your Prod database contains an InvTransaction table defined as follows:
Users frequently run the following query:
SELECT TranDate, TranType, Quantity
FROM InvTransaction
WHERE TranType = @type AND TranDate BETWEEN @begin AND @end AND Quantity > 100;
Which action should you take to improve the query’s performance?
A. Create a clustered composite index on TranDate, TranType, and Quantity.
B. Create a nonclustered index on TranDate.
C. Create a clustered index on TranDate and a composite non-clustered index on the TranType and Quantity columns.
D. Create a nonclustered index on TranDate and include TranType and Quantity.
Correct Answer: D
Explanation/Reference:
An index created on all the columns in the SELECT list increases the performance of the query because the data required for the query can be directly retrieved from the index. Therefore, only the index pages containing the index will be scanned to return the data, improving query performance. An index that covers all the columns in the SELECT list is referred to as a covering index.
You should not create a clustered composite index on TranDate, TranType, and Quantity, or create a clustered index on TranDate and a composite non-clustered index on TranType and Quantity. Only one clustered index is allowed for each table. A clustered index denotes the physical order of the table. In this scenario, the InvTransaction table contains a primary key. When a primary key is created, a clustered index is created automatically if one does not already exist.
You should not create a nonclustered index on TranDate. This might improve query performance somewhat, but creating a covering index would provide better query performance.
