2006 SQL Server Temporary Tables Versus Table Variables
Temporary tables have long been an integral part of any complex SQL processing. If you have a long script or stored procedure, one which needs to extract or manipulate data using multiple queries and related statements, then being able to save results into temporary tables can dramatically improve performance (as well as readability and management).
SQL Server has supported temporary table use for any years, I first used them in SQL Server 6.5 (which were released in the mid 90s). Temporary tables are created and used just like any other tables, and then can be either local or global. Here is a simple code snippet that creates two temporary tables, one local and one global:
CREATE TABLE #MyTempTable
id INT PRIMARY KEY,
-- Create global temporary table
CREATE TABLE ##MyTempTable
id INT PRIMARY KEY,
-- Create local temporary table
The two code snippets look similar, the only difference is that one table is named #MyTempTable and the other is named ##MyTempTable. In SQL Server, a single # is used to refer to a local temporary table, and double # is used to refer to a global temporary table.
Once created, these tables can be used like any other tables. You can SELECT, INSERT, UPDATE and DELETE, you can use them in JOINs and subqueries, and more. You just have to be careful to use # (or ##) as part of the table name.
Oh, and you can also use SELECT INTO to create and populate a temporary table in one step.
You can also DROP temporary tables. Although SQL Server can automatically drop the temporary tables for you, too. Temporary tables are automatically dropped when the session that created them terminates (goes out of scope).
SQL Server 2000 added support for an alternative to temporary tables, the table variable. Table variables are similar to temporary tables in that they are intended for temporarily working with data. Here is how one is created:
DECLARE @MyTempTable TABLE
id INT PRIMARY KEY,
-- Create table variable
DECLARE is used to declare variables, and the variable is prefixed by @ (instead of #). In SQL Server, variables are always prefixed by @, and table variables are no different.
Once created, a table variable can also be used like a regular table. You can SELECT, you can INSERT, UPDATE, and DELETE, you can use the table variable in JOINs and subqueries, and more. And the table variable has some important benefits over temporary tables:
- Table variable access can be considerably faster than temporary table access.
- There is no need to DROP tables or perform cleanup, when the variable goes out of scope (the statement or stored procedure ends) the table variable is cleaned up just like any other variable.
- Table variables can be safer in a multi-user environment than temporary tables.
So, there are definitely benefits to using table variables. But is there any reason not to use them? Well, maybe. Table variables have some important restrictions that may impact whether or not you can use them:
- Tables variables are not available pre SQL Server 2000.
- Table variables must always be accessed using an alias (except in the FROM clause of course). If you want to refer to the table variable in a JOIN, for example, you must refer to is by its alias.
- Table variables do not support SELECT *, you must always explicitly list the columns you want.
- Table variables cannot be explicitly DROPped.
- Once created, table variable definitions cannot be changed.
- Tables variable do not support SELECT INTO syntax.
- There are fewer index options available for table variables, and this can impact performance.
This Microsoft FAQ explains some of the differences between temporary tables and table variables.
So yes, table variables do have some limitations that temporary tables do not. But, having said that, I was working on a rather complex stored procedure today, one that made extensive use of temporary tables. And in this project, replacing the temporary tables with table variables resulted in a very significant performance gain.
The bottom line is that both temporary tables are table variables are invaluable tools in your SQL Server toolbox, and you really should become familiar with both.
Note: Other DBMSs (including both Oracle and MySQL) also support temporary tables and table variables. Unfortunately, the syntax used to work with these tables (as well as the restrictions and differences between them) tends to differ significantly between DBMSs implementations.