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.