Temporary Tables Types In SQL

This blog is about temporary tables in SQL, their types, and differences between them.

Temporary Tables

Temporary Tables are similar to permanent tables, the only difference is Temporary Tables are stored in TempDB and they are dropped when the connection which created it is closed, whereas permanent tables are stored in the database in which we create them and are dropped when we drop them using drop command.

Types of Temporary Tables:

1. Local Temporary Tables
2. Global Temporary Tables

Local Temporary Tables

Local Temporary Tables start with Single (#) sign.Here is the Syntax for Creating Local Temporary Tables, as shown in the following image, I have created a Local Temporary table name #tmp1

 

Local Temporary tables are available only for the connection which created it, connection means one query editor window, as shown in the image, I have tried to access Local Temporary table created using a different connection but it gave me the error, invalid object name #tmp1

 

You can create two Local Temporary Tables with the same name using different connections.This is because SQL server attaches random numbers as a postfix to the name of the Local Temporary Tables as shown in the image.

 

As you can see in the above image, I have created two local temporary tables with same name #tmp1 using two different connections, to identify them uniquely Sql Server has attached a Unique random number as a postfix after table name. 

If the Local Temporary table is created inside the stored procedure, it gets dropped automatically after the stored procedure is executed.

Local Temporary Tables are dropped automatically when the connection which created it is closed or we can also drop them using drop command ex:-drop table #tmp1

Global Temporary Tables

Global Temporary Tables start with double (##) sign.Here is the Syntax for Creating Global Temporary Tables, as shown in the following image I have created a Global Temporary table name ##tmp1

 

Global Temporary Tables are available for all the connections. As shown in the image I have tried to access the Global Temporary Table I created using one connection in another connection and it did not give any error:

 

You cannot create two Global Temporary Tables with the same name using different connections because Global Temporary Tables have a unique name, and the SQL Server will not be attached with any random number as a postfix to Global Temporary Tables as shown in the image:

 

As you can see in the above image, I tried to create a global temporary table with the same name which is ##tmp1 using a different connection, but SQL server does not allow me to create a global temporary table if the same was already created.

Global Temporary Tables, like local temporary tables, are also dropped automatically when the connection which created it is closed or you can drop them using drop command ex:-drop table ##tmp1

Hence the major difference between a Global Temporary table and a Local Temporary table is that Global Temporary Table is available for all the connections and Local Temporary table is available only for the connection which created it.

 

You cannot create two global temporary tables with the same name using different connections but you can create two local temporary tables with the same name using different connections.

And both will get dropped or deleted when the connection which created them is closed.