SQL Server temp tables store data temporarily. You can perform the same operations—like SELECT, INSERT, DELETE, and UPDATE—on a temporary table as you would a regular SQL table.

Temp tables live in the tempdb database and are only visible during the duration of the connection. When you terminate the connection, SQL Server deletes the temp table. You can also explicitly delete it at any time.

Types of SQL Server Temp Tables

There are two types of SQL Server temp tables: local and global.

Local Temp Table

A local temp table is only visible to the connection that created it. When that connection ends, or the user disconnects from the SQL Server instance, a local temp table is automatically dropped.

To create a local temp table, use a single hash symbol (#) at the beginning of the table name with the CREATE TABLE statement. Here is the syntax.

        CREATE TABLE #TempTable (
   Column1 INT,
   Column2 VARCHAR(50)
);

    

For example, the following code creates a temporary table called TempCustomer with a name and email field.

        CREATE TABLE #TempCustomer (
   ID int NOT NULL PRIMARY KEY
   FullName VARCHAR(50),
   Email VARCHAR(50)
);

    

Global Temp Table

A global temp table is a temporary table visible to all connections and users. SQL Server will drop it when all connections and users that reference the table have disconnected.

To create a global temp table, prefix the table name with double hashes (##) and use the CREATE TABLE statement.

        CREATE TABLE ##TempTable (
   Column1 INT,
   Column2 VARCHAR(50)
);

    

The following code creates a global temp table named TempCustomer.

        CREATE TABLE ##TempCustomer (
   ID int NOT NULL PRIMARY KEY
   FullName VARCHAR(50),
   Email VARCHAR(50)
);

    

This code is similar to the example for the local temp table, just with two hash symbols instead of one. You can now use standard SQL commands to add or manipulate the data in the temp table.

How to Drop a Temp Table

The SQL Server instance automatically drops a temp table when all users referencing it have disconnected. As a best practice, you should always explicitly drop your temp tables to free up the tempdb memory.

To drop a temp table, use the DROP TABLE IF EXISTS statement followed by the name of the temp table.

Here’s how to drop the #TempCustomer table:

        DROP TABLE IF EXISTS #TempCustomer

    

Use the same code but swap the table name to drop a global temp table.

        DROP TABLE IF EXISTS  ##TempCustomer

    

Typical Uses of SQL Temp Tables

Temp tables are useful when you need to store intermediate results of complex queries that you need to process further. For example, when creating reports, you may need to create temporary tables to store query results from several databases. You can then generate the final report by running a query against the temporary tables.

Another scenario where temp tables are useful is when you need the query results from one table to run another query. You can store the results in a temp table and then reference it in your new query. Essentially, you are using the temp table as a work or buffer table to hold the data you need to perform a specific task.

Using Temp Tables in SQL Server

You can use SQL Server temp tables to store and process data temporarily. There are two types of temp tables, local and global. A local temp table is visible to the connection in which it’s created while a global temp table is visible across all the connections.

Temp tables are automatically destroyed when you disconnect from the server instance. However, to conserve memory, you should always drop them after use.