0

I am doing a join on 2 tables (car and Work).

I am searching on the reg number to find out all the work done on a particular car.

SELECT
    C.RegNum
    ,W.StartDate
    ,W.ShortDesc
    ,w.LabourCost
FROM Work W 
    INNER JOIN Car C ON
    C.CarID = W.CarID
WHERE C.RegNum = 'CP56 CA2L'

I have the below indexes set up:

--  Index on Work Table

CREATE INDEX  IxStart
ON [Work] (StartDate ,ShortDesc,LabourCost)

--  Index on Car Table

CREATE  INDEX IxRegNum
ON [Car] (RegNum)
INCLUDE (ColourID, CarMakeID, CarModelID)

However, I the exection plan is - http://i44.tinypic.com/f5c1mp.png

with a key lookup and output list - http://i44.tinypic.com/9iybdt.jpg

Can someone help me how I would remove the key lookup for a index? Should I re-write the query or change the index?

I cannot understand why the IxRegNum on the car table does not inter join with the IxStart on the work table.

tshepang
  • 12,111
  • 21
  • 91
  • 136
Jay
  • 81
  • 2
  • 10

1 Answers1

0

Had another index on the work table which I removed.

Jay
  • 81
  • 2
  • 10