WikiDBA

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.