||SQL Server Tips by Gama and Naughter
Temporary tables are very useful
for storing intermediate values from time consuming calculations,
store the output rows from a SP, serve as a buffer for data waiting
to be processed, avoid a cursor with a heavy load, avoid repeating
queries with a huge number of rows or complex joins, an alternative
to recursive solutions, etc.
Temporary tables are very
effective with huge amounts of data. By filtering only the strictly
necessary rows and columns and storing the result in a temporary
table, for several gigantic tables, this will create mini versions,
easier to use. Doing this before a SELECT statement with a JOIN
clause that would use those temporary tables and with carefully
chosen indexes, the result will be a great performance boost.
One of the downsides is that it
creates locks on tempdb.
It is recommended to avoid SELECT
INTO with temporary tables because it locks system objects causing
performance degradation. Extra care is required with clustered
indexes to make sure that the extra overhead will not outweigh the
There are three kinds of temporary
tables: local, global and table datatype.
The above book excerpt is from:
Turbocharge Database Performance with C++ External Procedures
Joseph Gama, P. J. Naughter