Many SQL Server databases use TEMP or temporary tables to store information used in processing data. TEMP tables can be a convenient way to organize data sets that are too large to be stored in variables, transient data used in calculations, and data that changes so frequently that it doesn't make sense to store it in a permanent table.
On average, a TEMP table will perform similarly to a regular table for both reads and writes. In memory there is no difference between data stored in either table type, the table's name and stored location is only a factor when an I/O operation is required. If you have a heavily used TEMP table, one that is created frequently, having a permanent table will save you some processing overhead. The word "frequently" depends upon not only how many times the table needs to be processed; it depends on the size of the data set as well.
One important advantage of a regular table is that it can have defined indexes with populated data. A TEMP table won't have any indexes available to use unless you define them, and build them at the time of definition. For a TEMP table created frequently index building is another overhead issue, but for a TEMP table without an index that table retains a small advantage over a regular indexed table. Operations such as SELECTs that can make use of an index are faster for regular tables.
The fact is that most people use TEMP tables to store smaller data sets. Given SQL Server's performance you'd be hard pressed to realize any performance difference between tables with less than 500 or 1000 records that is indexed vs. one that is not indexed. That is, for small tables building an index on the fly is a very fast process.
SQL Server manages TEMP tables using the TEMPDB database. When you create a TEMP table there is some additional work that has to be done to establish the table in the database, along with additional work needed to modify and delete the table.
Many developers disdain TEMP tables and recommend that you avoid them because they feel that they are a drag on performance. They recommend that you use permanent tables or those derived from completed queries or stored procedures, or you create a UNION statement to serve in their place. However, there are instances where you might want to avoid a cursor or pass a recordset.
Barrie Sosinsky is president of consulting company Sosinsky and Associates (Medfield MA). He has written extensively on a variety of computer topics. His company specializes in custom software (database and Web related), training and technical documentation.