Temporary Tables in SQL Server

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. Scope of the Local Temporary Table is the session in which it is created and they are dropped automatically once the session ends and we can also drop them explicitly. If a Temporary Table is created within a batch, then it can be accessed within the next batch of the same session.

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 #

Creating local temporary table : -

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


Global temporary tables 

These 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
)

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.

Delete the temporary table using the drop command as follows:

DROP TABLE #temporaryTable