September 17, 2012 at 3:41 am
aaron.reese (9/14/2012)
do you mean global ##temptablesThese persist between stored procedures until the session is ended or the connection is dropped.
you can fill a ##temptable in one procedure and access the data in another, but it is generally not good practice.
That is not exactly correct...
First of all, it can be done even with local #temptable. If you create this table in one proc you can use it in a subsequent "chained" proc. Try this:
create proc p1
as
begin
create table #tP (a int)
exec p2
select * from #tP
end
go
create proc p2
as
begin
insert #tP select 11111
end
go
exec p1
go
The second thing about global ##temptables is they are not only accessible by the "chained" proc executed within the one which creates a global temp table, but also it can be accessed by any query in other connection until the connection, which used to create this global temptable is closed or any other process dropped it.
September 17, 2012 at 4:49 am
I thought the ## temp tables persisted until they were explicitly dropped from the TempDb, or the SQL service was restarted.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
September 17, 2012 at 5:23 am
Jason-299789 (9/17/2012)
I thought the ## temp tables persisted until they were explicitly dropped from the TempDb, or the SQL service was restarted.
Just try:
1. Open query window 1 and type: create table ##t (a int)
2. Open query window 2 , type and execute: select * from ##t
3. Close query window 1.
4. Try to execute the query in window 2
According to BoL:
Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server.
Now, there is a small details, even so the query window 2 still has a SELECT query, it doesn't really reference the table at the moment when you closed the windows 1, therefore ##t is dropped.
However all will change if you will start again and instead of simple SELECT in as second query window execute:
begin tran
insert ##t select 1
select * from ##t
...
Now that is really cool one. You may try yourself:
1. Open query window 1 and type: create table ##t (a int)
2. Open query window 2, type and execute:
begin tran
insert ##t select 1
3. Open query window 3, type and execute: select * from ##t. - it will not return as transaction in #2 is still open
4. Close window 1. Query in #3 is still waiting for transaction in #2 ...
5. Commit or roll back transaction in #2. Query in #3 will return (something or nothing depending of what you have done- commit or rollback
6. Try to execute query again in window #3
😉
September 17, 2012 at 6:02 am
Thanks Eugine, as I said I thought they were persisted even after the process that created them was closed,
I was actually confused with creating a specific fixed table,Eg Create Table Tempdb..Test (Col1 int) within the temp db.
so added that to my knowledge base, along with following the Natural Key vs IDENTITY thread. 😉
_________________________________________________________________________
SSC Guide to Posting and Best Practices
September 17, 2012 at 6:32 am
Jason-299789 (9/17/2012)
Thanks Eugine, as I said I thought they were persisted even after the process that created them was closed,I was actually confused with creating a specific fixed table,Eg Create Table Tempdb..Test (Col1 int) within the temp db.
so added that to my knowledge base, along with following the Natural Key vs IDENTITY thread. 😉
Yeah, I think that this discussion (about Global ##Temp tables) is quite important. My experience tells me that it's quite common for SQL developers starting using global temp table without complete understanding of global temp tables usage aspects eg. their life-span and its pre-conditions. Hopefully, the shown example will give more thoughts into it.
Regarding of the Natural Key vs IDENTITY thread, I feel a bit guilty there... We I have hijacked someone else thread - not very good, isn't it! :blush:
September 17, 2012 at 6:43 am
I've seen the same thing in regards to ##tables.
I dont know its good to have those types of discussions.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply