Local and Global Temporary Tables - What is the Difference?

OVERVIEW

Sometimes we use local and global temporary tables in SQL Server which are similar to permanent tables. They are mostly used while processing data within a procedure for providing working space for the intermediate results. Operations which are performed by a normal table can also be performed with the temporary table and easily can be created at runtime. Once the use of temporary table finished, automatically get deleted. In SQL Server local and global temporary tables are created within tempdb database.

SQL Server Local Vs Global Temporary Tables

Now let’s have a look on these two Local and Global Temporary Tables. Also, different Scenario when to use global temporary table and local temporary table and basic differences. So, below we start with local temporary tables.

What is Local Temporary Table?

Local temporary tables are only visible to the current user in SQL Server. These tables automatically get terminated when a user disconnects from the instance of SQL Server and also private to the process that created it. Therefore views and triggers cannot be associated with these tables.

Single hash sign “#”as the first character of their names for representing local temporary tables.

Syntax:

CREATE TABLE #tablename

Characteristics of Local Temporary Tables:

• Start with a single hash value ‘#’
• Only for the connection in which created
• Stored in tempdb database
• Automatically get deleted when user disconnects or can be deleted using drop command
• In different database same name local temporary table can be created

What is Global Temporary Table and when to use it?

Global temporary tables are similar to local temporary tables, only the difference between local and global temporary table in SQL Server is that in global tables are visible to all connections of SQL Server where as Local Temporary Tables are visible to current user. Once global temporary table is created it is stored within tempdb database. Tables get deleted when all the connections referencing to that particular table are closed. We can say that the table is not terminated when creator session ends, it waits until the last reference connection is active. Multiple user can access the global temporary table therefore no grant or revoke permissions granted on this table.

Double hash sign “##” as the first character of their names for representing global temporary tables.

Syntax:

CREATE TABLE ##tablename

Characteristics of Global Temporary Tables:

• Its name is uniquely defined and starts with the double hash sign ‘##’
• Global temporary tables are visible to all
• Until the referencing connections are open one can access the global temporary table
• Automatically get deleted when all referencing connections closed or can be deleted manually by using DROP command

Create Local and Global Temporary Tables in SQL Server

Following query were performed for creating local and global temporary table in SQL Server 2012:

How to Create LOCAL Temporary Tables in SQL Server?

Using syntax below created employee local temporary table:

CREATE TABLE #employee (ID int Not Null, Name nvarchar(20), Department nvarchar(20),)

This will create a temporary table name employee within tempdb database. Now we can use insert or delete command to insert or delete record within local temporary table.

insert into #employee values(101, ‘John’, ‘Sales’);
insert into #employee values(102, ‘Joe’, ‘IT’);
insert into #employee values(103, ‘Lucy’, ‘HR’);
insert into #employee values(104, ‘Joy’, ‘IT’);

Now Select query is used to select record from the table:

select * from #employee

How to Create Global Temporary Tables in SQL Server?

Using syntax below created employee global temporary table:

CREATE TABLE ##employee_detail (ID int Not Null, Name nvarchar(20), Department nvarchar(20),)

This will create a temporary table name employee within tempdb database. Now we can use insert or delete command to insert or delete record within global temporary table.

insert into ##employee_detail values(101, ‘John’, ‘Sales’);
insert into ##employee_detail values(102, ‘Joe’, ‘IT’);
insert into ##employee_detail values(103, ‘Lucy’, ‘HR’);
insert into ##employee_detail values(104, ‘Joy’, ‘IT’);

Now Select query is used to select record from the table:

select * from ##employee_detail

The only difference in between local and global temporary table syntax is # & ##

Different Conditions When Temporary Tables Are Used

Several scenario are there when we can use Local and Global Temporary Tables in SQL Server can be used:

• Most common scenario for using temporary tables is within a stored procedure
• When we need to keep data temporarily
• When there is need to create indexes
• Often used as a work table
• For reports that visit several database
• Need of lookup table for multiple queries that are used for creating a report

Conclusion:

Sometimes we need a table which automatically get terminated when its use is finished. For performing operations temporarily we use temporary tables. Local and Global Temporary tables in SQL Server are the types of temporary table. After reading above article one would easily know the differences in between Local and Global Temporary Tables.