SQL - Temp Table (Local & Global Temp Tables)

Temporary table is also known as temp table. This is another most usable feature of the SQL Server. Temp tables are used to store temporary result set which is defined within the execution scope of a single SQL statement such as SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW. 
Temp tables have the same feature as any normal data table. We can create indexes on the temp table but it depends on the requirements.

Whenever we create a temporary table, it goes to Temporary folder of tempdb database. Based on the behavior and scope of requirement, SQL server provides two types of temp tables as given below - 

1. Local Temp Table , 
2. Global Temp Table

Local Temp Tables – They are easily accessible for single user. This is the most usable in SQL server and works only in the current connection of the user. They automatically deleted from the session whenever user disconnects from instances. We can create them by starting with hash (#) sign as given below -

1. We can create them as we create permanent data tables in the database before use them and
2. We can create them by using into temp table name from the permanent table or views in the select statements. They get the same data structure from the select data columns which is very useful to avoid data conflict.

----- syntax to create temp table create table #TempEmp ( EmpId Int, EmpName Varchar(25), EmpAge int, EmpDept varchar(6) ) ----- how to insert data into temp table Insert into #TempEmp  values (1,'ABC',21,'D001'), (2,'DEF',22,'D011'), (3,'GHI',23,'D021'), (4,'JKL',24,'D031') ----- get result from the temp table select EmpId, EmpName, EmpAge, EmpDept From #TempEmp EmpId EmpName EmpAge EmpDept 1 ABC 21 D001 2 DEF 22 D011 3 GHI 23 D021 4 JKL 24 D031 ----- get filtered result into another temp table select EmpId,EmpName, EmpAge,EmpDept into #Empbelow22 From #TempEmp where EmpAge>22 ----- pull the data from select EmpId, EmpName, EmpAge, EmpDept from #Empbelow22 EmpId EmpName EmpAge EmpDept 1 ABC 21 D001 2 DEF 22 D011 ----- drop temp tables drop table #Empbelow22, #TempEmp

Global Temp Tables –They are easily accessible for multiple users. The scope of Global temporary table is the same for the entire user for a particular connection. They automatically deleted whenever all users sessions are disconnected. We can create them by starting with double hash (##) sign. We cannot set revoke and grant permissions to the global temp table because it’s always accessible to all users.

----- syntax to create Global temp table create table ##TempEmp ( EmpId Int, EmpName Varchar(25), EmpAge int, EmpDept varchar(6) ) ----- how to insert data into Global temp table Insert into ##TempEmp  values (1,'ABC',21,'D001'), (2,'DEF',22,'D011'), (3,'GHI',23,'D021'), (4,'JKL',24,'D031') ----- get result from the Global temp table select EmpId, EmpName, EmpAge, EmpDept From ##TempEmp EmpId EmpName EmpAge EmpDept 1 ABC 21 D001 2 DEF 22 D011 3 GHI 23 D021 4 JKL 24 D031

When can we use of temp tables –

There are lots of scenarios, where we can easily use of the temp table to increase the performances and solve the problems such as –

• In the stored procedure, wherever we require a large number of row manipulations. 
• They could be the replacement of the cursor in the query to store the result set data into a temp table, then we can manipulate the data from there. 
• They are the best option to do complex join operations.
• We can use of n-numbers of temp tables to do more complex data manipulation and call them in the entire single session.

Point to remember: To avoid additional overhead and performance issues, drop the temp tables after use of the temp table because Temporary table stored on tempdb of SQL Server which is a separate database.
To know more on the different kinds of the tables in SQL Servers at

1. SQL – Wide Tables
2. SQL - Table Variables
3. SQL - Temp Table (Local & Global Temp Tables)
4. SQL - Common Table Expression
5. SQL - Difference between Table Variable and Common Type Expression
6. SQL - Difference between Temp Table and CTE
7. SQL - Difference between Temp Table and Table Variable