I have 2 tables (tblA and tblB) joined by ReportID key. This key is a Primary Clustered key on tblA, and is a FK on tblB. I have created a Non-clustered index on UserID and UserDate in tblA.

I have a query where I want to return a number of rows from tblB based off a ReportID selection from tblA.

My issue is the Execution Plan runs a Clustered Index SCAN on tblB. How do I get it to perform a seek? Or how can I improve the query execution?


    tblB b
    tblA a
    ON a.ReportID = b.ReportID
WHERE a.UserID = @UserID
    AND a.UserDate = @Period;
  • What indexes do you have on the tables? What is the execution plan now? Add the CREATE TABLE scripts. Mar 28, 2014 at 12:22

1 Answer 1


Put a non-clustered index on b.ReportID, and 'include' all the other columns from b that your query uses.

  • If there were an extra 10 columns returned, would I just add these to the INCLUDE statement as well?
    – K09
    Mar 28, 2014 at 15:03
  • Yes. Otherwise, your index is not enough to solve the query, and lookups would be needed. If it thinks too many lookups need to be done, it could well figure the index simply isn't good enough and divert to using the Clustered Index.
    – Rob Farley
    Mar 29, 2014 at 21:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.