5

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 ?

DatumPoint
  • 419
  • 4
  • 21
Shekhar Dalvi
  • 209
  • 6
  • 15
  • How do you close session 52 ? – ceth Jun 24 '14 at 06:00
  • Please refer the same in the below link for better idea: http://stackoverflow.com/questions/3887989/global-temporary-tables-in-sql-server – knkarthick24 Jun 24 '14 at 06:07
  • "Other sessions referencing the table" means that the other sessions must actually have a lock on the table. – Martin Smith Jun 24 '14 at 06:24
  • If the wording `sessions referencing this session` is taken from somewhere - since it seems to cause you confusion - it would help if you linked to where you read it. Usually the terminology would be along the lines of `sessions referencing the table`, not `session`. – Damien_The_Unbeliever Jun 24 '14 at 06:24
  • @Damien_The_Unbeliever – Shekhar Dalvi Jun 24 '14 at 06:39
  • @demas, I close the query window in which i have created the table to close session. – Shekhar Dalvi Jun 24 '14 at 06:50
  • @Damien_The_Unbeliever, that's exactly what my understanding is. 'sessions referencing the table' if it is so then the table should not released after closing of 'Session 52' since 'Session 57' still have reference to it. – Shekhar Dalvi Jun 24 '14 at 06:50

2 Answers2

5

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.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • It doesn't have to be actively executing a statement. It just needs to hold a lock. For transactions at higher isolation levels than read committed (or using equivalent hints) this could be for a statement executed earlier. – Martin Smith Jun 24 '14 at 22:03
3

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.

Ajwad
  • 53
  • 1
  • 7