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;