In this post, I provide a Level 100 primer on the scope related considerations for local temporary tables.

While I have written about temporary tables a lot in the past (and the same has also been done by other writers), there are some questions that I keep encountering from the team or in various forums on the Internet which is why I am writing this post.

Temporary tables use the tempdb and come in two variants:

1. Local Temporary Tables
   • As the name implies, they are local to the connection that created them
   • Therefore, if multiple connections request creation of a temporary table with the same name, each connection gets it’s own unique copy of the temporary table
   • The local temporary tables are implicitly deleted when the connection is closed
   • To create a local temporary table, prefix the desired table name with a single hash (#) mark, e.g. A local temporary table for employees might be named #Employee or #HumanResources
2. Global Temporary Tables
   • These are global to the entire SQL Server instance and are therefore shared by all connections to the server
   • They are implicitly deleted when the last connection referencing them is closed
   • To create a global temporary table, prefix the desired table name with a double hash (##) mark, e.g. A global temporary table for employees might be named ##Employee or ##HumanResources

A demo on local temporary tables

The most important point that is missed out by most novice developers is that temporary tables are session specific. To briefly demonstrate this, allow me to follow the following steps:

1. Open SQL Server Management Studio and launch two (2) query editor windows
   • In each window, connect to the same database and same SQL Server instance
2. Run the following in window #1. This snippet creates a local temporary table and populates some records into it

--Window 01
USE AdventureWorks2012;
GO

--Create the local temporary table if it does not exist
IF OBJECT_ID('tempdb..#BasicEmployeeList','U') IS NULL
BEGIN
CREATE TABLE #BasicEmployeeList 
    ( BusinessEntityID INT          NOT NULL,
      JobTitle         NVARCHAR(50) NOT NULL,
      Title            NVARCHAR(8)      NULL,
      FirstName        NVARCHAR(50) NOT NULL,
      MiddleName       NVARCHAR(50)     NULL,
      LastName         NVARCHAR(50) NOT NULL
    );
END;
GO

INSERT INTO #BasicEmployeeList
    (BusinessEntityID,
     JobTitle,
     Title,
     FirstName,
     MiddleName,
     LastName
    )
SELECT p.BusinessEntityID,
       e.JobTitle,
       p.Title,
       p.FirstName,
       p.MiddleName,
       p.LastName
FROM Person.Person AS p
INNER JOIN HumanResources.Employee AS e
ON p.BusinessEntityID = e.BusinessEntityID;
GO

1. Without closing window #1, Switch over to window #2
2. Attempt to run the statement shown below
   • The system will encounter an error

USE AdventureWorks2012;
GO
SELECT bel.BusinessEntityID,
       bel.JobTitle,
       bel.Title,
       bel.FirstName,
       bel.MiddleName,
       bel.LastName
FROM #BasicEmployeeList AS bel;
GO

/* ERROR
Msg 208, Level 16, State 0, Line 1
Invalid object name '#BasicEmployeeList'.
*/

1. The error is received because the temporary table was created as a local temporary table and is therefore inaccessible to other connections

Important points to note

• Because temporary tables are persisted in the tempdb, the tempdb can see significantly large I/O operations if your application makes use of a lot of temporary storage
• SQL Server gives Local Temporary tables an auto-generated name which is transparent to the caller to allow multiple connections to the use local temporary tables with the same name [Link]
• Local temporary tables can be shared to referencing stored procedures as long as they use the same connection
  • This is documented in my post [Link]
• Creating named constraints on temporary tables prevent them from being used concurrently
  • You can read more on this in my post on Temporary Tables – Named Constraints, Msg 2714, 1750 [Link]
• Temporary tables do not support:
  • Foreign key constraints [Links]
  • Triggers

Until we meet next time,

Be courteous. Drive responsibly.