Local and Global Temporary Table in SQL Server

local Temporary Table:A local temporary table, #tableName, exists only for the duration of a user session or the table that created the temporary table.When the user logs off or when the procedure that created the table completes, the local temporary table is lost.

create table #Employee

(

Id int Primary key Not Null,

Name varchar(150)

)

Insert into #Employee values(1,'Jitu')

select * from #Employee

Global Temporary Table:A global temporary table, ##tableName, also exists for the duration of a user session or the table that created the table.When the last user session that references the table disconnects, the global temporary table is lost.

create table ##Employee

(

Id int Primary key Not Null,

Name varchar(150)

)

Insert into ##Employee values(1,'Hemant')

select * from ##Employee

There are a few characteristics of Global Temporary Tables:

1.   Local Temporary ('#') tables are visible only in the current session; Global Temporary('##') tables are visible to all sessions.

2.   It starts with the single hash value "##" as the prefix of the table name and its name is always unique. There is no random number appended to the name.

3.   Global Temporary Tables are visible to all connections of SQL Server.

4.   Global Temporary Tables are only destroyed when the last connection referencing the table is closed (in which we have created the Global Temporary Table).

5.   You can access the Global Temporary Tables from all connections of SQL Server until the referencing connection is open.