Global Temporary Tables

I was wondering the other day just how global are global temp tables?  MSDN has this to say:

Temporary Tables
You can create local and global temporary tables. Local temporary tables are visible only in the current session, and global temporary tables are visible to all sessions. Temporary tables cannot be partitioned.
Prefix local temporary table names with single number sign (#table_name), and prefix global temporary table names with a double number sign (##table_name).

This is in line with what I remembered, but I still had a few questions, so I ran a short test and this is what I found.  First, we need to set up our initial session.

USE AdventureWorks;
GO

IF OBJECT_ID('tempdb..#Customer') IS NOT NULL
DROP TABLE #Customer;

CREATE TABLE #Customer
(
[CustomerID] [INT] IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED
, [TerritoryID] [INT] NULL
, [AccountNumber] [VARCHAR](10) NOT NULL
, [CustomerType] [NCHAR](1) NOT NULL
, [rowguid] [UNIQUEIDENTIFIER] NOT NULL
, [ModifiedDate] [DATETIME] NOT NULL
)
ON  [PRIMARY];

SET IDENTITY_INSERT #Customer ON;
INSERT  INTO #Customer
( CustomerID
, TerritoryID
, AccountNumber
, CustomerType
, rowguid
, ModifiedDate
)
SELECT *
FROM    Sales.Customer;
SET IDENTITY_INSERT #Customer OFF;

SELECT * FROM #Customer;

This yields the following results:

Results of Initial Session

In a second session, I ran a select statement against the same local temp table.  As expected, the query failed.

USE AdventureWorks;
GO

SELECT * FROM #Customer;

Results:
Msg 208, Level 16, State 0, Line 2
Invalid object name ‘#Customer’.

Let’s try that again, except with a global temp table.  First, create the global temp table in the initial session.

USE AdventureWorks;
GO

IF OBJECT_ID('tempdb..##Customer') IS NOT NULL
DROP TABLE ##Customer;

CREATE TABLE ##Customer
(
[CustomerID] [INT] IDENTITY(1, 1) NOT NULL
PRIMARY KEY CLUSTERED
, [TerritoryID] [INT] NULL
, [AccountNumber] [VARCHAR](10) NOT NULL
, [CustomerType] [NCHAR](1) NOT NULL
, [rowguid] [UNIQUEIDENTIFIER] NOT NULL
, [ModifiedDate] [DATETIME] NOT NULL
)
ON  [PRIMARY];

SET IDENTITY_INSERT ##Customer ON;
INSERT  INTO ##Customer
( CustomerID
, TerritoryID
, AccountNumber
, CustomerType
, rowguid
, ModifiedDate
)
SELECT  *
FROM    Sales.Customer;
SET IDENTITY_INSERT ##Customer OFF;

SELECT * FROM ##Customer;

This query returns same results as before (as expected).  Now for a couple of small twists.  In the second session, I will use Contoso instead of AdventureWorks.  Also, after testing whether the SELECT works, I will also attempt to DROP the global temp table from the initial session.

USE Contoso;
GO

SELECT * FROM ##Customer

DROP TABLE ##Customer

This time the SELECT statement returns the same results as in the initial session, even though we are running the query under a different database.  Note also that you can even DROP the global temp table in a session other than the one in which it was created.  If we now go back to the initial session and run the final SELECT again, you will see the “Invalid object” error below.
Msg 208, Level 16, State 0, Line 2
Invalid object name ‘##Customer’.

Most things that can be done with global temporary tables can probably be done better with other methods, but it is good to understand how these things work should you choose to use this option.

Advertisements