Systems Engineering and RDBMS

Replacing temp tables with table variables

Posted by decipherinfosys on February 2, 2007

If your application frequently creates temporary tables, consider using the table variable or a permanent table. You can declare a table variable to store a row set in memory. Table variables are cleaned up automatically at the end of the function, stored procedure, or batch that they are defined in. Many requests to create temporary tables may cause contention in both the tempdb database and in the system tables. Very large temporary tables are also problematic. If you find that you are creating many large temporary tables, you may want to consider a permanent table that can be truncated between uses.  Using temp tables is also not advisable since it can lead to re-compilation (in SQL 2005, there is statement level re-compilation but in SQL 2000, the stored procedure re-compilation can lead to high CPU utilization issues so if you use temp tables, read the Microsoft KB article to ensure that you are not running into re-compilation issues because of them).

Large table variables also use the tempdb database in a similar fashion like the temp tables use tempdb, so avoid large table variables also. Also, table variables are not considered by the optimizer when the optimizer generates execution plans and parallel queries.  However, for smaller data-sets, these are created in memory and can prove to be more beneficial than temp tables.

Every application is different – you should test temporary table and table variable usage for performance in your environment. Test with many users for scalability to determine the approach that is best for each situation. Also, be aware that there may be concurrency issues when there are many temporary tables and variables that are requesting resources in the tempdb database.

One Response to “Replacing temp tables with table variables”

  1. […] A possible replacement for temp tables is a table variable. We had blogged that before – you can read more on that here. […]

Sorry, the comment form is closed at this time.

%d bloggers like this: