MS SQL Server: Temporary Table and Table Variable Performance

Temporary tables and table variables in SQL Server actually perform quite differently under different circumstances.

Temporary tables are decelared as: CREATE Table #TableName ( id INT )

Table variables are declared as: DECLARE @TableName TABLE ( id INT )

Temporary tables are transaction bound whereas table variables are not. This means that if a transaction in which a temporary table or a table variable are instantiated and populated is rolled back, the temporary table will still exist while the table variable will no longer exist.

Along the lines of rolling back transactions, if a temporary table and table variable are declared outside of the transaction, but populated within the transaction that is later rolled back, the temporary table would not have the data it was populated with. On the other hand the table variable would retain the data that was added during the rolled back transaction.

The bottom line is that transaction logs are not kept for table variables but they are for temporary tables, so table variables are separate from the transaction mechanism. Since table variables are not part of the transactions mechanism, or logging, or locking they tend to be lighter weight components that can perform better than temporary tables.

Locking, in fact, is another performance hit that affects temporary tables but not table variables. Lock management can take significant server resources when the functionality may not even be required or desired.

Another interesting point that affects performance is that pre-compilation of stored procedures. Any procedure containing a temporary table cannot be pre-compiled. On the other hand table variables do not pose this problem, so stored procedures that use table variables can be pre-compiled. This ends up potentially being a very significant performance boost to stored procedures that use table variables.

Table variables also differ from temporary tables in terms of their scope. Temporary tables have a greater scope than table variables and unlike table variables, are visible to sub-levels of stored procedures.

In general table variables are lighter weight components than temporary tables. Although table variables are initially memory resident, pages belonging to a table variable can be pushed to tempdb if the set of data becomes overly large. At that point the speed advantage of table variables is lost and temporary tables will actually out-perform an overburdened table variable.

Further, table variables have the problem that the SQL Server optimizer does not create distribution statistics, so one runs the risk of matching bad query plans with large amounts of data which will result in significant I/O thrashing.

As a general rule of thumb, when using smaller sets of data, table variables will outperform temporary tables. However if the amount of data grows larger, then temporary tables will perform significantly better than table variables.

References:

• Temp Tables and Table Variables: When To Use What And Why – Part 2
• MSDN – Troubleshooting Insufficient Disk Space in tempdb
• OdeToCode.com – Table Variables In T-SQL 
• weblogs.sqlteam.com – Biggest difference between Temp tables and Table variables in Sql Server
• SQL Server Performance.com – Temporary Tables vs. Table Variables and Their Effect on SQL Server Performance