Local and Global temporary tables in SQL Server

In this post, I will explain Local and global temporary tables in SQL Server.

1. A local temporary table exists only for the duration of a connection or if defined inside a compound statement, for the duration of the compound statement. Local temp tables are only available to the SQL Server session or connection (means single user) that created the tables. These are automatically deleted when the session that created the tables has been closed. The local temporary table name is stared with a single hash ("#") sign.

Example-

CREATE TABLE #LocalTemp

(

UserID int,

Name varchar(50),

Address varchar(150)

)

GO

insert into #LocalTemp values ( 1, 'Name','Address');

GO

Select * from #LocalTemp

Drop table #LocalTemp

The scope of Local temp table exists to the current session of a 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.

2. A global temporary table remains in the database permanently, but the rows exist only within a given connection. When the connection is closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when the database is opened next time.

Global temp tables are available to all SQL Server sessions or connections (means all the user). These can be created by any SQL Server connection user and these are automatically deleted when all the SQL Server connections have been closed. The global temporary table name is stared with double hash ("##") sign.

Example-

CREATE TABLE ##GlobalTemp

(

UserID int,

Name varchar(50),

Address varchar(150)

)

GO

insert into ##GlobalTemp values ( 1, 'Name','Address');

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.