70-433 Question #38: Real Exam Question with Answer & Explanation

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?