You administer a SQL Server 2008 database that contains a table name dbo.Sales, which contains the following table definition:
CREATE TABLE [dbo].[Sales](
[SalesID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[OrderDate] [datetime] NOT NULL,
[CustomerID] [int] NOT NULL,
[SalesPersonID] [int] NULL,
[CommentDate] [date] NULL);
This table contains millions of orders. You run the following query to determine when sales persons comment in the dbo.Sales table:
SELECT SalesID,CustomerID,SalesPersonID,CommentDate FROM dbo.Sales
WHERE CommentDate IS NOT NULL AND SalesPersonID IS NOT NULL;
You discover that this query runs slow. After examining the data, you find only 1% of rows have comment dates and the SalesPersonID is null on 10% of the rows. You need to create an index to optimize the query. The index must conserve disk space while optimizing your query. Which index should you create?
A.
CREATE NONCLUSTERED INDEX idx1
ON dbo.Sales (CustomerID)
INCLUDE (CommentDate,SalesPersonID);
B.
CREATE NONCLUSTERED INDEX idx1
ON dbo.Sales (SalesPersonID)
INCLUDE (CommentDate,CustomerID);
C.
CREATE NONCLUSTERED INDEX idx1
ON dbo.Sales (CustomerID)
INCLUDE(CommentDate)
WHERE SalesPersonID IS NOT NULL;
D.
CREATE NONCLUSTERED INDEX idx1
ON dbo.Sales (CommentDate, SalesPersonID)
INCLUDE(CustomerID)
WHERE CommentDate IS NOT NULL;