Introduction to Temporary Tables in SQL Server

A temporary table in SQL Server, as the name suggests, is a database table that exists temporarily on the database server. A temporary table stores a subset of data from a normal table for a certain period of time.

Temporary tables are particularly useful when you have a large number of records in a table and you repeatedly need to interact with a small subset of those records. In such cases instead of filtering the data again and again to fetch the subset, you can filter the data once and store it in a temporary table. You can then execute your queries on that temporary table. Temporary tables are stored inside “tempdb” which is a system database. Let’s take a look at how you can use a temporary data in a simple scenario.

Preparing the Data

Let’s first prepare some dummy data. We will use this data to create temporary tables.

Run the following script on your database server.

CREATE DATABASE schooldb

CREATE TABLE student
(
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    gender VARCHAR(50) NOT NULL,
    age INT NOT NULL,
    total_score INT NOT NULL,
    
 )


INSERT INTO student 

VALUES (1, 'Jolly', 'Female', 20, 500), 
(2, 'Jon', 'Male', 22, 545), 
(3, 'Sara', 'Female', 25, 600), 
(4, 'Laura', 'Female', 18, 400), 
(5, 'Alan', 'Male', 20, 500), 
(6, 'Kate', 'Female', 22, 500), 
(7, 'Joseph', 'Male', 18, 643), 
(8, 'Mice', 'Male', 23, 543), 
(9, 'Wise', 'Male', 21, 499), 
(10, 'Elis', 'Female', 27, 400);

The above SQL script creates a database ‘schooldb’. In this database, a table called ‘student’ is created and some dummy data added into the table.

Creating A Temporary Table

There are two methods of creating temporary tables.

Method 1

The simplest way of creating a temporary table is by using an INTO statement within a SELECT query. Let’s create a temporary table that contains the name, age, and gender of all the male student records from the student table.

USE schooldb;

SELECT name, age, gender
INTO #MaleStudents
FROM student
WHERE gender = 'Male'

Take a look at the above query. Here we created a temporary table “#MaleStudents” which stores the name, age, and gender of all the male student records from student table. To define a temporary table, we use the INTO statement after the SELECT statement. The name of a temporary table must start with a hash (#).

Now, to see where this table exists; go to “Object Explorer -> Databases -> System Databases-> tempdb -> Temporary Tables”. You will see your temporary table name along with the identifier. Take a look at the following figure:

You must be wondering about the “000000000006” at the end of the table name. This is a unique identifier. Multiple database connections can create temporary tables with the same name, therefore to differentiate between the temporary tables created by different connections, the database server automatically appends this unique identifier at the end.

You can perform operations on the temporary table via the same connection that created it. Therefore, in the same query window that created the “#MaleStudents” table, execute the following query.

SELECT * FROM #MaleStudents

Since, the #MaleStudents table contains the name, age, and gender of all the male students. The above query will fetch following results.

[table id=15 /]

To create a new connection you can simply open a new query window in “SQL Server Management Studio”. Now, keep the previous connection open and create another “MaleStudents” table using method 2 in a new query window (new connection).

Method 2

The second method is similar to creating normal tables. Take a look at the following query. Here again, we shall create #MaleStudents temporary table. Remember, this query must be executed by a new connection.

USE schooldb;

CREATE TABLE #MaleStudents
(
	name VARCHAR(50),
	age int,
	gender VARCHAR (50)

)

INSERT INTO #MaleStudents
SELECT name, age, gender
FROM student
WHERE gender = 'Male'

Now, if you execute the above query, you should see two #MaleStudents temporary tables with different unique identifiers inside the tempdb. This is because these two tables have been created by two different connections. Take a look at the following screenshot.

Global Temporary Tables

It is pertinent to mention here that, a temporary table is only accessible to the connection that created that temporary table. It is not accessible to other connections. However, we can create temporary tables that are accessible to all the open connections. Such temporary tables are called global temporary tables. The name of the global temporary table starts with a double hash symbol (##). Let’s create a global temporary table that contains records of all female students from the student table.

USE schooldb;

SELECT name, age, gender
INTO ##FemaleStudents
FROM student
WHERE gender = 'Female'

Now, you can access the ##FemaleStudents table from any of the open connections.

Deleting a Temporary Table

There are two ways to delete temporary tables in SQL Server: Automatic Deletion and Manual Deletion.

Automatic Deletion

A temporary table is automatically deleted when the connection that created the table is closed. Alternatively, when you close the query window that created the temporary table, without saving the changes the table will be closed. If a connection is executing some queries on the global table then those queries have to be completed first before the global table is deleted.

Manual Table Deletion

You can manually delete a table without closing a connection using the DROP TABLE statement. However, remember that the statement has to be executed by the connection that actually created the table. Take a look at the following query:

DROP TABLE #MaleStudents

This is similar to deleting a normal table.

Temporary Tables and Stored Procedures

Earlier we learned that a temporary table can only be accessed locally inside the connection that created it. There is one exception to that rule. When you are creating stored procedures, you can access temporary tables in other connections as well.

Let’s create two stored procedures using two different connections. The first stored procedure will insert data into the #MaleStudents table, while the second will select the data from the table.

Create a new connection. If you are using SQL Server Management Studio, you can do so by opening a new query window. Execute the following SQL script in the new query window.

Create Procedure spInsertStudent
    (@Name Varchar(50), @Age int,  @Gender Varchar(50))
As
Begin
    Insert Into #MaleStudents
    Values (@Name, @Age, @Gender)
End

We’ve now created a stored procedure that inserts a record into the #MaleStudent temporary table. Notice that this connection did not create the #MaleStudent, yet we are accessing it by inserting a record into it. This is because when creating a stored procedure you can access temporary tables from a connection other than the one that created the table. If you execute the above query, you will see that SQL Server will not throw any error.

Similarly, open a new connection and create the following stored procedure in it:

CREATE PROCEDURE spListStudent
AS
BEGIN

	SELECT * FROM #MaleStudents
	ORDER BY name
END

The above stored procedure selects all the records from #MaleStudents temporary table. Here again, we are accessing a temporary table inside a connection that did not create the table.

Now here is the tricky part. Though you can access a temporary table inside another connection when creating a stored procedure, you cannot access a temporary table when “executing” a stored procedure inside another connection. To execute a stored procedure that accesses a temporary table, you have to be inside the connection that created the temporary table.

Therefore, execute the following queries inside the connection that created the #MaleStudents table.

EXECUTE spInsertStudent Bradley, 45, Male
Execute spListStudent

Here the first stored procedure inserts a new student record with the name: Bradley, age:45 and gender:Male into #MaleStudents table. The second stored procedure selects all the records from the #MaleStudents table in the ascending order of name. The output of the above stored procedures will be:

[table id=16 /]

You can clearly see our newly inserted record in the selected records above.

See Also:

Performance of Table Variables in SQL Server

Introducing Common Table Expressions in SQL Server

Tags: sql, sql server, t-sql Last modified: February 01, 2023