Reputation: 11
I am trying my hands on global temporary table. As per my understanding global table should be accessible by other users and connection once it is created. Once all the references to the global temp table is closed then the table is dropped.
But in practice I can not see this happening. I opened two sql server sessions created a global temp table and then tried a select command from the other session so both of them are active.
Now once I close the session which was used to create the global temp table the table is dropped.
Why is that so even if there is another active session referencing the global temp table?
Upvotes: 1
Views: 174
Reputation: 453908
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
The other session isn't actively referencing the temp table.
It takes locks whilst reading it and then releases them as soon as the table is read.
You would need to run it inside a transaction at at least REPEATABLE READ
isolation level for the other session to be counted as referencing it and preventing the drop.
CREATE TABLE ##T
(
X INT
)
BEGIN TRAN
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SELECT *
FROM ##T;
RAISERROR('Waiting ...',0,1) WITH NOWAIT;
WAITFOR DELAY '00:00:10'; --Now can close the other session
SELECT *
FROM ##T; --And the table should still be there
COMMIT
Upvotes: 2