Tables which are created in TempDB Database instead of creating in the actual database is considered as Temporary Tables. It will be automatically deleted once the connection is closed or if the server is stopped/restarted. Temporary tables doesn't locate in our actual database inturn created and function from tempdb database. To reduce the number of rows for joins, to aggregate data from different sources, or to replaces cursors and for parameterized views, we will use Temporary tables.
There are two kinds of Temporary Tables:
1. Local Temporary Table
2. Global Temporary Table
Local Temporary Table:
Tables whose accessibility is specific only to the scope of temporary table declaredis considered as Local temporary tables. Local Temporary tables exists until specific user connection exists/until the server is restarted. If User1 have created a table #A another user can create a temporary table #A so that, each user can create their own user specific temporary tables. Local Temporary table can be specified with # symbol before the table name.Am Creating a SP with Temporary table(named #LocalTempTable) creation inside the SP,
Create procedure usp_proc1
as
Begin
create table #Temp (ID int,[Name] char(30) )
insert into #Temp values (1,'Venkat')
End
Am Creating another SP Which calls the above created Temporary table(#LocalTempTable)
Create procedure usp_proc2
as
Begin
select * from #Temp
End
Now, i am trying to executing the first Stored Procedure
exec usp_proc1
A Temporary table is created and now i am trying to execute the Second SP,
exec usp_proc2
Am gettting an error "Invalid object name '#LocalTempTable'." Its because the local temporary table created in the first SP is specific only to it and it cant be accessed by another SP.
Global Temporary Table:
Tables whose accessibility is global to all the users in the database were come under global temporary table. Global Temporary table exists until the last connection exists/until the server is restarted. Global Temporary table can be specified with ## symbol before the table name.
There are two kinds of Temporary Tables:
1. Local Temporary Table
2. Global Temporary Table
Local Temporary Table:
Tables whose accessibility is specific only to the scope of temporary table declaredis considered as Local temporary tables. Local Temporary tables exists until specific user connection exists/until the server is restarted. If User1 have created a table #A another user can create a temporary table #A so that, each user can create their own user specific temporary tables. Local Temporary table can be specified with # symbol before the table name.Am Creating a SP with Temporary table(named #LocalTempTable) creation inside the SP,
Create procedure usp_proc1
as
Begin
create table #Temp (ID int,[Name] char(30) )
insert into #Temp values (1,'Venkat')
End
Am Creating another SP Which calls the above created Temporary table(#LocalTempTable)
Create procedure usp_proc2
as
Begin
select * from #Temp
End
Now, i am trying to executing the first Stored Procedure
exec usp_proc1
A Temporary table is created and now i am trying to execute the Second SP,
exec usp_proc2
Am gettting an error "Invalid object name '#LocalTempTable'." Its because the local temporary table created in the first SP is specific only to it and it cant be accessed by another SP.
Global Temporary Table:
Tables whose accessibility is global to all the users in the database were come under global temporary table. Global Temporary table exists until the last connection exists/until the server is restarted. Global Temporary table can be specified with ## symbol before the table name.
Am Creating SP with Temporary table(named ##GlobaTempTable) creation inside the SP,
Create procedure usp_proc3
as
Begin
Create table ##Temp (ID int,[Name] char(30) )
Insert into ##Temp values (1,'Venkat')
End
Creating another SP Which calls the above created Temporary table(#GlobalTempTable)
Create procedure usp_proc4
as
Begin
Select * from ##Temp
End
Now, i am trying to executing the first Stored Procedure
exec usp_proc3
Global Temporary table is created and now i am trying to execute the Second SP,
exec usp_proc4
It will display the result as "1" "Venkat"
Limitations and Alternative for Temporary Table
Temporary tables created in TempDB will create additional overheads because of huge resource utilization. The best alternative for temporary table is creating table variables, which reside only in memory instead of creating in TempDB and it will use less resources when compare to Temporary tables.