Global temp table in SQL

Like temp table you can create the global temp table using the ## token before the table name. Right. What is the global temp table? The global table is one, which is visible to all the active sessions. Let us say when the global table is created there are 12 users (Or connection. Remember it includes you client code connecting to the database).  The global temp table is available for all 12 users. Now consider the situation below that will make you understand this:

 

1) All 12 Users active.

The Global table is available for all the 12 users

 

2) 3 Users disconnected and a new user, say B is connected to SQL Server.

The Global table is still available for remaining 9 Users as well as the newly connected user/Connection B

 

3) All users disconnected except B

The Global table is still available as the user B is active. Note that this user established a session when the global table is alive. That means, he is also possibly using it. So SQL still allow Global temp table to be alive.

 

4) User/Connection B is also terminated and there is no user at present.

Global temporary table is deleted and it is no longer available.

 

Below is the usage of the Global temp table and note down the ##sign before the table name.

 

 

The QLQuery4 window is make a connection to SQL Server and the “into ##” statement is creating the global temporary table. Now, I opened one more Query window QLQuery5 and this will create one connection to the SQL server. Then the global table created is accessed in that session:

 

 

It proves that the Global table is accessible by other connection also. Think like this that the second query window (QLQuery5) is opened in a different machine and the global table is accessed. Also note that the table is Global and temporary and it does not belongs to any schema.  You can access this table when you are in NorthWnd or Pubs or master. It does not matter.

 

Now close all the SQL Window (I am assuming that you do not have any client application that still maintains connection to the server). Execute the query shown in the QLQuery5 by opening a new session. What happens? Object does not exist, right?

 

1) Once you close the entire query window, the global temp table ##EmpTemp is deleted

2) When you opened a new query window, a new session is created and Global table becomes no more for it.