global temporary tables - why?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

global temporary tables - why??

Author

Topic

gdeconto
Posting Yak Master

107 Posts

Posted - 2003-05-27 : 16:10:32

saw in a couple of posts the suggestion of using global temporary tables to store the results of a stored procedure (ie a called stored procedure stores a resultset in a global temporary table which allows the calling stored procedure or tsql stmt to access that resultset)

I've never really used global temporary tables (gtt) and the documentation is slim to none (it tells me how but not why).

Would anyone out there be able to provide a brief paragraph that would describe where/why we would use a gtt??

I would think that this type of feature would be of really limited use in a multi-user environment, and dont see how it would be better than a local temp table.

thx in advance for any info

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-27 : 16:31:53

You answered your own question:

"which allows the calling stored procedure or tsql stmt to access that resultset"

Tara

gdeconto
Posting Yak Master

107 Posts

Posted - 2003-05-27 : 16:36:07

not really.

the same threads talked about how it would not work [well] in a multi-user environment, which is sort of my point.

I rarely work on something that only one person can use.

SamC
White Water Yakist

3467 Posts

Posted - 2003-05-27 : 16:37:00

If a temp table is automatically deleted on exit, can temp table #T1, created in procedure P1, be queried by procedure P2 if P1 calls P2?

OR - are temp tables visibile only to the creating procedure?

Sam

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2003-05-27 : 16:42:33

Yes, but it's on exit of P1 so P2 has access to temp tables in P1


create proc p1
as
create table #t1(c1 varchar(50))
insert #t1 values('I''m in p1')
exec p2
return
go
create proc p2
as
select c1 as 'From p2' from #t1 
return
go
exec p1
go
drop procedure p1,p2
go

HTH
Jasper Smith

0x73656c6563742027546f6f206d7563682074696d65206f6e20796f75722068616e6473203f27

gdeconto
Posting Yak Master

107 Posts

Posted - 2003-05-27 : 16:44:41

a local temp table (ie #t1) exists only as long at the stored procedure (transaction) that created it is running. if the sproc that creates it calls another, I think it will be visible to that called sproc.

a global temp table can exist (I think) outside of the stored procedure that creates it as long as it is still being referenced/used somewhere else. unfortunately, unless you have a dynamic naming system for the temp table, the same temp table exists for all users accessing the sproc at the same time.

gdeconto
Posting Yak Master

107 Posts

Posted - 2003-05-27 : 16:45:30

a local temp table (ie #t1) exists only as long at the stored procedure (transaction) that created it is running. if the sproc that creates it calls another, I think it will be visible to that called sproc.

a global temp table can exist (I think) outside of the stored procedure that creates it as long as it is still being referenced/used somewhere else. unfortunately, unless you have a dynamic naming system for the temp table, the same temp table exists for all users accessing the sproc at the same time.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-05-27 : 16:57:33

you can create a temp table in one SP, call another to populate it, call another to access the data, access the data from the first.
You can create a temp table in one sp, change it's structure in tha same or another then call another to access it. You cannot access the changed structure in the calling sp.
This will all work in a multi_user environment as the temp table is spid specific (it has a spid identifier included in the table name in temp db).

You can create a global temp in one sp and access it from any connection until all accessing connections are terminated.
All connections will access the same temp table.

Therefore global temp tables are useful for things like import/export via bcp or batch processes but not so useful for user processes.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-27 : 16:58:38

Jasper,

Isn't ##t1?

quote:

Yes, but it's on exit of P1 so P2 has access to temp tables in P1

create proc p1
as
create table ##t1(c1 varchar(50))
insert ##t1 values('I''m in p1')
exec p2
return
go
create proc p2
as
select c1 as 'From p2' from ##t1
return
go
exec p1
go
drop procedure p1,p2
go

HTH
Jasper Smith

0x73656c6563742027546f6f206d7563682074696d65206f6e20796f75722068616e6473203f27

Brett

8-)

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2003-05-27 : 17:31:33

I was (trying

) to just address Sam's question about "normal" temp tables spanning procedures rather than address the original question about global temp tables - so I did mean #t1

gdeconto
Posting Yak Master

107 Posts

Posted - 2003-05-27 : 17:36:38

hadn't realized that my original question would produce such activity so quickly.

ANYHOW, anyone got an answer for the original question? ie why/where would I use a global temp table rather than a local temp table. what use is it outside of a single-user system? etc.

SamC
White Water Yakist

3467 Posts

Posted - 2003-05-27 : 17:38:51

quote:

ANYHOW, anyone got an answer for the original question? ie why/where would I use a global temp table rather than a local temp table. what use is it outside of a single-user system? etc.

nr answered that question earlier...

quote:
Therefore global temp tables are useful for things like import/export via bcp or batch processes but not so useful for user processes.

gdeconto
Posting Yak Master

107 Posts

Posted - 2003-05-27 : 17:47:45

well, it does provide two examples of where.

any info on why it would be useful in bcp and batching?

I'm trying to see if this tool would benefit my code and if so where and how.

SamC
White Water Yakist

3467 Posts

Posted - 2003-05-27 : 18:15:52

Temporary tables have always met my needs, I've never had to resort to a global table (yet).

Sam

nr
SQLTeam MVY

12543 Posts

Posted - 2003-05-27 : 18:15:58

bcp creates a new connection to the server so cannot access temp tables but it can access global temp tables.

To create text files you can format your output into a gloval temp table (with a single column) then bcp it out.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources