Visibility of Global Temporary Table

Visibility of Global Temporary Table

When we are saying we have to capture data in temporary table to manipulate internal operation we have two choice.

1. Table Variable
2. Temp Table

When we have to use same temp table including multiple session and accessed by multiple user in that case we have limitation that we can’t use normal temp table. We have to use Global temporary table.

Global identified when we are declaring using (“##”) sign,

Example:-

##TmpTest  (Global Temp table)

This table deleted when session closed and if any other user or active session not using this table, in that case this table automatically deleted.

If we want to forcedly delete either we can use Drop statement for deleting the table.

We can see this global table in Tempdb object explorer. You can observe all temp table in same explorer.

create table ##TmpTest

(id int identity(1,1),

name varchar(100)

)

insert into ##TmpTest values ('Testing global values');

When you will try to create same temp table you will not be able to create, you will get error message table already exists.

Msg 2714, Level 16, State 6, Line 1

There is already an object named '##TmpTest' in the database.

Even you try to create from different machine in same database you will not be able to do so. In case someone from other machine trying to access this table and you  are deleting same table in that case you will not able to delete in case user trying to update or update operation in process.

Conclusion:-

After creation, global temporary tables become visible to any user and any connection. They can be manually dropped with DROP TABLE command. Global temporary tables are automatically dropped when the session that create the table completes and there is no active references to that table. If the creator session ends, but there is an active references to that table from other sessions, SQL Server waits until the last T-SQL statement of the reference session completes and then table is dropped. Also during the execution of reference session statements, the table becomes unavailable for new connections.

Let's understand this behavior with concrete examples. We create a global temporal table, insert data into it and illustrate its scope with examples. Let's open a new query window (Query 1) in SSMS and create global temporary table and insert data.

--Query 1

IF (OBJECT_ID('tempdb..##tmpTest') IS NOT NULL)

DROP TABLE ##tmpTest

CREATE TABLE ##TmpTest

(

id int identity(1,1),

Value INT

)

DECLARE @i INT =1

WHILE (@i <= 1000000) --Value 1000000 is used to facilitate testing process, please be careful in choosing this value for your server to avoid overloading the server

BEGIN

INSERT INTO ##TmpTest(Value) VALUES(@i)

SET @i=@i+1

END

Now, let's connect to the same SQL Server instance with a different user, open new query window (Query 2) and run the following command:

--Query 2

SELECT TOP 1 * FROM ##TmpTest

As we can see, table is visible from other sessions:

Now we open a new query window (Query 3), and run the following code:

--Query 3

SELECT * FROM ##TmpTest

While "Query 3" is running, we close "Query 1". When running "Query 3" we execute "Query 2" again. As we can see "Query 3" is successfully finished and returned all rows from ##TmpTest table. Execution of "Query 2" runs until "Query 3" is finished and then fails.

What is happening? When we close "Query 1" there are still active connections referring to ##tmpTest in "Query 3", so why didn't SQL Server drop ##tmpTest and why does "Query 3" complete successfully returning all rows in ##tmpTest table. "Query 2" is being executed after closing "Query 1", so it can't access to ##tmpTest. After completion of "Query 3" there was no active references on ##tmpTest, therefore it is dropped, and "Query 2" fails.

Now, let's test what happens, when the other session refers to a global temporary table and there is more than one query which uses the table (i.e. not a single query). We run "Query 1" again (##tmpTest created and data inserted). Now we open a new query window, and run the following query ("Query 4").

DECLARE @i INT =0,

@val INT =0

WHILE (@i <= 1000000)

BEGIN

SELECT @val=Value FROM ##tmpTest WHERE Value=@i

   

SET @i=@i+1

SELECT @val

END

While "Query 4" is running, we close "Query 1". As a result "Query 4" fails, but we can see data which is selected before closing "Query 1":

The reason is that "Query 4" refers to ##tmpTest table not with a single T-SQL command. It refers to it in each iteration in the while loop. So, when "Query 1" is closed (##tmpTest dropped), SQL Server waits until "Query 4" completes its latest T-SQL statement which refers to ##tmpTest (in our example "SELECT @val=Value FROM ##tmpTest WHERE Value=@i") and then drops the ##tmpTest table. In the next loop, when it tries to execute this statement, the error occurred, because the temporary table has already been dropped.

Conclusion

As we can see with real examples, global temporary tables are visible for all sessions and all users until the session which created it, is completed. When the creator session ends and there is no references to the global temporary table, then it automatically drops. If there are still references to the table, after creator session ends, SQL Server waits until the latest T-SQL reference statement completes and then drops the table.