In SQL Server, a temporary table is a certain kind of table that exists until goes out of scope (unless it’s explicitly dropped).

This is different to a regular (persistent) table, where the regular table exists permanently in your database until you explicitly drop it.

Advantages of Temporary Tables

Temporary tables can be useful at times when you need to run multiple queries against a subset of a larger result set. You can use SELECT... INTO with a WHERE clause to put the subset of data into a temporary table. You can then run queries against that temporary table. This saves you from having to constantly filter data from the larger result set when making multiple queries.

Temporary tables can also be useful from a permissions perspective. If you need to work with data from a persistent table but you don’t have sufficient permissions to do all the operations you need, you can copy that data into a temporary table (assuming you have read access) and do whatever you need to do.

Temporary tables can also be used if you don’t have permission to create a (persistent) table in the current database.

Another handy aspect of temporary tables is that you can use them for running quick ad-hoc queries when running a test, demonstrating a concept, etc, without worrying about leaving a trail of persistent tables that you forgot to drop.

TempDB

Temporary tables are created in the TempDB database. This is a system database that stores temporary user objects such as temporary tables and indexes, temporary stored procedures, table variables, tables returned in table-valued functions, and cursors. It also stores internal objects created by the database engine, and version stores.

Any user can create temporary tables in TempDB. You don’t need to configure any special permissions for TempDB access.

Syntax

Temporary tables are created using the same syntax as regular tables. The difference is that temporary tables are prefixed with either one or two number signs (#):

• Local temporary tables are prefixed with a single number sign (eg, #table_name)
• Global temporary tables are prefixed with a double number sign (eg, ##table_name)

Example of creating a local temporary table:

CREATE TABLE #LocalTableName (
    col1 int PRIMARY KEY,
    col2 varchar(255)
);

Example of creating a global temporary table:

CREATE TABLE ##GlobalTableName (
    col1 int PRIMARY KEY,
    col2 varchar(255)
);

Local temporary tables are visible only in the current session, and global temporary tables are visible to all sessions.

How Long Does a Temporary Table Last?

Temporary tables are automatically dropped when they go out of scope.

You can also explicitly drop a temporary table by using DROP TABLE.

Exactly when a temporary table is automatically dropped depends on whether the temporary table is local or global, and whether or not it’s created in a stored procedure.

• Local: A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process that called the stored procedure that created the table.

  All other local temporary tables are dropped automatically at the end of the current session.

• Global: Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single T-SQL statement. This means that a global temporary table is dropped at the completion of the last T-SQL statement that was actively referencing the table when the creating session ended.

Schema

All temporary tables are created in the dbo schema. If you explicitly specify a different schema, it’s ignored.

Foreign Keys

FOREIGN KEY constraints are not enforced on temporary tables. If you specify a foreign key, the table is still created, just without a foreign key. A message will be returned advising you of this.