As per BOL, “Indexes can be created on a temporary table. When the table is dropped or the session ends, all indexes and triggers are dropped

The general rule is to avoid using temp tables, usually in favor of derived tables or table variables, but there are times when it seems that nothing else will do the job. Or, maybe we just inherit complex code that already makes extensive use of temp tables. Frequently, query performance against large temp tables can benefit from adding a few well-chosen indexes.

Its Sometimes observed the optimizer is intent on ignoring the very performance-boosting indexes that we’ve just created. This usually happens because the access plans for the temp tables have been generated before the indexes ever existed.

Here is an example.

CREATE TABLE #TblTest (ID int)



Temporary tables comply with the same rules as permanent tables when it comes down to indexing. The only difference is in the storage location, which is Tempdb for temporary tables. However, if an index added to a table that is heavily written, just be sure about the write vs. read tradeoff.

Since the temporary table is probably used in a procedure or in a script, it’s depend on code that controls how hardly its hitting the table with writes. INSERTs are faster without indexes in place: if insertion amount of data is so high using lots of multiple statements, we must want to create the index after fully populating the table. UPDATEs and DELETEs have to find the row(s) to modify first, so they could highly benefit from proper indexing.

  1. Autogrow is another potential performance issue related to overly heavy usage of TempDB , particuarly if you are running code generating large amounts of INSERTS –


  2. Alexander Suprun says:

    There is no such rule to avoid temp tables in favor of table variables.
    Do you know the differences between these two? Do you know that there is no statistics collected for table variable, which impacts estimations and ruins execution plan in some cases?


Leave a Reply to Jeremy Singer Cancel reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s