Temporary Tables in SQL Server 2012

In this article, I am going to give a quick overview of temporary tables in SQL Server 2012. local temporary table, global temporary table, drop temporary table.

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

  1. create table #table_name  
  2. (  
  3. column_name varchar(20),  
  4. column_no int  
  5. )
Create-Local-Temporary-Tables-in-sqlserver.jpg
 

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

  1. create table ##GlobalTemporaryTable  
  2. (  
  3. column_name varchar(20),  
  4. column_no int  
  5. ) 
 
 
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.

Drop Temporary Tables

 
We can delete the temporary tables using the drop command as follows:
  1. DROP TABLE #temporaryTable  
  2. Print 'Deleted table'  
Output
 
 

Drop Multiple Temporary Tables

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

Syntax
  1. DROP TABLE #table_name1,#table_name2