Temporary Tables Quick Practical in SQL Server 

(Temp Tables)

   Part - 10

Here is an interesting practical to check the scope of local temp table. You can just copy paste these queries and check the scope also verify their existence in tempdb

Lets create 3 different sessions for both Local temp table and Global temp table each.

Local Temp Table Practical:

Create 3 different session:

-----------> Session 1 for Local Temp Table <------------------------

CREATE TABLE #tblEmployee 

(Empid INT NOT NULL,

EmpName Varchar(50))

INSERT INTO #tblEmployee VALUES (1, 'John')

select * from tempdb.sys.tables

SELECT * FROM #tblEmployee

When you run these queries the #tblEmployee created in session 1 is not available in session 2

--> Refresh Temp db at below path: 1 temp table will be available 

Databases >> System Databases >> Tempdb >> Temporary Tables

-----------> Session 2 for Local Temp Table <------------------------

SELECT * FROM #tblEmployee  -->  which would give a invalid object name

CREATE TABLE #tblEmployee 

(Empid INT NOT NULL,

EmpName Varchar(50))

INSERT INTO #tblEmployee VALUES (2, 'Peter')

SELECT * FROM #tblEmployee

When you run these queries the #tblEmployee created in session 2 is not available in session 3

--> Refresh Temp db at below path: there will be 2 tables

Databases >> System Databases >> Tempdb >> Temporary Tables

-----------> Session 3 for Local Temp Table <------------------------

SELECT * FROM #tblEmployee

CREATE TABLE #tblEmployee 

(Empid INT NOT NULL,

EmpName Varchar(50))

INSERT INTO #tblEmployee VALUES (3, 'Smith')

SELECT * FROM #tblEmployee

The local temp table is available to that scope alone

--> Refresh Temp db at below path: there will be 3 tables

Databases >> System Databases >> Tempdb >> Temporary Tables

------------------------------------->Closing the sessions<---------------------------------------------

--> Once you close the session 1 and refresh temp db the temp table 1 will be dropped

--> Once you close the session 2 and refresh temp db the temp table 2 will be dropped

--> Once you close the session 3 and refresh temp db the temp table 3 will be dropped

Global Temp Table Practical:

Create 3 different session:

-----------> Session 1 for Global Temp Table <------------------------

CREATE TABLE ##tblEmployee 

(Empid INT NOT NULL,

EmpName Varchar(50))

INSERT INTO ##tblEmployee VALUES (1, 'John')

select * from tempdb.sys.tables

SELECT * FROM ##tblEmployee

--> Refresh Temp db at below path: global  temp table will be available 

Databases >> System Databases >> Tempdb >> Temporary Tables

-----------> Session 2 for Global Temp Table <------------------------

SELECT * FROM ##tblEmployee ---> accessible in session 2 as well until session 1 is closed

CREATE TABLE #tblEmployee 

(Empid INT NOT NULL,

EmpName Varchar(50))

INSERT INTO #tblEmployee VALUES (2, 'Peter')

SELECT * FROM #tblEmployee

-----------> Session 3 for Global Temp Table <------------------------

SELECT * FROM ##tblEmployee ---> accessible in session 2 as well until session 1 is closed

CREATE TABLE #tblEmployee 

(Empid INT NOT NULL,

EmpName Varchar(50))

INSERT INTO #tblEmployee VALUES (3, 'Smith')

SELECT * FROM #tblEmployee

-----------> Session 4 for Global Temp Table <------------------------

SELECT * FROM #tblEmployee -- accessible is session 4 until session one is open

Temp table Usage:

Temp tables can be joined with permanent tables and can be used for processing

DROP TABLE IF EXISTS tblEmployee  

CREATE TABLE tblEmployee 

(Empid INT NOT NULL,

EmpName Varchar(50))

INSERT INTO tblEmployee VALUES (1, 'John'), (2, 'Peter')

CREATE TABLE #tblEmployee 

(Empid INT NOT NULL,

EmpName Varchar(50))

INSERT INTO #tblEmployee VALUES (1, 'John'), (2, 'Peter')