SQL - Temporary Tables in SQL Server

Temporary Tables in SQL Server

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