SQL Server Global Temporary Table Visibility

By: Sergey Gigoyan   |   Updated: 2015-09-22   |   Comments   |   Related: 1 | 2 | 3 | 4 | 5 | > Temp Tables

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:

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:

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.

Next Steps

• Keep in mind these examples and this behavior with Global Temporary Tables in SQL Server for your next project.
• Check out these related tips:
  • https://technet.microsoft.com/en-us/library/ms186986(v=sql.105).aspx
  • https://msdn.microsoft.com/en-us/library/ms174979.aspx
  • https://technet.microsoft.com/en-us/library/ms189084(v=sql.130).aspx
  • Differences between SQL Server temporary tables and table variables
  • SQL Server Temp Table vs Table Variable Performance Testing
  • Create SQL Server temporary tables with the correct collation

About the author

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