SQL Server Temp Table

A temporary (temp) table in SQL Server is a special table that cannot be stored permanently on the database server. This table keeps a subset of data from a regular table and can be reused multiple times in a particular session. We cannot store this table in the memory. Since this table exists temporarily on the current database server, it will be deleted automatically when the current session ends, or the user terminates the database connection. We can get the temporary tables in the system database "tempdb."

Temporary tables are convenient when we have a bunch of records in a table and only need to deal with a small group of those records on a regular basis. In such cases, we can filter the data once and keep them in a temporary table rather than filtering the data multiple times to get the required data. After that, we can run our queries on that temporary table. Thus, the temporary table has a feature that enables us to achieve complex tasks quickly.

Creating Temporary Tables in SQL Server

We can create temporary tables in two ways:

1. SELECT INTO
2. CREATE TABLE Statements

Let us first create a table to demonstrate the temporary table in both ways. The following statement creates a table named 'Student' in the sample database:

We can verify the table using the SELECT statement. It displays the below table:

Temporary table using SELECT INTO Statement

The SELECT INTO statement is the simplest method to create a temporary table in SQL Server. We generally used this method to save intermediate results for repetitive use in the same session.

The following syntax illustrates how to create a temp table using the SELECT INTO statement:

The name of a temp table always starts with a hash symbol (#). The hash sign tells that the SQL Server is dealing with a temp table. For example, the below query creates a temp table in the tempdb database with the help of a SELECT INTO statement. We can use this table only in a session that creates a temporary table.

As we can see in the above statement, it creates a temp table named "#MaleStudents" with three columns (name, age, and gender) derived from the SELECT statements. It populated the data from the 'Student' table containing all the male student record's names, ages, and gender. We should not forget that the temp table name must have a hash (#) sign at the beginning of its name.

Once we execute the statement, we can see where the temporary table exists by accessing the SQL Server Management Studio using the following path: Object Explorer -> Databases -> System Databases-> tempdb -> Temporary Tables. Here, we get the temp table name along with the hash sign. See the below figure:

As we can see in the image, the temp table also contains a sequence number at the end of the name. It indicates a unique identifier that the SQL server creates for each temporary table to distinguish the temporary tables. It is created because more than one database connection can have the same name for multiple temporary tables.

Now, we can perform SQL operations on the temporary tables in the connection where it is created. For example, if we want to get the details of all the male students, then we can execute the query as follows:

This statement will fetch the following records:

Temporary Table using CREATE TABLE Statement

The CREATE TABLE statement is the second method to create a temporary table. The below statement will create a temp table named #FemaleStudents. If we want to create the same table (For example, #MaleStudents), it is required to change the current database to a new connection.

Here is the query to create a temp table using CREATE TABLE statement:

As we can see in the statement, it is similar to creating normal tables. The only difference is that the temporary table's name begins with a hash (#) sign.

Next, we will add records into this table, same as a regular table:

Now, we can query the data within the current session as below:

If we try to query the above table in another connection, SQL Server will through the following error message:

SQL Server through this error because the temporary tables can only be accessed inside the session that generated them.

Types of Temporary Table

SQL Server categorizes the temporary table into two types:

1. Local Temporary Table
2. Global Temporary Table

Local Temporary Table

Local temporary tables are stored in tempdb and are visible only to the session that created them. These tables are automatically destroyed when we close the connection from the instance of SQL Server. We can define the local temporary table name with the prefix of the hash (#) sign. The temp table can be created with the same name in multiple connections.

We can use the following syntax to create a local temporary table in SQL Server:

We have already seen the above temporary table examples that explain the local temporary table also.

Global Temporary Table

Global temporary tables are stored in tempdb and are visible to all users and all sessions. These tables are automatically destroyed when all users that are referencing the table disconnect the connection or when the last session that uses them has been completed. We can define the global temporary table name with the prefix of double hash (#) sign.

We can use the following syntax to create a global temporary table in SQL Server:

Example

The below example explains the creation of the global temporary table. Here, we have created a temp table named "##FemaleStudents" stored in the tempdb system database.

We can query this table as below:

How to drop temporary table?

SQL Server allows us to remove the temporary table in two ways:

1. Automatic
2. Manual

Automatic Deletion

SQL Server automatically drops the temporary table when we will close the connection that created them. SQL Server will delete a global temp table when the connection that created them is closed, and the queries referencing this table from other connections have been completed.

Manual Deletion

We can use the DROP TABLE statement to remove the temporary table manually from the connection in which they are created. The syntax is given below:

If we want to remove multiple temporary table, we need to provide comma-separated table names. See the below syntax:

Temporary Tables vs. Regular Table

The following comparison chart explains the main differences between temporary and regular tables: