What are Table Variables and Temporary Tables in SQL ?

Introduction

Temporary tables in SQL Server also known as temp tables are used by database developers and DBAs to store data temporarily and work on this data for faster performance. In this article, we will learn what temporary tables are in SQL Server, different temporary table types, and how to work with temp tables using SQL.

Temporary Tables in SQL Server

Temporary tables provide temporary data storage in exact form of original tables for quick access of data. Temporary tables are stored in TempDB. They work like a regular table in that you can perform the operations select, insert and delete as for a regular table. If created inside a stored procedure, they are destroyed upon completion of the stored procedure.

Let's have a look at a practical example of how to create and use temporary tables in SQL Server. The example is developed in SQL Server using the SQL Server Management Studio.

What are types of temporary tables in SQL Server?

There are two types of temporary tables; one is local and the other is global.

Local Temporary Tables

Local temporary tables are the tables stored in tempdb. Local temporary tables are temporary tables that are available only to the session that created them. These tables are automatically destroyed at the termination of the procedure or session. They are specified with the prefix #, for example, #table_name, and these temp tables can be created with the same name in multiple windows.

Creating a local temporary table

create table #table_name  
(  
column_name varchar(20),  
column_no int  
)

Global Temporary Tables

Global temporary tables are also stored in tempdb. Global temporary tables are temporary tables that are available to all sessions and all users. They have dropped automatically when the last session using the temporary table has been completed. They are specified with the prefix #, for example, ##table_name.

Creating Global Temporary Table

create table ##GlobalTemporaryTable  
(  
column_name varchar(20),  
column_no int  
)

Both tables are stored in the tempdb system database.

Where is the temporary table stored in SQL Server?

Temporary tables are stored inside the Temporary Folder of TempDB. Whenever we create a temporary table, it goes to the Temporary folder of the tempdb database. tempdb -> temporary tables.

Temporary Tables vs Regular Table in SQL Server

What is the difference between a temp table and a regular table?

Difference between Temporary Tables VS Regular Table

A Temporary table differs in the following two ways from regular tables:

Each temporary table is implicitly dropped by the system.
Each temporary table is stored in the tempdb system database. The table variable doesn't.

How to Drop Temporary Tables in SQL Server?

We can use the DROP SQL statement to drop a temp table.

Drop Temporary Tables

We can delete the temporary tables using a DROP TABLE command as follows.

DROP TABLE #temporaryTable  
Print 'Deleted table'

Output

Drop Multiple Temporary Tables

You can drop a single Temporary Table or multiple Temporary Tables at a time.

Syntax

DROP TABLE #table_name1,#table_name2

When to use a Temporary Tables in SQL Server?

A temporary table is used to store data within a session from multiple tables using complex queries and where you need data manipulation on the server side before the data can be returned. For example, if you need to select data from multiple tables, apply a business logic on some columns, also the selection can be changed based on the calculated results. Once you are done with this temp data, the table is dropped form the server. There is no permanent storage of temp tables.

Another example is, if you have a store proc that involves data manipulation using complex SQL queries.

Difference between a temp table and a table variable?

Table variables in SQL are local variables that are created using a DECLARE statement and are assigned values using a SET or SELECT statement. The variables exist in the TempDB with their value as long as their scope exist but once they are out of scope, they are dropped.

Difference between Temporary Table and Table Variable

Conclusion

In this article, we learned about Temporary Tables and how to create them in SQL Server.

Next: Temporary Tables and Table Variables in SQL Server