Sql server: Local vs. Global temporary table

In this article today I am going to explain the difference between Local and Global Temporary table in sql server

Description:

Temporary tables are created at runtime and we can do all operation which we can do on a normal table. Temporary tables are stored in System Databases >> tempdb database.

Temporary tables are of two types:

1. Local Temporary tables

2. Global Temporary tables

Local Temporary tables:

The local temp table is the most commonly used temp table. Local temporary table name is started with single hash ("#") sign. Local temp tables are only exists for the duration of user session that created the temp tables and automatically deleted when the user log off or session that created the tables has been closed.

Example and Syntax of Local Temporary tables:

Create a local Temp table:

Create table #Temp

(

Id int,

Username varchar(50),

FirstName varchar(50),

EmailId varchar(50)

)

We can insert and delete the records in temporary table.

Insert the records to table:

Insert into #Temp values (1,'vijay','Vijay','vijay@gmail.com')

Insert into #Temp values (2,'Tarun','Tarun','Tarun@gmail.com')

Insert into #Temp values (3,'Neha','Neha','Neha@gmail.com')

Delete the records from table:

Delete from #Temp where id =1

Select the records from table:

select * from #Temp

Check the Temporary table exists or not:

IF OBJECT_ID('tempdb..#Temp','local') IS NOT NULL

BEGIN

PRINT '#temp exists!'

END

ELSE

BEGIN

PRINT '#temp does not exist!'

END

Global Temporary Tables:

Global temporary table name is started with double hash ("##") sign. Global temporary tables are temporary tables that are available to all sessions and users. These are automatically dropped when all Sql server connection have been closed.

Example and Syntax of Global Temporary tables:

Create a Global Temp table:

Create table ##GlobalTemp

(

Id int,

Username varchar(50),

FirstName varchar(50),

EmailId varchar(50)

)

Insert records into created table:

Insert into ##GlobalTemp values (1,'vijay','Vijay','vijay@gmail.com')

Insert into ##GlobalTemp values (2,'Tarun','Tarun','Tarun@gmail.com')

Insert into ##GlobalTemp values (3,'Neha','Neha','Neha@gmail.com')

Select the records from created global table:

Select * from ##GlobalTemp

Check Global table exist or not:

IF OBJECT_ID('tempdb..##GlobalTemp','local') IS NOT NULL

BEGIN

PRINT '##GlobalTemp exists!'

END

ELSE

BEGIN

PRINT '##GlobalTemp does not exist!'

END