CODEWITHSUNDEEP
sql-server
LostWorld
LostWorld

Reputation: 11

Global Temporary tables

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

Answers (1)

Martin Smith
Martin Smith

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.

Session 1

CREATE TABLE ##T
(
X INT
)

Session 2

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

Related Questions