Temp Tables and Table Variables

Posted March 30, 2007

Quick Comparison

	Temp Tables	Table Variables

I/O	High	N/A to Low
Indexing	Explicit Allowed	Explicit Not Allowed
Constraints	Allowed	Allowed
Declaration	Explicit/Implicit	Explicit Only
Reuse	Within Session	Within Batch Scope
Speed	Medium	High

The primary situation where using temp tables is preferred is when working with huge data sets. In almost every other case, the use of table variables is preferred.

Comparing Usage

Use the Query Analyzer or Management Studio to run the following commands. Run them one after another and observe the output.

declare @tableVar table ( f1 int, f2 varchar(10) ) ;

create table #tableVar ( f1 int, f2 varchar(10) ) ;

select * from [tempdb]..[sysobjects] where [name] like '%TableVar%' ;

From this, we find that…

Temporary tables have physical representation in the database and carries with it all the baggage related to transaction isolation, locking and logging.
Use temporary tables in a stored procedure and you will end up having as many copies of the table as the number of users who run the procedure. Scalability then becomes a concern.
To identify copies of the table between different users, SQL Server suffixes the name of the table with a numeric value. Somewhere within the Microsoft documentation, it says that if there are two tables with the same name, it is not guaranteed against which table the query will be resolved. Data integrity then becomes a concern.

Table variables could result in fewer recompilations than when temporary tables are used.