Different Types of Temporary Tables

Based on the behavior and scope of the table SQL Server provides two types of temporary tables as listed below.

  • Local Temp Table
  • Global Temp Table

Local Temp Table

Local temp tables are only available to the current connection for the user; and they are automatically deleted when the user disconnects from instances. Local temporary table name is stared with hash (“#”) sign.

Example

CREATE TABLE #LocalTemp
(
 UserID int,
 Name varchar(50), 
 Address varchar(150)
)
GO
insert into #LocalTemp  values ( 1, 'Brijesh Kumar','Noida');
GO
Select * from #LocalTemp

The scope of Local temp table exist to the current session of current user means to the current query window. If you will close the current query window or open a new query window and will try to find above created temp table, it will give you the error.

Global Temp Table

Global Temporary tables name starts with a double hash (“##”). Once this table has been created by a connection, like a permanent table it is then available to any user by any connection. It can only be deleted once all connections have been closed.

Example

CREATE TABLE ##GlobalTemp
(
 UserID int,
 Name varchar(50), 
 Address varchar(150)
)
GO
insert into ##GlobalTemp  values ( 1, 'Brijesh Kumar','Noida');
GO
Select * from ##GlobalTemp

Global temporary tables are visible to all SQL Server connections while Local temporary tables are visible to only current SQL Server connection.