You can define indexes on temporary tables. In many cases, these indexes can improve the performance of queries that use tempdb. The optimizer uses these indexes just like indexes on ordinary user tables. The only requirements are:
The table must contain data when the index is created. If you create the temporary table and create the index on an empty table, Adaptive Server does not create column statistics such as histograms and densities. If you insert data rows after creating the index, the optimizer has incomplete statistics.
The index must exist while the query using it is optimized. You cannot create an index and then use it in a query in the same batch or procedure.
The optimizer may choose a suboptimal plan if rows have been added or deleted since the index was created or since update statistics was run.
Providing an index for the optimizer can greatly increase performance, especially in complex procedures that create temporary tables and then perform numerous operations on them.
