Local and global temporary tables in SQL Server

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 -- gt  System Databases -- gt  tempdb -- gt  Temporary Tables

User · Answer

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

User · Answer

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

User · Answer

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

[sql-server] Local and global temporary tables in SQL Server

Examples related to sql-server

Examples related to temp-tables

Examples related to global-temp-tables