SQL Server Temp Table Performance Tuning

In general, temp tables should be avoided, if possible. Because they are created in the tempdb database, they create additional overhead for SQL Server, slowing overall performance. As an alternative to temp tables, consider the following alternatives:

• Rewrite your code so that the action you need completed can be done using a standard query or stored procedure, without using a temp table.
• Use a derived table.
• Consider using a correlated sub-query.
• Use a permanent table instead.
• Use a UNION statement to mimic a temp table.

[7.0, 2000, 2005] Updated 3-6-2006

*****

One legitimate use for temp tables is to use them to pass recordsets from a nested stored procedure to a calling stored procedure. This is the only way to pass recordsets from one stored procedure to another. When you do this, follow the other tips on this web site to maximize temp table performance. [6.5, 7.0, 2000, 2005] Updated 3-6-2006

*****

One legitimate reason you might want to consider using a temp table is to avoid having to use a cursor. SQL Server cursors have huge overhead and slow SQL Server's performance. One alternative of using a cursor is to use a temp table instead. In almost all cases, using a temp table over a cursor will produce less overhead and better performance. Of course, if you do not have to use a temp table, and find another way to get away from using a cursor, so much the better. [7.0, 2000, 2005] Updated 3-6-2006

*****

SQL Server 2000,2005 offers a data type called "table." Its main purpose is for the temporary storage of a set of rows. A variable, of type "table," behaves as if it is a local variable. And like local variables, it has a limited scope, which is within the batch, function, or stored procedure in which it was declared. In most cases, a table variable can be used like a normal table. SELECT, INSERT, UPDATE, and DELETE can all be made against a table variable.

If you need a temporary table in your Transact-SQL code, consider using a table variable instead of creating a conventional temporary table instead. Table variables are created and manipulated in memory instead of the tempdb database, making them faster in some cases. But not in all cases. Because of this, you will need to test both options to determine which works best for you under your particular circumstances.

In addition, table variables found in stored procedures result in fewer compilations (than when using temporary tables), and transactions using table variables only last as long as the duration of an update on the table variable, requiring less locking and logging resources. [2000, 2005] Updated 3-6-2006

*****

If you have no choice but to use a temp table, you can help to optimize its performance by taking one or more of the following steps:

• Only include the columns and rows you actually need in the table, no more.
• Do not use SELECT INTO to create your temp table, as it places locks on system objects. Instead, create the table using standard Transact-SQL DDL statements, and then use INSERT INTO to populate the table.
• Consider using clustered and non-clustered indexes on your temp tables, especially for very large temp tables. You will have to test to see if indexes help or hurt overall performance.
• When you are done with your temp table, delete it to free up tempdb resources. Do not wait for the table to be automatically deleted when the connection is ended.
• If the tempdb database is not already on its own dedicated disk or array, consider taking this step. By isolating the tempdb database on its own disk, disk contention is reduced and performance is increased. Since the tempdb database does not need to be backed up, it can be located on a single disk, or for best performance on a RAID 0 array.

See article on temp tables. [7.0, 2000, 2005] Updated 3-6-2006

*****

If you have to use a temp table, do not create it from within a transaction. If you do, then it will lock some system tables (syscolumns, sysindexes, and syscomments) and prevent others from executing the same query, greatly hurting concurrency and performance. In effect, this turns your application into a single-user application.

To avoid this problem, create the temporary table before the transaction. This way, the system tables are not locked and multiple users will have the ability to run this same query at the same time, helping concurrency and performance. Contributed by Gaurav Bindlish. [7.0, 2000, 2005] Added 2-24-2003