Temporary Tables

Besides table variables, there are temporary tables we can utilize in our complex queries to hold intermediate data. Unlike table variables that exist only within the scope of a batch, temporary tables are accessible throughout the whole session when there’s a connection to the MS SQL Server. Temporary tables can be either local or global.

Temporary table types

There are two types of temporary tables in MS SQL Server.

Local temporary tables

Local temporary tables are accessible only from the session they are created in. Users or applications connected to the MS SQL Server are not able to access the temporary tables created in other sessions. They are automatically dropped when the session closes.

Syntax

Local temporary tables are created similarly to regular tables with one notable difference. We put # in front of the table name:

CREATE TABLE #TempData
(
    Id INT PRIMARY KEY IDENTITY(1, 1),
    Info NVARCHAR(100) NOT NULL
);

To delete a local temporary table before the session is closed, we can use the DROP TABLE command, just like we do with a regular table:

DROP TABLE #TempData;

Usage

Like table variables, local temporary tables can be used within stored procedures or other complex queries. They support all the commands we expect from a table: