Local Temporary Table In SQL

Local Temporary Table In SQL

Like other temporary tables i.e. Global TempTable ,Table Variable 

Local Temporary tables are also used to store data temporarily but they are visible to only current  session who created the local temp table once the connection who created local temp table disconnects the session the table automatically cleared up from the tempdb database.

As it's Scope is limited to current session only so you can create two Local temp table  with same name in different session in order to identify the tables by sql server the sql server internally adds a suffix after the tablename to uniquely identify the table in tempdb database.which is the background process you may not to worry about this.

They are created with single # sign before the table name.

--Syntax of creating local Temp Table

create table #Employee 

( 

Empid nvarchar(30),

empName nvarchar(30)

)

insert into  #Employee values ('1','xyz'),('2','abc')

select * from  #Employee 

 The Advantage of Temporary tables over table variable  is that you can have index,constraints 

and Keys on that and the syntax is same as any physical table in the database.

For large Dataset or large no. of records to store temporarirly only for a session go with local temp table as you can have indexes on them.

NOTE: As you know the local temp table are visible to current session only but suppose you have created a procedure as procedure 1 inside that you have created a local temp table #temp1 if you are calling that  procedure 1 from procedure 2 the temp table #temp1 is visible to procedure 2 also and procedure 2 is called by another proc 3 then also temp1 it is visible to proc 3 in Short if a procedure which created temp table is called by n no. of procedures the temp table is visible to all procedure calling that proc..