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
😉