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.