Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Global Temporary Table Visibility


By:   |   Read Comments   |   Related Tips: 1 | 2 | 3 | 4 | More > T-SQL

Problem

Sometimes SQL Server database developers come across cases when they need to use global temporary tables. Query optimization, managing staging data, working with the same temporary data from different sessions, etc. are examples of situations. Understanding global temporary table visibility is essential when using these objects, especially when the global temporary table is used from different sessions.

Solution

Global temporary tables are stored in the tempdb database. They are identified by double number signs before their name:

CREATE TABLE ##tmpTest
(
	Value INT
)

Once global temporary tables are created we can find them in the tempdb database:

Global Temporay Table in SQL Server Management Studio

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
(
	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:

Global Temporary Table Visible in 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.

Multiple Global Temporary Tables Error

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":

Invalid Object Name ##tmpTest

Successfully retrieve data from SQL Server Global Temporary Tables

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.

Next Steps


Last Update:






About the author
MSSQLTips author Sergey Gigoyan Sergey Gigoyan is a database professional with more than 6 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools