Global Temporary Tables in SQL

Global Temporary Tables in SQL

Like other temporary tables Global Temporary tables are also used to store data temporarily but the difference is it is visible to all sessions that means no other sessions can create the Global Temporary 

Table with same name that exists in other sessions until the creating Global table session closes or other session which are referencing global tables closes down.

Like Local Temporary Table & table variable they are also stored in TempDb Database and automatically cleanup when the session which creates Global Temporary Table disconnect and there are no active references to the table.

They are useful when you are sharing temporary data with everyone over the server as all session have full access to Global Temp Table without any permissions so keep in mind that full access mean any session working on it can drop the table also.

They are created with two hash ie ## sign before the table Name whereas Local Temp are created with single #

--Syntax of creating Global Temp Table

create table ##Employee 

( 

Empid nvarchar(30),

empName nvarchar(30)

)

insert into  ##Employee values ('1','xyz'),('2','abc')

select * from  ##Employee 

--If you check in TempDB database a table ##Employee exist there

--Now try to run from other session 

select * from  ##Employee 

This will run succesfully with other sessions also.

Now close the session where you have created the ##Employee table and refresh the TempDb

database the table will no more exists.

 The Advantage of Temporary tables over table variable  is that you can have index,constraints 

and Keys on that and the syntax is same as any physical table in the database.

For large Dataset or large no. of records to store temporarirly and shared with everyone it's better to go for Global temp table as you can have indexes on them.