Prizes & Awards
My Profile
Active Members
TodayLast 7 Days
more...
|
 New Feature: Community Sites:
Create your own .NET community website and start earning from Google AdSense !
It's Free !
|
Temporary Tables Usages and Types
Posted Date: 24 Jul 2007 Resource Type: Articles Category: Databases
|
Posted By: http://venkattechnicalblog.blogspot.com/ Member Level: Diamond
Rating:  Points: 7
|
Introduction
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 declared
is 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 LocalTempTableTestSP
as begin
create table #LocalTempTable (ID int,[Name] char(30) )
insert into #LocalTempTable values (1,'Venkat')
end
Am Creating another SP Which calls the above created Temporary table(#LocalTempTable)
create procedure LocalTempTableTestSP1
as begin
select * from #LocalTempTable
end
Now, i am trying to executing the first Stored Procedure
exec LocalTempTableTestSP
A Temporary table is created and now i am trying to execute the Second SP,
exec LocalTempTableTestSP1
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 GlobalTempTableTestSP
as begin
create table ##GlobalTempTable (ID int,[Name] char(30) )
insert into ##GlobalTempTable values (1,'Venkat')
end
Creating another SP Which calls the above created Temporary table(#GlobalTempTable)
create procedure GlobalTempTableTestSP1
as begin
select * from ##GlobalTempTable
end
Now, i am trying to executing the first Stored Procedure
exec GlobalTempTableTestSP
A Global Temporary table is created and now i am trying to execute the Second SP,
exec GlobalTempTableTestSP1
It will display the result as "1" "Venkat"
Limitations andAlternative 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.
|
Responses
|
No responses found. Be the first to respond and make money from revenue sharing program.
|
|