Ben Forta

AdobeStock_455007340

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 local temporary table
CREATE TABLE #MyTempTable
(
id INT PRIMARY KEY,
firstName CHAR(50),
lastName CHAR(50)
)
-- Create global temporary table
CREATE TABLE ##MyTempTable
(
id INT PRIMARY KEY,
firstName CHAR(50),
lastName CHAR(50)
)
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:
-- Create table variable
DECLARE @MyTempTable TABLE
(
id INT PRIMARY KEY,
firstName CHAR(50),
lastName CHAR(50)
)
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.

10 responses to “SQL Server Temporary Tables Versus Table Variables”

  1. todd sharp Avatar
    todd sharp

    Not that I’m one to question you, but are you sure about the following statements? I’m pretty sure I’ve done both of these things (though the select * is just done when testing)
    "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."
    Great post though – I’m a big fan of Table variables and I use them all the time!

    Reply
  2. Robert Burghardt Avatar
    Robert Burghardt

    SELECT * FROM @tempTable works. I have also used these compared to #temp tables and the table variables do perform a bit faster.
    One correction on the SQL example above. In order to create a temp variable table, use the statement "DECLARE @tempTable TABLE( field listing…)" instead of CREATE TABLE

    Reply
  3. Ben Forta Avatar
    Ben Forta

    Todd, yep, you are correct. SELECT * used to not work, but it does now (I just tested in in SQL Server 2005), thanks for the correction. However, an alias being required is still the case.
    Robert, ouch, good catch. I’ll correct that.
    — Ben

    Reply
  4. Tuyen Avatar
    Tuyen

    I don’t understand how table variable is FASTER than temp table while Non-clustered indexes cannot be created on table variables? (that’s the only reason why I personally don’t use table variable)

    Reply
  5. Ben Nadel Avatar
    Ben Nadel

    Whether SELECT * works or not…. I hope none of us are actually using SELECT * 🙂 There is nothing performant or self-documenting about such a silly statement.
    As far as SELECT INTO, they support INERT INTO which is pretty sweet. I am not sure exactly what SELECT INTO does, but it sure sounds similar.
    And, this is an opinion type thing, but honestly, half of the listed "limitations" of a table variable seem more like SQL server forcing you to use best practices more than they are limiting you. Using aliases, not using select *, fully defining a table…. these just all seem like things you *should* be doing, not that you are limited to doing. (just my opinion).
    That nice to know that this performs faster. Great post!

    Reply
  6. Dr Evil Avatar
    Dr Evil

    is there a way to …
    DECLARE @T1 TABLE OF dbo.ARealTable ?
    …without listing every column when you want a table variable based on an existing table?
    it might be best practice, but T-SQL doesn’t seem a patch on Oracle PL/SQL forthis type of thing.

    Reply
  7. Subhadip Avatar
    Subhadip

    The select * #temp…. stetement is not working in SQL Server 2005,it says,you dont have permission to drop the table,the same code works fine in 2000.What to do?

    Reply
  8. Eric Mamet Avatar
    Eric Mamet

    This article does give a different view http://www.sqlmag.com/Article/ArticleID/40614/sql_server_40614.html (look for TIP: TABLE VARIABLES VS. TEMPORARY TABLES)
    Also,
    – Temporary tables don’t need to be cleaned up either. They are when getting out of scope
    – why would temptorary yables not be safe in multi user environments???
    Eric

    Reply
  9. pradeep Avatar
    pradeep

    Select into works (in sql server 2005)
    Declare @MyTable Table
    (
    ID int not null ,
    datastoreName nvarchar(200) not null,
    DateCreated datetime,
    UserCreated nvarchar(200)
    )
    insert into @MyTable
    select 1 , ‘DS1’,getdate() , ‘ADMIN1’
    union all
    select 2 , ‘DS2’,getdate() , ‘ADMIN2’
    union all
    select 3 , ‘DS3’,getdate() , ‘ADMIN3’
    select * into temp from @myTable
    u will get 3 rows in ‘temp’ table

    Reply
  10. David Walker Avatar
    David Walker

    You do NOT have to refer to table variables using an alias. I see this error written all over the place. Try this:
    Select * From @TableVar Inner Join RegularTable
    On [@TableVar].Column1 = RegularTable.ColumnA
    And [@TableVar].Column2 = RegularTable.ColumnB
    The problem is the T-SQL parser doesn’t understand that @TableVar is a table and not a scalar.

    Reply

Leave a Reply