CODEWITHSUNDEEP
sql-server
Shekhar Dalvi
Shekhar Dalvi

Reputation: 209

When do Global Temporary Tables get destroyed in SQL Server?

I am confused about the usage of global temporary table. I have created a global temporary table

//Session 52 : Creator Session

Create Table ##temp(i int) 
Insert Into ##temp(i) Values(1),(2),(3)
Select * From ##temp

//Session 56 

Select * From ##temp

//Session 57 : last session which holds the refference of ##temp 

Select * From ##temp

Now if I close the Session 52, the ##temp table destroys.

I believe global temp table released in the following cases

  1. It's explicitly removed by any sessions
  2. The creator session closed and there is no other sessions referencing this session.

I am confused about sessions referencing this session(creator Session) what does this mean ?

Upvotes: 5

Views: 7039

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239724

From CREATE TABLE:

Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended

Although as a personal nit-pick, I'd have used after instead of when in that last sentence.

So, per your example, session 57 keeps the global temporary table around so long as it's actually executing a statement that relies upon the table. As soon as that statement is complete - so the session has moved onto another statement or it's sitting idle - the global temp table will be dropped.

Upvotes: 5

Ajwad
Ajwad

Reputation: 53

Global temporary tables are only destroyed when the current user is disconnected and all the sessions referring to it are closed. This means that the global temp table will not be destroyed unless all the sessions related to the user(session creator) are not closed.

Upvotes: 3

Related Questions