How to force SQL to use ad-hoc created Indexes on #temp tables

Inside a stored procedure, one simple and common way of querying/modifying some production data and avoiding unnecessary locks on production tables is to use temporary tables. These tables can be queried/modified and joined with base tables without creating blockages; resulting information can be manipulated easier and faster.

There is a problem though, when working with temporary tables. I am speaking about tables created with # in front which are removed at each session’s closure and only those that are part of a stored procedure code.

If you use a certain temporary table in a join operation with another table, and you have many rows implicated – than you would probably see a performance issue, since you don’t have an index covering both joining columns.
This behavior is also available in order by statements or filter clauses (where involved).

You will see table scans on #(temporary) table and the performance will be very poor. The next logical step is to create the proper indexes right after the table creation.

You are absolutely right to do so, but SQL will ignore this index and exclude it from the Execution Plan, because the Execution Plan was already created when the index didn’t exist. When a stored procedure is called, it is first compiled and Query Optimizer creates an Execution Plan for it. It does not take into consideration the indexes created on temporary tables.

In order to achieve this you can do this by following one of the below methods:

  • Create Primary/Unique keys when creating the temporary table. – this is only efficient if you have unique columns involved in order by or filtering options.
  • Specify the table hint: with (index(name_of_the_index)) right after mentioning the table in a select statement.
     

    Example: Select name from #table with (index (index_id)) where id>1000
  1. Hi,

    It’s already been 5 years since you wrote “How to force SQL to use ad-hoc created Indexes on #temp tables”… My question is:
    Since Stored Procedures first compile and Execution Plan is created before hand, Does this only apply when Stored Procedures are used?

  2. Hi John, I am not sure of your question. “Does this happen” meaning what exactly ? when a SP is compiled a plan is created. If the index is not part of the create temp table statement or if it is not specified with the index hint, than the SP execution plan will not consider that index. And this is only available in SP, because simple queries don’t have this behavior. If the same script is out of the stored procedure than there will be a different execution plan and probably will use the index. As I wrote in the post it only applies to SP.

Leave a Comment


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>