Global Temp Tables in SQL Server (##temp): Sharing Data Across Sessions Safely

A global temp table (##) is visible to every session on the server — genuinely useful, and genuinely a concurrency hazard if you’re not careful. This is the least commonly needed of the three temp object types, and this lesson is honest about exactly when that rarity is justified.

Creating and Sharing One

CREATE TABLE ##SharedDriverSnapshot (
    driver_name NVARCHAR(100),
    total_fare  DECIMAL(10,2)
);

INSERT INTO ##SharedDriverSnapshot
SELECT driver_name, SUM(fare_usd) FROM dbo.TripAdvanced GROUP BY driver_name;

-- Any other session, connected to the same server, can now see this:
-- SELECT * FROM ##SharedDriverSnapshot;

DROP TABLE ##SharedDriverSnapshot;

When It’s Dropped

This second condition is the subtle part: if Session A creates a global temp table and disconnects, but Session B is mid-query against it, SQL Server keeps it alive until Session B finishes — it doesn’t get yanked out from under an active reader. Once every referencing session is done, it’s cleaned up automatically.

The Concurrency Risk

Multiple sessions can write to a global temp table simultaneously with no built-in isolation between them, unlike a real table where you’d deliberately design locking/transactions around it (Chapter 9 covers this properly). Two sessions inserting at the same time won’t corrupt data, but two sessions racing to both check-then-insert (“if this row doesn’t exist yet, add it”) can both pass the check simultaneously and both insert — a classic race condition, worse here because it’s easy to forget a scratch table needs the same concurrency discipline as a real one.

-- A race condition waiting to happen if two sessions run this concurrently:
IF NOT EXISTS (SELECT 1 FROM ##SharedDriverSnapshot WHERE driver_name = 'Amir')
    INSERT INTO ##SharedDriverSnapshot VALUES ('Amir', 0);
-- Both sessions can see "not exists" before either has inserted, producing a duplicate

Legitimate Use Cases

Use it for genuinely useful, narrow cases — sharing a debug snapshot between two active SSMS windows during a troubleshooting session, or coordinating a multi-step batch/ETL job where separate connections (sometimes even separate tools) need to hand off an intermediate result. Reason explicitly about concurrent access rather than assuming it’s safe by default; in most cases, a permanent staging table with proper locking, or simply passing data through parameters, is the more robust choice for anything beyond ad-hoc debugging.