You are a database developer. You plan to design a database solution by using SQL Server 2008. The database application has a table named Transactions that contains millions of rows. The table has multiple columns that include transaction_id and transaction_date. There is a clustered index on the transaction_id column. There is a nonclustered index on the transaction_date column. You discover that the following query takes a long time to execute:
SELECT transaction_id, transaction_date, transaction_notes
FROM transactions
WHERE transaction_type_id = ‘FXO’
AND transaction_date between @start_date and @end_date
The summary of the execution plan is as shown in the following code segment:
|– Filter(WHERE:([transaction_type_id]=’FXO’)
|– Nested Loops(Inner Join)
|– Index Seek(OBJECT:([transactions]. [nc_transactions_transaction_date])
|– Clustered Index Seek(OBJECT:([transactions]. [PK_transactions_transaction_id])
You need to ensure that the query retrieves data in minimum possible time. What should you do?
A.
Create a nonclustered index on the transaction_type_id column.
B.
Create a nonclustered index on the transaction_date and transaction_type_id columns.
C.
Create a nonclustered index on the transaction_date column and include the transaction_type_id andtransaction_notes columns.
D.
Create a nonclustered index on the transaction_date and transaction_type_id columns and include thetransaction_notes column.