Todd Johnson (6/10/2009)
I enojyed reading your article. It verfied some of the rumors I have heard over the years.I was wondering if you could clarify one thing for me in your document. Under Temporary Tables --> 3) --> a. "Global temporary tables are dropped when the session that created it ends, and all other sessions have stopped referencing it."
Don't you have to explicitly drop global temp tables unless SQL Server is restarted which re-creates tempDB? If you have a proc crash which built a global temp table then the next time the proc is run then you will get a duplicate object error during execution until you drop the object, right?
I was wondering if I have been doing something wrong over the years. I typically used global temp tables to build cross-tab queries in the database for large amounts of data.
Thanks again,
T-Odd
Try this:
Open two sessions in SSMS, in this first one, run this:
create table ##T (
ID int primary key);
insert into ##T (ID)
select 1;
raiserror('Deliberate error', 20, 1) with log;
In the second one, run this:
select *
from ##T;
You'll find that the global temp table is not there, and will get an "Invalid object" error on the second query.
I don't have an SQL 2000 server to try that on, but I've tested it on 2005 and 2008 and it does what it's supposed to there. I think I've tested it on 2000 before and found that it also works there, but I can't verify that today.
The key is that the error has to be severe enough to kill the connection. Try it with severity 16,
and the connection will still be open, so the global temp will still be valid.
Edit: WARNING: Do not play with high-severity errors on a production server!!! Probably shouldn't have to mention that, but I'm going to anyway, just in case. I have a desktop instance of Dev Edition, with a ProofOfConcept database that's throwaway, and I do this kind of testing there.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon