Procedure 'Proc_Level3' calls procedure 'Proc_Level4'. Procedure 'Proc_Level4' populates a temp table. I created an index on the temp table. My 'create index' statement is in 'Proc_Level3'. The index keys are based on the columns used in the join statement (in Proc_Level3) that uses the temp table. However, the index is not being used. So, a table scan of the temp table is taking place. I tried creating the index in 'Proc_Level4' instead of 'Proc_Level3', still wasn't used. The number of rows in the temp table range between 25,000 - 50,000. Any suggestions? Thank you.
Depends on if you're filtering on the indexed column in the temp table. If you're not filtering, then all rows need to be read anyway.
Try creating filtered indexes in this case and use that hint in order to force usage, check the execution plan too.
Thank you all for your replies. The temp table is only being used in join statements. There are no filters specified in the query. I did use the index hint. The optimizer uses an Index Scan. Since the number of rows in the temp table is 25,000 and above, thought by creating an index on the columns used in the join statements, an Index Seek would be used. from #temp_proc_level4 T1 with (index(idx_temp_proc_level4)) inner join Table2 T2 on T1.reg_id = T2.reg_id inner join Table3 T3 on T1.emp_id = T3.emp_id left outer join Table4 T4 on T4.emp_id = T3.emp_id and T4.start_date = T1.start_date left outer join Table5 T5 on T1.emp_id = T5.emp_id
If all rows from the temp table are used in the join, with no filtering, then there is no point in doing a seek - all rows must be read to make up the join.
Yes, that is true. The Index Scan that the optimizer is using is the best option in this case. Thank you. I have created indexes on all the temp tables used in join statements, however, in some cases I get "Missing Statistics" displayed in the (graphical) query plan. Why is that the case? Thank you.