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
- Local temporary table
- 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;
select * from tempdb..sysobjects where name like '%student%'
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.
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.
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.|