Monday, June 8, 2015

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

1 comment:

  1. Securing personal information up front is far less expensive than cleaning up the mess after the fact. The cost of responding to a data breach is 15 times the cost of encrypting the data in the first place.
    virtual data room pricing

    ReplyDelete

Popular Posts