Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AAAV
Posting Yak Master
152 Posts |
 Posted - 2010-03-22 : 15:52:07
|
| i am preparing for 70-433 i saw this question when i just googled for model questions 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] IDENTIT (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 idx1ON 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; Answer: D Can someone explain? |
|
|
DaleTurley
Yak Posting Veteran
76 Posts |
Posted - 2010-03-23 : 08:04:41
|
| Which part do you not understand? The index keys, included column or the filter? |
 |
|
|
AAAV
Posting Yak Master
152 Posts |
Posted - 2010-03-23 : 09:24:29
|
| I dont understand INCLUDE(CustomerID) WHERE CommentDate IS NOT NULL and why sales id is not there?i think it is indexed on other 2 fields because of they have values only in 1% and 10% of the rows.Correct me if i am wrong |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-23 : 10:03:49
|
quote:
Originally posted by AAAV
I dont understand INCLUDE(CustomerID) WHERE CommentDate IS NOT NULL and why sales id is not there?i think it is indexed on other 2 fields because of they have values only in 1% and 10% of the rows.Correct me if i am wrong
what you're doing is creating index for only cases where CommentDate IS NOT NULL which will cause index to be created for only 1 %.SalesID already has a clustered index on it.So this index in turn will point to clustered index from which it can access SalesID info. and since you're including CustomerID by virtue of INCLUDED column the query gets completely covered by above index------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Test questions