Go Back

difference between a "Local Temporary Table" and "Global Temporary Table" in SQL -DeveloperIndian

5/7/2022
All Articles

#global temporary table in sql #global temporary table sql #global temporary table #global temp table sql

difference between a "Local Temporary Table" and "Global Temporary Table" in SQL -DeveloperIndian

6. What is the difference between a "Local Temporary Table" and "Global Temporary Table"?

  1.  A Local Temporary Table  created by giving it a prefix of #  it is easy to represent whereas a Global Temporary Table  created by giving it a prefix of ##.
  2.  A Local Temporary Table can not be shared among multiple users whereas a Global Temporary Table should be shared among multiple users.
  3.  A Local Temporary Table is only present to the current DB connection for the current user and are cleared when the connection is closed whereas a Global Temporary Table is available to any connection once initialise. They are clean when the last connection is closed.

Below are the Syntex of   Temporary  table and Global table 

  • Table variables (DECLARE @t TABLE)
  • Local temporary tables (CREATE TABLE #t)
  • Global temporary tables (CREATE TABLE ##t
  • Tempdb permanent tables (USE tempdb CREATE TABLE t
     

Below is Example of Temporary Table

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

 

Below is Example of Permanent Table


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

Article