Confused About Index Usage

• Steve F.

  Mr or Mrs. 500

  Points: 587

  More actions

  April 22, 2008 at 1:17 pm

  #120977

  I have a query that returns the following execution plan (I hope this displays reasonably well):

  |--Nested Loops(Inner Join)

  |--Clustered Index Seek(OBJECT: ([MyDatabase].[dbo].[tblCustomer].[PK_tblCustomer_1]), SEEK: ([tblCustomer].[CustomerID]=[@CustomerID]) ORDERED FORWARD)

  |--Filter(WHERE: ([MyTable].[DateSold]>=[@Wk1] AND [MyTable].[DateSold]<=[@Wk2]))

  |--Bookmark Lookup(BOOKMARK: ([Bmk1002]), OBJECT: ([MyDatabase].[dbo].[MyTable]))

  |--Index Seek(OBJECT: ([MyDatabase].[dbo].[MyTable].[IX_MyTable_CustomerID]), SEEK: ([MyTable].[CustomerID]=[@CustomerID] AND [MyTable].[WeekEnding] >= [@CW]) ORDERED FORWARD)

  Yes, the object names are silly; I changed them for the purpose of posting this here.

  Note the index seek on CustomerID and WeekEnding, using the index IX_MyTable_CustomerID. Here is the definition of that index, generated by scripting as a CREATE statement:

  CREATE INDEX [IX_MyTable_CustomerID] ON [dbo].[MyTable]([CustomerID]) ON [PRIMARY]

  GO

  One simple question: How is it possible that the index seek is performed on CustomerID AND WeekEnding if the only field in the index is CustomerID?

  Thanks!

• shamshudheen

  SSCrazy

  Points: 2390

  More actions

  April 25, 2008 at 12:40 am

  #806985

  i think the index seek is not taking weekend column it is taking only customer id , but it is showing full conditional string