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:([transactio n_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 and transaction_notes columns.
D.
Create a nonclustered index on the transaction_date and transaction_type_id columns and include the transaction_notes column.