Is a Temporary Table Really Necessary? - PART IV:

How can you work around using a temporary table?

Now that I shown you several situations when you consider using a temporary table, lets talk about what you can do to avoid using a temporary table if all possible.There is a nice thing in the SQL world called a derived table that can be used to replace temporary tables in most cases.

Once again I'll get on my performance soapbox and say that sometimes with very large data sets, derived tables performance is considerably less than using a temporary table with an index. But for most cases simply using a derived table on a join will cut the need for your temporary table.

You can find several articles on the use of derived table at WWW.SQLServerCentral.Com so I will not go into detail on their use in this article.

If you are using a temporary table to stage data from several different sources either replace the temporary table with a UNION or create a permanent table to mimic the temporary one, both will usually satisfy your needs with reduced overhead. If you are operating on SQL Server 2000 and are using small data sets, try using the new table data type.

This will create a temporary table like object in memory rather than on the tempdb and improve the performance of your query.

Explore the use of a correlated sub-query and see if it can replace your temporary table. Sometimes just restating where your data is coming from will replace the need for temporary tables. Any one of these ways has been discussed as possible alternative solutions to the use of a temporary table.