Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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;
share|improve this question
What indexes do you have on the tables? What is the execution plan now? Add the CREATE TABLE scripts. – ypercubeᵀᴹ Mar 28 '14 at 12:22

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

share|improve this answer
If there were an extra 10 columns returned, would I just add these to the INCLUDE statement as well? – k09 Mar 28 '14 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 '14 at 21:46

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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