What is a Temporary Table in SQL Server

Introduction

This article explains how to make a temporary table in SQL Server. A temporary table is one of the best features of SQL Server.

Temporary table in SQL Server

If you want to perform a large number 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 for some 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 storing temporary data inside it; the temporary table will be automatically deleted when the current client session is terminated.

Types of Temporary tables in SQL Server

 

1. Local Temporary Table- These are only available for the current instance; they will be automatically deleted when the user is disconnected. We use the table name with a # (hash) prefix to create a local temporary table. The following are the two kinds of temporary tables available:
2. Global Temporary Table- This table is the same as a permanent table. Still, the difference is only that the table will be deleted when all the connections are closed or all the instances are deleted. We use a table name with a ## (double hash) prefix to create a local temporary table.

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 would use the "# 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, or 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 written.

select * from #Example_Table

Note. The table is not deleted between all the operations, 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 the "## 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 temporary global table, so it will be deleted when all the instances or the created session are closed.

The need for temporary tables 

1. When Many operations are performed in 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.

Conclusion

This article taught us about Temporary Table with different types and code examples in SQL Server.