In this article today I am
going to explain the difference between Local and Global Temporary table in sql
server
Description:
Temporary tables are created at runtime and we can do all
operation which we can do on a normal table. Temporary tables are stored in System Databases >> tempdb
database.
Temporary tables are of two types:
1. Local Temporary tables
2. Global Temporary tables
Local Temporary tables:
The local temp table is the most commonly used temp table. Local
temporary table name is started with single hash ("#") sign. Local
temp tables are only exists for the duration of user session that created the
temp tables and automatically deleted when the user log off or session that
created the tables has been closed.
Example and Syntax of Local
Temporary tables:
Create a local Temp table:
Create table #Temp
(
Id int,
Username varchar(50),
FirstName varchar(50),
EmailId varchar(50)
)
We can insert and delete the records in temporary table.
Insert the records to table:
Insert into #Temp values (1,'vijay','Vijay','vijay@gmail.com')
Insert into #Temp values (2,'Tarun','Tarun','Tarun@gmail.com')
Insert into #Temp values (3,'Neha','Neha','Neha@gmail.com')
Delete the records from table:
Delete from #Temp where id =1
Select the records from table:
select * from
#Temp
Check the Temporary table exists or not:
IF OBJECT_ID('tempdb..#Temp','local') IS NOT NULL
BEGIN
PRINT '#temp exists!'
END
ELSE
BEGIN
PRINT '#temp does not exist!'
END
Global Temporary Tables:
Global temporary table name is started with double hash
("##") sign. Global temporary tables are temporary tables that are
available to all sessions and users. These are automatically dropped when all
Sql server connection have been closed.
Example and Syntax of Global
Temporary tables:
Create a Global Temp table:
Create table ##GlobalTemp
(
Id int,
Username varchar(50),
FirstName varchar(50),
EmailId varchar(50)
)
Insert records into created table:
Insert into ##GlobalTemp values (1,'vijay','Vijay','vijay@gmail.com')
Insert into ##GlobalTemp values (2,'Tarun','Tarun','Tarun@gmail.com')
Insert into ##GlobalTemp values (3,'Neha','Neha','Neha@gmail.com')
Select the records from created global table:
Select * from
##GlobalTemp
Check Global table exist or not:
IF OBJECT_ID('tempdb..##GlobalTemp','local') IS NOT NULL
BEGIN
PRINT '##GlobalTemp exists!'
END
ELSE
BEGIN
PRINT '##GlobalTemp does not exist!'
END
No comments:
Post a Comment