Todd Johnson (6/10/2009)
I enojyed reading your article. It verfied some of the rumors I have heard over the years.
Thank you
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?
If you look at 3.b, I go through an example of how the global temp table goes away once the connection is ended.
Now, if you're running this from a query window, and the code in that window "crashes" (but the window aka the connection is still active), then the table will still reside. As long as the connection that created the temp table (local or global) is still open, then the temp table will stay around until explicitely dropped.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes