Temp tables are a vital part of every T-SQL developer’s toolbox, but did you know that there are different types of temp tables? This article goes through the ways you can implement a temp table, as well as how they affect performance.

Regular temp tables

These are the ones you’re probably used to working with. A temp table is defined like any regular table, except they don’t use schemas, and they’re prefixed with a # sign. You can use all the standard DDL commands like CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, etc, that apply to regular tables.

SELECT *
INTO #aTempTable
FROM dbo.aRegularTable;

CREATE TABLE #anotherTempTable (
   column_a     int NOT NULL,
   column_b     int NOT NULL,
   column_c     numeric(12, 2) NULL
);

ALTER TABLE #anotherTempTable
   DROP COLUMN column_b;

Temp tables are stored in tempdb or in memory, depending on the amount of data and other server variables.

Temp tables are dropped when the context they were created in terminates, i.e. the stored procedure ends or the connection is closed. However, it is considered good practice to explicitly use DROP TABLE to drop temp tables at the end of a procedure or script.

Indexing a temp table

Temp tables are indexed just like regular tables. This means you can define primary key constraints and indexes, both clustered and non-clustered using ALTER TABLE and CREATE INDEX respectively.

CREATE UNIQUE CLUSTERED INDEX anotherIx
   ON #anotherTempTable (column_a);

This also means that SQL Server will automatically generate and maintain statistics on temp tables, which is a performance advantage when you have more than just a few rows in your table.

Global temp tables

Global temp tables are prefixed with ## (as opposed to # on temp tables) and are accessible from any connection on the server. This means that you can share a temp table between two processes, but it also means that the name of a global temp table must be unique for the server at any given time.

A global temp table is dropped when the context that created it is terminated.

Table variables

Table variables are defined with a DECLARE statement and their scope is that of any regular variable.

DECLARE @myTable TABLE (
   column_a     int NOT NULL,
   column_b     int NOT NULL,
   column_c     numeric(12, 2) NULL
)

INSERT INTO @myTable (column_a, column_b, column_c)
VALUES (1, 2, 3.45)

There are quite a few key differences between table variables and temp tables. Table variables do not use locking and transaction logging like temp tables. This means that they are always immediately committed, but it also means that they don’t have to do with the overhead of locking and transaction handling.

Because table variables are declared as variables and not as tables, you cannot use DDL statements like CREATE INDEX or ALTER TABLE on them.

Table variables are a poor choice for large volumes of data, because they do not have statistics and they cannot be used in parallel execution plans. Also, because there are no table/index statistics, the query optimizer will always assume that a table variable has 1 row, which may lead to a very poor query plan if you have lots of data in it.

However, because statistics are not maintained and you cannot execute DDL statements against a table variable, they will not cause your procedure to recompile, which may be advantageous in some situations.

In user-defined functions (scalar and table valued), you cannot use temp tables, so here you need to go with table variables.

Indexing a table variable

Since you cannot use DDL statements on a variable, the only way to index a table variable is to place constraints on it in the declaration. Here’s an example of a table variable with a primary key (which is created as a clustered index on the table).

DECLARE @myTable TABLE (
   column_a     int NOT NULL,
   column_b     int NOT NULL,
   column_c     numeric(12, 2) NULL,
   PRIMARY KEY CLUSTERED (column_a)
)

Table types

As of SQL Server 2008, a user-defined type can be of a table type. This means that you can define a UDT as a table, with columns and primary key, that you can later on use to declare table variables without having to specify the columns.

Here’s the user-defined table type:

CREATE TYPE dbo.employeeRoster AS TABLE (
    employeeID    int NOT NULL,
    firstName    varchar(100) NOT NULL,
    lastName    varchar(100) NOT NULL,
    PRIMARY KEY CLUSTERED (employeeID)
);

And here’s how you can use it:

DECLARE @roster employeeRoster;

INSERT INTO @roster VALUES (1, 'A.', 'Smith');

One of the really cool applications for this is passing table valued arguments to functions and procedures! Instead of building cumbersome XML structures, passing the XML to the procedure, and then parsing it back into a table, you can just pass a table type variable.

CREATE PROCEDURE dbo.sp_listEmployeeNames
    @roster   employeeRoster READONLY
AS

SELECT firstName+' '+lastName
FROM @roster;

And here’s the corresponding procedure call:

DECLARE @roster employeeRoster;

INSERT INTO @roster VALUES (1, 'A.', 'Smith');

EXECUTE dbo.sp_listEmployeeNames @roster;

Conclusion

As a rule of thumb, if your data has more than a 100 rows, or you are going to use the table in a large query that requires good performance, go with a temp table. Otherwise, use the table variable, which has a lower overhead.

If you’re working with a function or want to pass table valued arguments to a procedure, you’ll need to use a table variable.