Temporary Tables in SQL Server 2012

In this article, I am going to give a quick overview of temporary tables in SQL Server 2012. 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. So let's have a look at a practical example of how to create temporary tables in SQL Server 2012. The example is developed in SQL Server 2012 using the SQL Server Management Studio.

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 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 are dropped automatically when the last session using the temporary table has 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.

 

Storage Location of Temporary Table

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

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

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

Dropping Temporary Tables

We can delete the temporary tables using the drop command as follows:

DROP TABLE #temporaryTable

Print 'Deleted table'

 

Output

DROPPING Multiple Temporary Tables

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

Syntax

DROPTABLE #table_name1,#table_name2