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)

CREATE NONCLUSTERED INDEX idx_Test ON #TblTest (ID)

DROP TABLE #TblTest

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.

Advertisements
Comments
  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 – http://www.sqlserver-dba.com/2013/08/scan-for-startup-procs-option-when-to-use.html

    Like

  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?

    Like

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s