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:

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.

2) 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.

3) Transactions against table variables last only for the duration of the update, therefore they require less locking and logging resources.

4) 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:

1. Although a table variable is a variable, it cannot be assigned to another table variable.
2. Check constraints, default values, and computed columns in the table definition cannot reference user-defined functions.
3. You cannot create a named constraint.
4. You cannot truncate a table variable.
5. 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:

1) 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.

2) 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.

3) 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.

4) Temporary tables cannot be partitioned.

5) Foreign Key constraints cannot be applied to a temporary table.

6) 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.

7) 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 <select_list> 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 ‘<objectname>’ 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?

1) 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 ‘<tablename>’” error when creating the estimated execution plan.

2) 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.

3) With table variables, you cannot select into a table variable using the SELECT <columnlist> INTO <table> syntax. As demonstrated in the code above, you can do this with a temporary table.

4) 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).

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.

6) 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)

7) 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?

Microsoft recommends using table variables (in Reference 4). If the number of rows that you are going to be inserting into the table is very small, then use a table variable. Most of the “internet gurus” will tell you to have less than 100 records as a guideline, as this is where the lack of statistics start to mess up a query – but they will also tell you to test your specific needs against both methods. Some people will only use table variables within user-defined table functions (which require them). If you can use an index from either a PRIMARY KEY or UNIQUE constraint on a table variable, then you could get excellent performance from table variables that contain tens of thousands of records. This is primarily true if you don’t have to join the table variable to another table. When you start joining the table variable to other tables, then the cardinality error frequently results in a bad execution plan, which gets worse the more complex the query is, and the larger the row count is.

To illustrate this, see the attachment to this article. It creates both a temporary table and a table variable, and populates each of these tables with the contents of the AdventureWorks.Sales.SalesOrderDetail table. In order to get a large data size (> 1 million rows), I perform 10 inserts on each table, with each insert being in a different order. I then perform an identical query on each table, joining up against the AdventureWorks.Sales.SalesOrderHeader table and filter by the ModifiedDate field. If you examine the output statistics, you will see a remarkable difference in the number of logical reads on the joined table between the select with the temporary table versus the select with the table variable. The same data, running the same query, is producing very different performance results. Time wise (on my system) it consistently takes the table variable over 50 seconds, while the temporary table takes under 8 seconds.

If your process requires you to perform DDL statements on the table after creation, then you will need to use a temporary table.

Temp Tables and Table Variables have overlapping purposes, so there's no real concrete answer to which you should use. For any given situation, you'll find the optimum only by examining the advantages, disadvantages, and characteristics of each and by doing some simple performance testing. The side-by-side comparison in the following "Summary" section should make the first part of that a bit easier to accomplish.

Summary

References:

1) INF: Frequently Asked Questions - SQL Server 2000 - Table Variables

2) T-SQL BOL (SQL 2000), table data type

3) T-SQL BOL (SQL 2008), Declare @local_variable

4) T-SQL BOL (SQL 2008), CREATE TABLE

5) Table-Valued Parameters (Database Engine)

6) Troubleshooting stored procedure recompilation

7) Local Temporary Tables and Table Variables

8) Startup stored procedure

9) Data Definition Language (DDL)

Other articles worth reading:

1) Things You Didn’t Know About Temp Tables and Table Variables

Acknowledgements:

I would like to acknowledge and thank Jeff Moden, Gail Shaw, Gus Gwynne and Lynn Pettis for their review and the constructive feedback that they gave for this article.