Temporary Table in SQL Server

Introduction

A temporary table is one of the best features of SQL Server. If you want to perform a large amount of operations in SQL or work on a large query of SQL then you need to store some data or maybe a table of data for the operation. To store a large amount of data we can create a temporary table and store the table with for some period of time.

You can perform any type of operation like insert, update, delete, selection, join and so on. One of the best features of a temporary table is you can store temporary data inside it and the temporary table will be automatically deleted when the current client session is terminated.

Types of Temporary table

The following are the 2 kinds of temporary tables available:

1. Local Temporary Table: These are only available for the current instance, it will be automatically deleted when the user is disconnected from the instance. To create a local temporary table we use the table name with a # (hash) prefix.
2. Global Temporary Table: This table is the same as a permanent table but the difference is only that when all the connections are closed or all the instances are deleted then the table will be deleted. To create a local temporary table we use a table name with a ## (double hash) prefix.

How to create temporary tables

As we know, there are two types of temporary tables, global and local.

Local Temporary Table

To create a local temporary table as I described we will use "# symbol" so the syntax of the local temporary table is:

create table ##<Table_Name>(

<Column_Name> [datatype](size),

<Column_Name> [datatype](size),........

)

For Example

create table #Example_Table(

id int,

Name varchar(20)

)

Now after creating this table you can see your table is available inside "System Databases > tempdb > Temporary Tables" so go to the Object Explorer and check it there.



Perform any operation inside it like insert, update, delete. like:

insert into #Example_Table values(1,'Sourabh Somani')

insert into #Example_Table values(2,'Shaili Dashora')

insert into #Example_Table values(3,'Divya Sharma')

insert into #Example_Table values(4,'Swati Soni') 

After performing all the operations, to see the data write:

select * from #Example_Table

Note: between all the operations the table is not deleted, but when you close this tab or instance the table will not be there.

For Example



After closing it refresh the object browser and your table will be deleted.



Global Temporary Table

To create a global temporary table as I said we use "## symbol" so the syntax of the local temporary table is:

create table ##<Table_Name>(

<Column_Name> [datatype](size),

<Column_Name> [datatype](size),........

)  

For Example

create table ##Example_Table(

id int,

Name varchar(20)

)  

Note: This table is a global temporary table so it will be deleted when all the instances or the session that was created are closed.

The need for temporary tables are:

1. When Many operations are performed in a single or multiple tables.
2. When many manipulations are done on a row in a Stored Procedure.
3. When we have a complex join operation.

Key Points

1. Temporary Tables exist at Databases > System Databases > tempdb > Temporary Tables
2. Temporary tables are automatically deleted; there is no need to delete them explicitly when you are working.