Local and global temporary tables in SQL Server

Question

What is the difference between local and global temporary tables in SQL Server?

Here are some summaries details about this , [Click Here](http://jayeshsorathia.blogspot.com/2012/03/beginning-sql-difference-between-local.html) — Jayesh Sorathia, Mar 28 '12 at 11:52
Be careful when using table variables. If you use them in a query, they can lead to serious performance problems with your query plan as they're not indexed. — , Jan 03 '13 at 03:25
Actually, temp tables can be indexed, if needed, but that takes time and resources, too, so it still could cause performance or resource issues. — Andrew Steitz, Sep 12 '16 at 16:32

score 386 · Answer 1 · edited Aug 10 '14 at 14:30

386

Table variables (DECLARE @t TABLE) are visible only to the connection that creates it, and are deleted when the batch or stored procedure ends.
Local temporary tables (CREATE TABLE #t) are visible only to the connection that creates it, and are deleted when the connection is closed.
Global temporary tables (CREATE TABLE ##t) are visible to everyone, and are deleted when all connections that have referenced them have closed.
Tempdb permanent tables (USE tempdb CREATE TABLE t) are visible to everyone, and are deleted when the server is restarted.

edited Aug 10 '14 at 14:30

answered May 27 '10 at 12:34

Anthony Faull

17,549
5
55
73

62

Also worth pointing out: Local temporary tables are deleted when the scope that created them is closed. So, if you create a local temp table inside a sproc, and then try and access it outside that sproc - it won't exist. – Apr 24 '13 at 03:59
+1 for Will. I was trying to use a local temporary table as an optimizer and I was trying to use a stored procedure as a "create and populate if it doesn't exist" initializer. As you say, it doesn't work unless you use a global temporary table instead. – quillbreaker Dec 24 '13 at 21:06
11

"are deleted when all connections that have referenced them have closed" - what does "that have referenced them" mean exactly? If a StoredProc from one connection#1 creates a ##TempTable can I see it from another connection#2 say 10 mins later (if that connection#2 was active at table creation time?) ANSWER: Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. (see more on this page in different answer) – tbone Nov 26 '14 at 04:28
I tried using a stored procedure to create local temporary tables (#t) needed by subsequent logic, however it turned out that the parent stored procedure had to create them in order for them to be available to the children stored procedure calls. This was a sad thing, because we have a bunch of stored procedures that have to set up the tables the same way and call down into common sprocs. Will global temp tables work in this case where the children calls have access to tables created by a sibling? We're using SQL Server 2008. – Brandon Sep 16 '16 at 13:33
@Brandon Yes, I believe you can create a global temporary tables (##t) in one stored procedure, and subsequently access it from a second stored procedure. – Anthony Faull Sep 19 '16 at 18:42
@AnthonyFaull Hmm. I was just thinking--what I really want is a temp table that can only be accessed from the current session, but created in a child stored procedure call. ##t would open it up to all connections, which would be problematic for my code. That's not your fault. I'm just going to have to find a different approach--if one is available. Am I missing an obvious use case? I wish TSQL lent itself to more code reuse than it does. – Brandon Sep 20 '16 at 15:12
1

@Brandon You're quite right. That is missing functionality. TSQL's support for proper scoping of transient data is quite sketchy. It's as if the language designers wanted everything to be global. And there is almost no support for closures. You could pass a cursor variable. But that's another can of worms because row-by-agonizing-row is no way to go. – Anthony Faull Sep 23 '16 at 06:48
Are you sure about that? I've used sprocs to create temporary tables specifically so that they are visible to the parent/calling procedure. Table variables are what you can't pass outward, but I'm pretty sure you can call a sproc that generates a local temp table, and it's still visible when the sproc returns because you're still in the same session. The temp table goes away only when the connection is closed. – Triynko Jun 04 '19 at 20:18

score 129 · Accepted Answer · edited Feb 23 '14 at 22:32

129

I find this explanation quite clear (it's pure copy from Technet):

There are two types of temporary tables: local and global. Local temporary tables are visible only to their creators during the same connection to an instance of SQL Server as when the tables were first created or referenced. Local temporary tables are deleted after the user disconnects from the instance of SQL Server. Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server.

edited Feb 23 '14 at 22:32

Blorgbeard

101,031
48
228
272

answered May 27 '10 at 12:06

Don

9,511
4
26
25

Great, helpful answer! I was looking for the specific information on if/when global temp tables were automatically cleaned up by SQL Server. – kwill Jun 25 '15 at 18:21
Very clear and concise answer. Can anyone think of a good use case for global temp tables? One that illustrates their purpose in contrast with the purpose of local temp tables? – Trevor Mar 31 '20 at 21:11
1

@Trevor: I had a use case for a global temp table. I collected some data in a temporary table and tried to send them via email with EXEC msdb.dbo.sp_send_dbmail ... witch will only work with a global temp table. – Michael W. Mar 09 '22 at 16:37

Vivek S. · Answer 3 · 2015-12-04T05:28:57.443

1.) A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.

Local temp tables are only available to the SQL Server session or connection (means single user) that created the tables. These are automatically deleted when the session that created the tables has been closed. Local temporary table name is stared with single hash ("#") sign.

CREATE TABLE #LocalTemp
(
 UserID int,
 Name varchar(50), 
 Address varchar(150)
)
GO
insert into #LocalTemp values ( 1, 'Name','Address');
GO
Select * from #LocalTemp

The scope of Local temp table exist to the current session of current user means to the current query window. If you will close the current query window or open a new query window and will try to find above created temp table, it will give you the error.

2.) A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection is closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.

Global temp tables are available to all SQL Server sessions or connections (means all the user). These can be created by any SQL Server connection user and these are automatically deleted when all the SQL Server connections have been closed. Global temporary table name is stared with double hash ("##") sign.

CREATE TABLE ##GlobalTemp
(
 UserID int,
 Name varchar(50), 
 Address varchar(150)
)
GO
insert into ##GlobalTemp values ( 1, 'Name','Address');
GO
Select * from ##GlobalTemp

Global temporary tables are visible to all SQL Server connections while Local temporary tables are visible to only current SQL Server connection.

Your definition of a global temp table is how I'd expect it to behave (coming from other DBs), but my testing shows that what actually happens in SQL Server is: _"Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them"_ — Nickolay, Apr 05 '18 at 14:58

score 12 · Answer 4 · answered May 27 '10 at 12:06

Quoting from Books Online:

Local temporary tables are visible only in the current session; global temporary tables are visible to all sessions.

Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped using DROP TABLE:

A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process which called the stored procedure that created the table.
All other local temporary tables are dropped automatically at the end of the current session.
Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.

Code Novice · Answer 5 · 2020-07-27T23:45:25.363

4

I didn't see any answers that show users where we can find a Global Temp table. You can view Local and Global temp tables in the same location when navigating within SSMS. Screenshot below taken from this link.

Databases --> System Databases --> tempdb --> Temporary Tables

edited Jul 27 '20 at 23:45

answered Jun 02 '20 at 21:31

Code Novice

2,043
1
20
44

score 2 · Answer 6 · answered Oct 13 '19 at 08:09

Local temporary tables: if you create local temporary tables and then open another connection and try the query , you will get the following error.

the temporary tables are only accessible within the session that created them.

Global temporary tables: Sometimes, you may want to create a temporary table that is accessible other connections. In this case, you can use global temporary tables.

Global temporary tables are only destroyed when all the sessions referring to it are closed.

Lukasz Szozda · Answer 7 · 2020-01-20T16:50:00.830

It is worth mentioning that there is also: database scoped global temporary tables(currently supported only by Azure SQL Database).

Global temporary tables for SQL Server (initiated with ## table name) are stored in tempdb and shared among all users’ sessions across the whole SQL Server instance.

Azure SQL Database supports global temporary tables that are also stored in tempdb and scoped to the database level. This means that global temporary tables are shared for all users’ sessions within the same Azure SQL Database. User sessions from other databases cannot access global temporary tables.
-- Session A creates a global temp table ##test in Azure SQL Database testdb1
-- and adds 1 row
CREATE TABLE ##test ( a int, b int);
INSERT INTO ##test values (1,1);

-- Session B connects to Azure SQL Database testdb1 
-- and can access table ##test created by session A
SELECT * FROM ##test
---Results
1,1

-- Session C connects to another database in Azure SQL Database testdb2 
-- and wants to access ##test created in testdb1.
-- This select fails due to the database scope for the global temp tables 
SELECT * FROM ##test
---Results
Msg 208, Level 16, State 0, Line 1
Invalid object name '##test'

ALTER DATABASE SCOPED CONFIGURATION
GLOBAL_TEMPORARY_TABLE_AUTODROP = { ON | OFF }
APPLIES TO: Azure SQL Database (feature is in public preview)

Allows setting the auto-drop functionality for global temporary tables. The default is ON, which means that the global temporary tables are automatically dropped when not in use by any session. When set to OFF, global temporary tables need to be explicitly dropped using a DROP TABLE statement or will be automatically dropped on server restart.

With Azure SQL Database single databases and elastic pools, this option can be set in the individual user databases of the SQL Database server. In SQL Server and Azure SQL Database managed instance, this option is set in TempDB and the setting of the individual user databases has no effect.

Local and global temporary tables in SQL Server

7 Answers7

Linked

Related