Local vs Global SQL Server Temporary Tables

Problem

Temp tables are temporary in the sense that they exist for as long as the database connection which created and populated them exists.  After the connection is closed, the temp table drops from SQL Server.  You can also explicitly drop a temp table even when the connection remains open (for example, when the values in the temp table are no longer needed).  There are two varieties of temp tables.  Local temp tables are only accessible from their creation context, such as the connection.  Global temp tables are accessible from other connection contexts.  Both local and global temp tables reside in the tempdb database.  In this section we will cover each of these concepts.

Solution

For the examples below we are using the AdventureWorks2014 database. Download a copy and restore to your instance of SQL Server to test the below scripts.

Creating and Populating SQL Server Local Temp Tables

A local temp table name begins with a single # sign.  Although you can create a local temp table from any database context, a local temp table always resides in the tempdb database.  Because a local temp table is a database table, you must drop any prior version of a local temp table before you can create and populate a fresh version of a local temp table.

You can create and populate a local temp table just like any other database table.  For example, you can use a select statement  with an into clause.  All other select statement clauses besides the into clause define a result set for populating a local temp table.  The select list items specify columns for the local temp table.  The into clause argument specifies the name of the local temporary table.

The following script indicates some syntax for creating three local temp tables.

• The use statement indicates the code operates in the context of the Temporary_Data_Stores_Tutorial database.  Several sections of this report use this database.  In the following T-SQL script, the name of the database is not critical to the operation of the code.  Therefore, you can use any other database that you prefer.
• Next, three try…catch blocks drop prior local temporary table versions if they exist with the names of #married_male, #married_female, and #not_married.
• Each of the three local temp tables are based on subsets from the Employee table of the HumanResources schema in the sample. The subsets are specified by setting(s) in the where clause for the select statement creating and populating each local temp table.
• The into clause within each select statement designates the local temp table name for the result set developed by the rest of the select statement syntax.

USE [Temporary_Data_Stores_Tutorial]
GO
-- drop local temp tables
BEGIN TRY
  DROP TABLE #married_male
END TRY
BEGIN CATCH
  PRINT '#married_male not available to drop'
END CATCH
BEGIN TRY
  DROP TABLE #married_female
END TRY
BEGIN CATCH
  PRINT '#married_female not available to drop'
END CATCH
BEGIN TRY
  DROP TABLE #not_married
END TRY
BEGIN CATCH
  PRINT '#not_married not available to drop'
END CATCH
-- populate local temp table for married, male employees
SELECT
  [BusinessEntityID] INTO #married_male
FROM [AdventureWorks2014].[HumanResources].[Employee]
WHERE [MaritalStatus] = 'M'
AND gender = 'M'
-- populate local temp table for married, female employees
SELECT
  [BusinessEntityID] INTO #married_female
FROM [AdventureWorks2014].[HumanResources].[Employee]
WHERE [MaritalStatus] = 'M'
AND gender = 'F'
-- populate local temp table for not married employees
SELECT
  [BusinessEntityID] INTO #not_married
FROM [AdventureWorks2014].[HumanResources].[Employee]
WHERE [MaritalStatus] != 'M'

The following screen shot displays the contents of the Messages tab from SSMS.  There is no Results tab because the SELECT statements create and populate local temporary tables instead of populate result sets that can be displayed.  The code generates three result sets for creating and populating local temp tables.  The script creates three result sets with row counts of 97, 49, and 144 rows each.  The sum of these row counts equals the total number of rows in the Employee table within the HumanResources schema of the AdventureWorks2014 database.

The preceding script works with three databases.

• The USE statement designates the Temporary_Data_Stores_Tutorial database.  This is the default database for the operation of the code in the script.
• A link at the end of this section designates one place for downloading the AdventureWorks2014 database along with releases of the AdventureWorks databases for other versions of SQL Server besides SQL Server 2014.  The AdventureWorks2014 database is explicitly relevant to the preceding script because the code uses a three-part name to designate a source table name in the from clause.  Without the database name part of the three-part name in the from clause, the code would default to searching the HumanResources schema for the Employee table in the Temporary_Data_Stores_Tutorial database.
• The third database implicitly referenced in the preceding script is the tempdb database.  Any table whose table name begins with a # symbol resides in the tempdb as local temp table for the current database connection.

Creating and Populating SQL Server Global Temp Tables

The following code demonstrates how to create and populate global temporary tables.  If you contrast the following script with the preceding script, it is easy to spot that table names begin with two leading hash signs (##).  In the following script, the global table names are ##not_married and ##married.  Other than the leading double leading hash sign, the process for creating and populating a global temp table is the same as for a local temp table.

-- drop global temp tables
BEGIN TRY
  DROP TABLE ##not_married
END TRY
BEGIN CATCH
  PRINT '##not_married not available to drop'
END CATCH
BEGIN TRY
  DROP TABLE ##married
END TRY
BEGIN CATCH
  PRINT '##married not available to drop'
END CATCH
-- not married employees in global temp
SELECT [BusinessEntityID] INTO ##not_married
FROM [AdventureWorks2014].[HumanResources].[Employee]
WHERE [MaritalStatus] != 'M'
-- married employees in global temp
SELECT [BusinessEntityID] INTO ##married
FROM [AdventureWorks2014].[HumanResources].[Employee]
WHERE [MaritalStatus] = 'M'

After running the preceding script, you generate two global tables.  One of these is for all married employees; this table has 146 rows.  The other global table is for all employees who are not married; this table has 144 rows.  The sum of row counts for these two global temp tables matches the sum of row counts for the three local temp tables. 

Listing SQL Server Local and Global Temporary Tables

As an administrator or even an advanced user, you may sometimes find it useful to list all the global and temporary tables in the tempdb database.  Anyone can query objects in tempdb (unless permission is explicitly revoked, which is not recommended).  The set of all tables in tempdb can be obtained by referencing tempdb.sys.tables in the from clause of a select statement.  The tables in tempdb can be more than just global and local temp tables.  Therefore, you need to limit the result set with a where clause to list just global temp tables or local temp tables.

The following script provides the syntax for listing just global temp tables or local temp tables.  The filter for listing global temp tables is to find all tempdb tables beginning with ##.  A two-part filter is required for listing local temp tables.  The local temp table names must begin with # but not begin with ##.

-- list global temp tables
SELECT
  name,
  type,
  type_desc,
  create_date
FROM tempdb.sys.tables
WHERE name LIKE '##%'
-- list only local temp tables
SELECT
  name,
  type,
  type_desc,
  create_date
FROM tempdb.sys.tables
WHERE name LIKE '#%'
AND name NOT LIKE '##%'

The following display shows the result sets from the preceding two SELECT statements.  The top result set is for global temp tables with names of ##not_married and ##married.  The bottom result set is for local temp tables with names of #married_female, #married_male, and #not_married.

SQL Server allows users to assign the same name to two different local temp tables – so long as the tables are from different connections.  If this happens, then the beginning part of the both local temp tables will have the same name.  However, the trailing part of the name will be distinct for each local temp table with the same beginning part.  SQL Server tracks the two different local temp tables for each connection based on the trailing part of the name.  In this way, SQL Server can use the proper source local temp table from two different connections where each local temp table may have different data in its connection.

Referencing SQL Server Local and Global Temp Tables in the Same Connection

When you create local temp tables in a connection, then you can reference local temp tables in the same connection.  Local temp tables created in one connection cannot be referenced from a different connection. 

Global temp tables can be referenced from the same connection or a different connection so long as the global temp table has not gone out of scope.

The database connection is the same when a temp table is created and referenced from a script within the same SSMS tab.  The database connection is different when it is created in one SSMS tab, but it is referenced in a different SSMS tab.  Because each T-SQL script file resides in its own SSMS tab, you can load and run scripts from the same or different T-SQL script files and evaluate the impact of connections on the ability to reference local and global temp tables.  A global temp goes out of scope when the tab in which it was created is closed, and there is no other active programmatic reference to it from another tab.

The following script gives simple examples of querying local temp tables and global temp tables. The queries for both local and global temp tables, respectively, counts the rows across all local tables and all global tables.  As you can see from the corresponding Results pane both queries return the same count of employees – namely, 290.  This implies the local temp tables are referenced from the same connection as the one used to create the local temp tables.  The global temp tables can be referenced in the same or a different tab so long as the connection used to create the global temp table is still open or there is at least one other active programmatic reference to the global temp table.

-- count of rows for three local temp tables 
-- with all employees in AdventureWorks2014
SELECT COUNT(*) count_from_local_temp_tables
FROM (SELECT * FROM #married_female
      UNION
      SELECT * FROM #married_male
      UNION
      SELECT * FROM #not_married) married_female_married_male_not_married
-- count of rows for two global temp tables 
-- with all employees in AdventureWorks2014
SELECT COUNT(*) count_from_global_temp_tables
FROM (SELECT * FROM ##not_married
      UNION
      SELECT * FROM ##married) not_married_married

Referencing SQL Server Local and Global Temp Tables in Another Connection

When the two SELECT statements are run from a different connection than the one in which they were created, then the outcome is different. 

So, if the local and global temp tables were created in one tab (one connection) and run in a different tab (second connection), then the outcome would be different.  A GO statement should follow the first SELECT statement to allow the connection to run both SELECTs even after a failure in the first SELECT statement.  The GO statement facilitates re-starting after an error.

The first query counting the rows in the set of three local temp tables fails.  This is because the SELECT query referencing local temp tables runs from a different connection than the connection used to create the local temp tables.  The following Messages tab points attention to this failure with Msg 208.   The error message indicates that the #married_female local temp table is not available in the second connection.  This is because the #married_female local temp table is only available in the connection for the one in which it was created.  Of course, the other two local temp tables (#married_male and #not_married) are also missing from the second tab, but the first error aborts the SELECT statement in the batch.

The second SELECT statement operates in a different batch because of the GO statement following the first SELECT statement.  Also, this second SELECT statement queries two global temp tables.  Because the second SELECT statement operates on global temp tables, it succeeds although it is from a different connection than the one used to create the global temp tables.  The following Results tab image displays the outcome from the second query.  This query returns a total count of employees of 290, which is known to be the correct number of employees in the Employees table of the HumanResources schema in the AdventureWorks2014 database.

Local and Global Temp Tables Inside and Outside of Stored Procedures

Even within the same tab, global and local temp tables can operate differently.  A stored procedure is a well-known example of how the two types of temp tables operate differently.  Local temp tables created in a stored procedure within a connection cannot be referenced outside of the stored procedure.  However, global temp tables created inside a stored procedure can be referenced from outside the stored procedure.

The following script demonstrates how references to local temp tables and global temp tables operate when the temp table is created in a stored procedure, but it is referenced outside the stored procedure.  Notice that the for_local_and_global_temp_tables_demo stored procedure creates two temp tables.  One table named #married_male_from_proc is a local temp table.  Another table named ##not_married_from_proc is a global temp table.

The script creates the for_local_and_global_temp_tables_demo stored procedure in the Temporary _Data_Stores_Tutorial database with a create proc statement.  The stored procedure is very simple.  One select into statement creates and populates #married_male_from_proc, a local temp table.  Another select into statement creates and populates ##not_married_from_proc, a global temp table.

After the create proc statement, an exec statement invokes the for_local_and_global_temp_tables_demo stored procedure in its own batch.

Next, a SELECT statement in its own batch attempts to perform a SELECT statement on the local temp table created in the stored procedure.  Even though local temp table created in the stored procedure is from the same tab as the select statement querying the temp table outside the stored procedure, the query fails.  The Messages tab shows the error is for an invalid object name.  This follows from the fact that the local table name, #not_married_from_proc, has a scope that is restricted to the stored procedure.

On the other hand, the SELECT statement for the global temp table named ##not_married_from_proc succeeds outside the stored procedure.  The global temp table is not restricted to a scope inside the stored proc.  It returns a count of the employees who are not married.  This count is validated by another query that runs directly against the Employee table in the HumanResources schema of the AdventureWorks2014 database.

USE [Temporary_Data_Stores_Tutorial]
GO
-- this is to drop a local table
BEGIN TRY
  DROP TABLE #married_male_from_proc
END TRY
BEGIN CATCH
  PRINT '#married_male_from_proc not available to drop'
END CATCH
-- this is to drop a global temp table
BEGIN TRY
  DROP TABLE ##not_married_from_proc
END TRY
BEGIN CATCH
  PRINT '##not_married_from_proc not available to drop'
END CATCH
BEGIN TRY
  DROP PROC for_local_and_global_temp_tables_demo
END TRY
BEGIN CATCH
  PRINT 'for_local_and_global_temp_tables_demo stored proc not available to drop'
END CATCH
GO
CREATE PROC for_local_and_global_temp_tables_demo
AS
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;
  -- Insert statements for procedure here
  -- populate local temp table for married, male employees
  SELECT [BusinessEntityID] INTO #married_male_from_proc
  FROM [AdventureWorks2014].[HumanResources].[Employee]
  WHERE [MaritalStatus] = 'M'
  AND gender = 'M'
  -- populate global temp table for not married employees
  SELECT [BusinessEntityID] INTO ##not_married_from_proc
  FROM [AdventureWorks2014].[HumanResources].[Employee]
  WHERE [MaritalStatus] != 'M'
END
GO
-- execute the stored procedure to create
-- local and global temp tables
EXEC for_local_and_global_temp_tables_demo
GO
-- attempt to display local temp table
-- original created in a stored proc
SELECT COUNT(*)
FROM #married_male_from_proc
GO
-- attempt to display global temp table
-- original created in a stored proc
SELECT COUNT(*) count_of_not_married_from_global_temp_table
FROM ##not_married_from_proc
-- not married employees from Employee table
-- in HumanResources schema
-- of the AdventureWorks2014 database
SELECT COUNT(*) count_of_not_married_from_Employee_table
FROM [AdventureWorks2014].[HumanResources].[Employee]
WHERE MaritalStatus != 'M'
GO

Next Steps

Here are some links to resources that you may find useful to help you grow your understanding of content from this section of the tutorial.

• AdventureWorks sample databases
• Install Your Own Copy of the SQL Server AdventureWorks2014 Database
• Differences between SQL Server temporary tables and table variables
• Introduction to Temporary Tables in SQL Server
• Temporary Tables in SQL Server
• SQL Server Temporary Tables
• How to get a list of global temporary tables in the current database?
• Is there a way to get a list of all current temporary tables in SQL Server?
• tempdb database in Parallel Data Warehouse