Programmer Collection: [MSSQL] Comparing Table Variables with Temporary Tables

By Wayne Sheffield, 2012/06/08 (first published: 2009/06/10)

There is a lot of confusion about what Table Variables are (and what they aren’t), and in how they compare to Temporary Tables. While there are a lot of articles on the internet about them, I haven’t found any that are complete. In this article, we’ll look into exactly what each are (and aren’t), and while we’re at it investigate some of the myths dealing with them, and see how accurate they are.

Table Variables

Table variables were first introduced in SQL 2000. So, what exactly is a table variable? Microsoft defines it in BOL (Declare @local_variable) as a variable of type table. Its definition includes column definitions, names, data types, and constraints. The only constraint types allowed are PRIMARY KEY, UNIQUE, NULL, and CHECK (FOREIGN KEY constraints are not allowed). The table definition is a subset of information used to define a table in the CREATE TABLE statement. They are created with the DECLARE @local_variable statement.

From Reference 1:

They have a well defined scope (the current batch of statements, but not any called procedures or functions). They are automatically cleared at the end of that batch.
Reference 6 discusses several reasons why temporary tables will force a recompilation of a stored procedure in the "Recompilations Due to Certain Temporary Table Operations" section. These reasons do not apply to table variables; therefore they can result in fewer recompilations of a stored procedure as compared to temporary tables.
Transactions against table variables last only for the duration of the update, therefore they require less locking and logging resources.
Since table variables have such a limited scope, and since they are not part of the persistent database, transaction rollbacks do not affect them.

A table variable can be used, within its scope, as you would any other table. Specifically, it can be used as a table or table expression anywhere they can be used in select, update, insert and delete statements. They cannot be used in a “SELECT select_list INTO table_variable” statement, and in SQL 2000 they cannot be used in an “INSERT INTO table_variable EXEC stored_procedure” statement.

What you can’t do with table variables:

Although a table variable is a variable, it cannot be assigned to another table variable.
Check constraints, default values, and computed columns in the table definition cannot reference user-defined functions.
You cannot create a named constraint.
You cannot truncate a table variable.
You cannot insert explicit values into an identity column (the table variable does not support the SET IDENTITY_INSERT ON).

Temporary Tables

Before we dig into temporary tables, we first need to talk about sessions. A session is simply a connection to the database engine. In SQL Server Management Studio (SSMS), each open query window has its own connection to the database engine. An application can have one or more connections to the database engine; additionally the application may leave a connection open throughout the application, or it may constantly close the connection and establish a new connection when necessary.

So, what is a temporary table? In BOL (CREATE TABLE), we find that a temporary table has the same physical makeup as any other table created with the CREATE TABLE statement, except that:

Its name is limited to 116 characters. This is because the database engine must be able to identify different temporary tables created by different sessions at the same time. To do so, it internally appends a numeric suffix to the end of the name.
Local temporary tables (created with a single “#”) are visible in the current connection, from the point the table is created and inside nested stored procedures. Local temporary tables will be dropped when:
a. Explicitly dropped using DROP TABLE.
b.If it is a local temporary table and it was created inside a stored procedure, it will be dropped when the stored procedure is finished.
c. All other local temporary tables will be dropped at the end of the current session.
Global temporary tables (created with a double “##”) are visible to all sessions. You should always check for existence of the global temporary table before creating it… if it already exists, then you will get a duplicate object error.
a. Global temporary tables are dropped when the session that created it ends, and all other sessions have stopped referencing it.
b. This referencing is performed at the statement level. For an example of how this works:
     i. Open up a new query window, and run the command

         create table ##temp (RowID int)

     ii. Open up a second query window, and run the following commands which builds an endless loop that selects from the global temp table every 5 seconds:

        while 1=1
        begin
           select * from ##temp
           waitfor delay '00:00:05'
        end

     iii. Once the statements in the second window are running, go back to the first query window and close it.
     iv. The next time the loop tries to reference the global temp table, it will generate an error.
Temporary tables cannot be partitioned.
Foreign Key constraints cannot be applied to a temporary table.
Columns cannot be defined with user-defined data types (UDDT) that are not created in tempdb; you must use the native data types. (UDDTs are specific to the database, and temporary tables belong to tempdb.) Since the tempdb database is recreated each time SQL Server starts, you could use a startup stored procedure to add the UDDT to tempdb. Alternatively, since the model database is the baseline that all databases are created from, you could add the UDDT to the model database and it will be incorporated into tempdb when it is created.
Columns of the XML type cannot be defined with an xml collection unless the collection has been added to tempdb.

Temporary tables can be created with the CREATE TABLE statement, or by selecting into a table using the “SELECT INTO #table” syntax. You can use the “INSERT INTO #table EXEC stored_procedure” statement with a temporary table.

Temporary tables can have named constraints, indexes, etc. However, if two users are running the same procedure at the same time, the second one will get an error: “There is already an object named ‘’ in the database”. You will need to build the object without a name, thus creating a system-assigned name which will be unique in the tempdb database.

Reference 6 talks about many different ways that will cause temporary tables to cause stored procedure recompilations, and how to avoid / minimize them.

Myths

So, now that we know what these table types are, let’s investigate a few myths about them.

Myth #1: Table variables reside in memory only.
And the closely related Myth #2: Temporary tables are physical only.
Both of these are false. In Reference 1, section Q4, we find that table variables are created in the tempdb database, since the table variable could hold more data than could fit into memory. Additionally, we find that both table variables and temporary tables will be created and processed in memory if enough memory is available. However, they can both be flushed to disk at anytime.

Would you like to see some proof of this? Try this (this code works with SQL 2000 – 2008):

 -- make a list of all of the user tables currently active in the
 -- TempDB database
 if object_id('tempdb..#tempTables') isnot null droptable #tempTables
 select name into #tempTables from tempdb..sysobjectswhere type ='U'
 -- prove that even this new temporary table is in the list.
 -- Note the suffix at the end of it to uniquely identify the table across sessions.
 select * from #tempTables where name like '#tempTables%'
 GO
 -- create a table variable
 declare@MyTableVariable table (RowID int)
 -- show all of the new user tables in the TempDB database.
 select name from tempdb..sysobjects
  where type ='U' and name notin (select name from #tempTables)
 GO

(While the above code works for 2000 – 2008, the sysobjects view has been depreciated and will be removed from a future version of SQL Server. For 2005 and 2008, you should use the system view sys.tables instead.)

There have been several wrong ways that others have posted to “prove” that temporary tables reside completely in memory. I’d like to point out one of those:
Note that the name that is returned for the table variable is a system-assigned name. The first character of the table variable name (@) is not a letter, so it is not a valid identifier name. Therefore, the system creates a system-assigned name for use in tempdb. This means that looking for the name of your table variable in sysobjects or sys.tables will fail.

I’d also like to point out the proper way to check for temporary tables. Above, I checked for the existence of the temporary table by looking for its object_id. This is the correct way. I have seen people perform this query:

 select * from sysobjects where name like'#tempTables%'

While this command appears to work, it has multi-user issues. Open two query windows (which are two different connections). In the first create the temporary table, and in the second perform the above statement. This statement will return a row for the temporary table created in the first connection. If you then try to do anything with that table, you will get an error. The error is because the temporary table is not in scope for your session.

Myth #3: Table variables cannot have any indexes.
This myth is also false. It is true that once a table variable is created, you cannot perform any DDL statements against it, including a CREATE INDEX statement. However, you can create indexes on the table variable that are associated with a PRIMARY KEY or UNIQUE constraint if you define those constraints as part of the table definition. For example:
declare @MyTableVariable table (RowID intPRIMARY KEY CLUSTERED)

This will create a table variable with a clustered primary key on the RowID column. Since primary keys have an associated index, a system-named index will be created on the RowID column.

This next example shows how you can build a unique constraint against a column, and how you can build a primary key against multiple columns:

 declare @temp TABLE (
   RowID int NOT NULL,
   ColA int NOT NULL,
   ColB char(1)UNIQUE,
   PRIMARY KEY CLUSTERED(RowID, ColA))

What other differences are there?

SQL cannot build statistics against a table variable, like it can against temporary tables. This means that the execution plan for table variables will always treat the table as if it has 1 row. Obviously, this means that it might not use the best execution plan. Note that the estimated execution plan for both table variables and temporary tables will show 1 row; however the actual execution plan for temporary tables will change once SQL has recompiled the stored procedure (see Reference 1, Q2). Also note that if a temporary table doesn’t exist, you will get an “Invalid object name ‘’” error when creating the estimated execution plan.
As mentioned previously, you cannot perform any DDL statements against a table variable. For instance, you might have a need to populate a table, and then add an index or column. In this case, you will need to use a temporary table.
With table variables, you cannot select into a table variable using the SELECT INTO syntax. As demonstrated in the code above, you can do this with a temporary table.
With SQL 2008, you can pass a table variable to a procedure if you have defined a user-defined table type and this is the type for the parameter. You cannot do this with temporary tables, or with table variables in SQL 2000 or SQL 2005 (see Reference 5).
Scope: table variables are only visible while the current batch of statements is running, and they are not visible to any nested procedures. Local temporary tables are visible to the current session once created, including nested procedures; however they will not be visible to parent procedures. Global temporary tables are visible to all sessions until dropped and all other sessions have stopped using them.
Collation: table variables use the collation of the current database. Temporary tables use the collation of the tempdb database. If they are not compatible, then you will need to specify the collation to use in either the queries or the table definition. (Reference 7, Table Variables and Temporary Tables)
If you want to use a table variable in dynamic SQL, you must define the table variable in the dynamic SQL code. A temporary table that was created before calling the dynamic SQL can be used inside the dynamic SQL.

So, what should I use?

recommends

Summary

Feature	Table Variables	Temporary Tables
Scope	Current batch	Current session, nested stored procedures. Global: all sessions.
Usage	UDFs, Stored Procedures, Triggers, Batches.	Stored Procedures, Triggers, Batches.
Creation	DECLARE statement only.	CREATE TABLE statement. SELECT INTO statement.
Table name	Maximum 128 characters.	Maximum 116 characters.
Column data types	Can use user-defined data types. Can use XML collections.	User-defined data types and XML collections must be in tempdb to use.
Collation	String columns inherit collation from current database.	String columns inherit collation from tempdb database.
Indexes	Can only have indexes that are automatically created with PRIMARY KEY & UNIQUE constraints as part of the DECLARE statement.	Indexes can be added after the table has been created.
Constraints	PRIMARY KEY, UNIQUE, NULL, CHECK, but they must be incorporated with the creation of the table in the DECLARE statement. FOREIGN KEY not allowed.	PRIMARY KEY, UNIQUE, NULL, CHECK. Can be part of the CREATE TABLE statement, or can be added after the table has been created. FOREIGN KEY not allowed.
Post-creation DDL (indexes, columns)	Statements are not allowed.	Statements are allowed.
Data insertion	INSERT statement (SQL 2000: cannot use INSERT/EXEC).	INSERT statement, including INSERT/EXEC. SELECT INTO statement.
Insert explicit values into identity columns (SET IDENTITY_INSERT).	The SET IDENTITY_INSERT statement is not supported.	The SET IDENTITY_INSERT statement is supported.
Truncate table	Not allowed.	Allowed.
Destruction	Automatically at the end of the batch.	Explicitly with DROP TABLE statement. Automatically when session ends. (Global: also when other sessions have no statements using table.)
Transactions	Last only for length of update against the table variable. Uses less than temporary tables.	Last for the length of the transaction. Uses more than table variables.
Stored procedure recompilations	Not applicable.	Creating temp table and data inserts cause procedure recompilations.
Rollbacks	Not affected (Data not rolled back).	Affected (Data is rolled back).
Statistics	Optimizer cannot create any statistics on columns, so it treats table variable has having 1 record when creating execution plans.	Optimizer can create statistics on columns. Uses actual row count for generation execution plan.
Pass to stored procedures	SQL 2008 only, with predefined user-defined table type.	Not allowed to pass, but they are still in scope to nested procedures.
Explicitly named objects (indexes, constraints).	Not allowed.	Allowed, but be aware of multi-user issues.
Dynamic SQL	Must declare table variable inside the dynamic SQL.	Can use temporary tables created prior to calling the dynamic sql.