SQL Server Global Temporary Table Visibility

By:   |   Comments   |   Related: 1 | 2 | 3 | 4 | 5 | > Temp Tables


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.


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

	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

	Value INT


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

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

	SET @i=@i+1


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").

		@val INT =0

WHILE (@i <= 1000000)

	SELECT @val=Value FROM ##tmpTest WHERE Value=@i
	SET @i=@i+1

	SELECT @val

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.


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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

get free sql tips
agree to terms