Create a Global Temporary Table in SQL Server

A global temporary table is a table that any session on the server can read. Its name starts with two # signs, and SQL Server stores it in tempdb. You create it like any table, and it disappears on its own when its creator leaves.

Local and Global Compared

A local temporary table starts with one # sign. Only the session that created it can see it. A global temporary table starts with two. Every session sees it, including sessions that connect after it exists.

The last row carries the cost. A local table gets a hidden suffix, so two sessions can both create #Orders. A global table keeps its exact name, so only one session can hold it at a time.

Create One and Read It From Another Window

The demo needs no database of its own, because everything lives in tempdb. The first script creates the table, adds two rows, and checks the name in tempdb.

CREATE TABLE ##ReorderList (ItemID int IDENTITY(1,1) PRIMARY KEY, ItemName nvarchar(40) NOT NULL);
INSERT INTO ##ReorderList (ItemName) VALUES (N'Notebook'), (N'Stapler');
SELECT ItemID, ItemName FROM ##ReorderList;
SELECT name FROM tempdb.sys.tables WHERE name = N'##ReorderList';

Leave that window open. Open a second query window in SSMS, which is a new session, and run the next query. It returns the same two rows.

SELECT ItemID, ItemName FROM ##ReorderList;

Any session can also insert, update and delete rows in the table. By default, every user has full access to it. That’s the feature, and it’s also the risk.

Two Windows, One Name

Now try to create the same table again from the second window.

CREATE TABLE ##ReorderList (ItemID int);

SQL Server refuses, because the name is taken.

Msg 2714, Level 16, State 6, Line 1
There is already an object named '##ReorderList' in the database.

Two applications that both create ##ReorderList will collide this way. The second one fails, or worse, it reads the first one’s rows. Give each session its own name. The session id works, because it’s unique while the session lives.

DECLARE @sql nvarchar(200) = N'CREATE TABLE ##ReorderList_' + CAST(@@SPID AS nvarchar(10)) + N' (ItemID int);';
EXEC (@sql);
SELECT COUNT(*) AS PerSessionTables FROM tempdb.sys.tables WHERE name = N'##ReorderList_' + CAST(@@SPID AS nvarchar(10));

When the Table Disappears

SQL Server drops a global temporary table when its creating session ends. It waits only for tasks that are running a statement against the table. A second session that only read the table earlier doesn’t keep it alive, even inside an open transaction. To repeat the test, create the table in one window. Read it from a second window inside an open transaction, and then close the first window. The next query in the second window answers Invalid object name. Don’t count on another session to keep the table alive.

A procedure behaves differently from what many people expect. A local table created inside a procedure disappears when the procedure ends. A global table survives it. The next script shows both. Temporary procedures, the ones that start with #, keep the demo free of permanent objects.

CREATE PROCEDURE #MakeLists
AS
CREATE TABLE #Local (id int);
CREATE TABLE ##MadeInProc (id int);
GO
EXEC #MakeLists;
SELECT CASE WHEN OBJECT_ID(N'tempdb..##MadeInProc') IS NULL THEN N'gone' ELSE N'still there' END AS GlobalAfterProc,
       CASE WHEN OBJECT_ID(N'tempdb..#Local') IS NULL THEN N'gone' ELSE N'still there' END AS LocalAfterProc;

A global table also helps procedures. A procedure can write rows into a global table that was created outside it, and the caller reads them afterwards.

CREATE PROCEDURE #AddItem @ItemName nvarchar(40)
AS
INSERT INTO ##ReorderList (ItemName) VALUES (@ItemName);
GO
EXEC #AddItem N'Pencil case';
SELECT COUNT(*) AS RowsNow FROM ##ReorderList;

The table held two rows, and the procedure added a third.

Space and Constraints

A global temporary table can have a primary key, indexes and defaults, as the primary key in the demo shows. FOREIGN KEY constraints aren’t enforced on it. It also uses tempdb space until it’s dropped. A large one holds that space while its creator stays connected. To see which global tables exist, filter tempdb.sys.tables on names that start with ##. That is another reason to drop your own table as soon as the work is done.

Is It the Right Tool?

You could argue that a global temporary table is never the right tool. Anyone can change it, its name can collide, and it vanishes when its creator disconnects. A permanent staging table in your own database avoids all three problems.

That’s a strong argument for application code. A global table earns its place in small, controlled jobs. Two SQL Agent steps can pass a result through it. A test script can hand rows to a second window. A one-off cleanup can share a list between sessions. Use a unique name and drop the table when the job ends.

Another way to pass rows between sessions is a permanent table with a job key column. Each job writes its rows under its own key and deletes them when it ends. That table survives disconnects and has no name collisions.

What to Remember

Use two # signs to share a temporary table between sessions. Expect it in tempdb under its exact name. Build a unique name when more than one job could run at once. Drop it yourself when the work is done, instead of waiting for the session to end.

Drop the demo objects now. Run this in the window that created the per-session table, or close that window, which drops the table.

DROP TABLE ##ReorderList;
DROP TABLE ##MadeInProc;
DECLARE @d nvarchar(200) = N'DROP TABLE ##ReorderList_' + CAST(@@SPID AS nvarchar(10)) + N';';
EXEC (@d);

A global temporary table is not a shared notebook, it is a whiteboard that anyone can wipe.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.