SQL Temp Table: Temporary Table In Detail 2020

In this article, you will able to understand Temporary table in detail in SQL, with how many different types of Temporary table present in SQL server. What is the difference between these Temporary tables.

Temporary Tables In Detail

Temporary tables are similar to permanent tables. When you create a permanent table they will create the database you specify and remain in that database permanently until you delete it.

Temporary tables get created in tempdb automatically and deleted automatically when no longer used them.

There are 2 different types of temporary tables

  1. Local temporary table
  2. Global temporary table.

Local Temporary Table

The local temporary table is created by appending a single hash(#) symbol as prefix whatever table name you have given.

Temporary tables are created in tempdb in a system database.

When you create a table it suffixed with so many underscores with numbers at the end and these numbers are random numbers.

To find out the table name of the temporary table use the below query.

To create temporary table use below command

create table #StudentDetails (Id int,fullName varchar(10))

You can insert modify a table and for inserting records you can use below command.

insert into #StudentDetails values (1,'Sagar'),(2,'Ram'),(1,'Lakhan'),(1,'Sham')

You can select the records from the temporary table

select * from #StudentDetails;

Temporary table in SQL 1
Temporary table in SQL 1

Temporary table in SQL 2
Temporary table in SQL

select * from tempdb..sysobjects where name like '%student%'

how to get temporary tables details
how to get temporary tables details

Points to remember

The local temporary table is available for the current connection session or the connection who created that table.

A local temporary table is automatically dropped when our connection is closed.

There is no need to explicitly drop that table. But if you want you can do so by using the below command.

drop table #StudentDetails

In the SQL server, you will able to create multiple temporary tables with the same name but you need to have different connections or sessions.

In the below figure 2 window mark with 2 different numbers they work as a different connection to the same database and different sessions.

Session in SQL temporary Table
Session in SQL temporary Table

If temporary tables are created inside the stored procedure then it gets dropped automatically upon the completion of the stored procedure.

Global Temporary Table

To create a global temporary table, you need to prefix the table name with 2 hash(#) symbols.

create table ##StudentDetails (Id int,fullName varchar(10));

Global temporary tables are visible to all connections of the SQL server and they are destroyed when the last connection referencing that table is closed.

In SQL server multiple local tables with the same name are possible but the global table is only one and you can’t create another global temporary table with the same name.

Global Temporary table
Global Temporary table

Difference between Global temporary table and a local temporary table.

Local Temporary table Global temporary table
It is prefixed with a single # symbol It is prefixed with the ## double # symbol.
SQL server appends an underscore and random numbers at the end of the table name. SQL Server does not append an underscore and random numbers after the table name
This is visible for only who created that table It is visible to all sessions of that SQL server.
It is dropped automatically when the session ends. It is in memory till the last connection who is using that table is closed.

GitHub:- https://github.com/Sagar-Jaybhay/

Sagar Jaybhay, from Maharashtra, India, is currently a Senior Software Developer. He has continuously grown in the roles that he has held in the more than seven years he has been with this company. Sagar Jaybhay is an excellent team member and prides himself on his work contributions to his team and company as a whole.

Related posts