Would it be a good idea to apply indexing on temp tables when it is suggested to do so in the estimated execution plan?
Sql-server – Indexing a temp table
indexsql servertemporary-tables
Related Question
- Sql-server – Estimated plan generation succeeds for batch creating and using #temp table but fails with a permanent table
- Sql-server – Using table variable instead of temp table makes query execution slow
- Sql-server – Temp Table Clustered Key Not Being Honored: Bug or Expected Functionality
- Sql-server – Bad execution plan after stats update due to temp table
- Sql-server – logical reads on global temp table, but not on session-level temp table
- Sql-server – msdb.dbo.sp_send_dbmail temp table does not exist
Best Answer
Yes it is a good idea. Temp tables will benefit from indexes in the same way that permanent tables will. Index creation is the same, too, with a few minor exclusions.
The indexes function in the same way as they do on permanent tables, with the exception that temp tables are cached, which can hurt if the workloads are skewed significantly.
The same caveats apply too. Just because an index exists does not mean the optimizer will use it. Writes will be costlier with many indexes in place. Stale statistics can produce poor plans.
Recently I was able to reduce a large batch procedure's run time from over 20 minutes to under three by adding indexes to temp tables. This is a worthwhile task.
See also Indexing Temp Tables by Erik Darling.