SQL Server’s Temporary and Global Temporary Tables

Temporary Table:-

Temporary tables are only accessible to the session that created them. When the operation or session that produced them ends, these tables are immediately destroyed.

Temporary tables in MS SQL Server are more developer friendly and commonly utilized in development. Only the current session’s temporary tables are accessible. Temporary tables are created with the same syntax as CREATE TABLE, except that the table name begins with a ‘#’ symbol. When a table is declared by a single ‘#’ symbol, it is a local temporary table with a scope confined to the session in which it is formed.

Global Temporary Table:-

Global Temporary tables are visible or accessible in all sessions. Also, all users.

Global Temporary tables are created using the same syntax as CREATE TABLE, with the exception that the table name begins with “##” (two ‘#’ signs). When the table is merely “##,” it is defined as a local global temporary table whose scope is not confined to the session in which it is created.

When the last session that used the temporary table has finished, the Global Temporary table is immediately dropped. Local temporary tables and global temporary tables are both physical objects.

Temporary Tables Uses:-

When used with stored procedures, a Temporary Table variable can be particularly handy for passing input/output parameters or storing the result of a table-valued function. To create the Temporary table, run the following query:

CREATE TABLE #TESTTEMPTABLE 
(  
Id INT,  
FirstName VARCHAR(30),  
LastName VARCHAR(30),
Date DATETIME DEFAULT GETDATE()  
)

Execute the query. The “TESTTEMPTABLE” will then be created. We’ll now insert some values into the TestTempTable.

INSERT INTO #TESTTEMPTABLE (Id, FirstName ,LastName) VALUES(1,'Joan','Due');  
INSERT INTO #TESTTEMPTABLE (Id, FirstName ,LastName) VALUES(2,'Shefali','abc');

select * from  #TESTTEMPTABLE

CREATE TABLE ##GLOBALTESTTEMPTABLE  
(  
Id INT,  
Name VARCHAR(30),  
Date DATETIME DEFAULT GETDATE()  
)

INSERT INTO ##GLOBALTESTTEMPTABLE (Id, Name) VALUES(1,'Shruti');  
INSERT INTO ##GLOBALTESTTEMPTABLE (Id, Name) VALUES(2,'Dhruti');

select * from  ##GLOBALTESTTEMPTABLE