SQL Server Temp Table Scope

by

The difference between a single hash (#) and a double hash (##), or the choice to use a table variable (@), isn’t just syntax—it’s a fundamental decision about data visibility and lifespan. In this article, I’m going to break down the scope of SQL Server temporary objects so you never have to guess whether your data is safe or accessible again.

SQL Server Temp Table Scope

An Overview

Before we dissect the scope, we need to identify the players. When we talk about “temp storage” in SQL Server, we are generally discussing three distinct objects. Each has a unique scope rule that dictates its visibility to other processes and its durability over time.

  1. Local Temporary Tables (#TableName)
  2. Global Temporary Tables (##TableName)
  3. Table Variables (@TableName)

Each of these lives in tempdb (yes, even table variables effectively use tempdb structures), but the rules of engagement are vastly different.

Local Temporary Tables (#)

This is the workhorse of SQL development. If I had a dollar for every #TempTable I’ve written, I’d be retired on a beach in Florida. But despite their ubiquity, their scoping rules have some nuance.

The “Session” Scope Rule

The golden rule for local temp tables is that they are scoped to the Session (or SPID) that created them.

If I, logged in as User_Mike, run a script that creates #ClientList, that table exists only for me. If my colleague, User_Sarah, logs in from her desk and runs a query looking for #ClientList, she will get an “Invalid object name” error.

This isolation is by design. It allows multiple users to run the same stored procedure simultaneously without stepping on each other’s toes. SQL Server appends a unique suffix to the table name behind the scenes (in tempdb) to keep my version of the table separate from Sarah’s.

The “Nested” Scope Behavior

Here is where it gets tricky.

Local temp tables are visible to child scopes.

If I create #MyTable in a stored procedure named Proc_A, and then Proc_A calls another stored procedure named Proc_B, guess what? Proc_B can see, query, and modify #MyTable.

Why this matters:

This feature allows for modular code. I can populate a scratchpad in a parent process and pass it down to a worker process without passing massive parameters. However, it also introduces a risk. If Proc_B tries to create a table named #MyTable (perhaps just by coincidence), it will fail because the name is already taken in the current scope chain.

When Do They Die?

A local temp table is automatically dropped when:

  1. The session that created it disconnects.
  2. The stored procedure that created it finishes execution (if created inside a proc).
  3. You explicitly run DROP TABLE.

I always recommend explicitly dropping them. Relying on the garbage collector is fine, but cleaning up after yourself is better engineering.

Global Temporary Tables (##)

They are identified by the double hash prefix (##TableName).

The “Public” Scope Rule

Global temp tables are visible to every session on the server.

If I create ##GlobalAuditLog in my session, User_Sarah can see it instantly. User_Jason in the next building can INSERT into it. It is a shared resource.

The Durability Dilemma

The confusion with global temp tables usually centers on when they disappear. Since multiple people might be using them, SQL Server cannot just drop the table when the creator logs off.

Here is the logic SQL Server uses:

  1. Creation: The table is created by a specific session.
  2. References: Other sessions may reference it.
  3. Cleanup: The table is dropped only when the creating session disconnects AND no other active references exist.

This means if I create a global table and then my VPN drops, the table should disappear. But if Sarah is currently running a long transaction against that table, the table stays alive until she is done. This “zombie table” behavior can block subsequent jobs that try to create the same table, causing nightly ETL failures.

Table Variables (@)

Introduced way back in SQL Server 2000, table variables are often touted as the “faster” alternative to temp tables. While performance is a debate for another article (spoiler: they aren’t always faster), their scoping is much stricter.

The “Batch” Scope Rule

Table variables behave exactly like INT or VARCHAR variables. They are scoped strictly to the Batch or Stored Procedure in which they are declared.

Unlike local temp tables (#), table variables are NOT visible to child scopes.

  • Scenario:
    • I declare @MyTable in Proc_A.
    • Proc_A calls Proc_B.
    • Proc_B attempts to select from @MyTable.
    • Result: Error. Proc_B has no idea @MyTable exists.

This strict scoping is actually a feature, not a bug. It makes your code incredibly predictable. I know for a fact that Proc_B cannot accidentally modify my data in @MyTable. It enforces a clean separation of concerns.

Lifecycle

A table variable is cleaned up automatically the moment the batch finishes. You cannot even explicitly DROP a table variable; the syntax doesn’t exist. This “fire and forget” cleanup makes them very attractive for short, transactional logic loops.

Comparison: The Scope Matrix

FeatureLocal Temp Table (#)Global Temp Table (##)Table Variable (@)
SyntaxCREATE TABLE #NameCREATE TABLE ##NameDECLARE @Name TABLE
Scope VisibilityCurrent Session + Nested Child ProcsAll Sessions (Server-wide)Current Batch Only
Visible to Child Procs?YesYesNo
Storage Locationtempdbtempdbtempdb (memory optimized in some cases)
Transaction LogsYes (fully logged)Yes (fully logged)Minimal logging
CleanupAuto at end of session/proc, or ManualAuto when all users are doneAuto at end of batch
StatisticsSQL Server creates statisticsSQL Server creates statisticsNo statistics (Cardinality always 1)

Pitfalls and Common Scoping Errors

I see the same issues cropping up repeatedly regarding scope. Let’s cover how to avoid them.

1. The “Name Collision” in Stored Procedures

Imagine you have a main controller procedure MasterProcess that creates #WorkTable. It calls a helper procedure CalculateTax.

If the developer of CalculateTax decides to also create a table named #WorkTable for their own local logic, the code will crash with an “Object already exists” error.

Solution:

  • Use Table Variables (@) inside sub-procedures whenever possible to ensure isolation.
  • If you must use # tables, use a naming convention that includes the procedure name, like #CalculateTax_WorkTable.

2. The “Ghost” Table in SSMS

Developers often run a script in SSMS, create a #TempTable, and then try to alter the script and run it again. They get an error saying the table already exists.

Because the SSMS window (the session) is still open, the scope is still active.

Solution:

I always start my ad-hoc scripts with a conditional drop check:

SQL

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

This ensures my scope is clean before I start.

3. Global Table Locking

I once saw a reporting system freeze because a developer used ##ReportData to stage data. One user ran the report and went to lunch, leaving the transaction open. No one else could run the report because the global scope meant the name was locked.

Solution:

Avoid ## tables for concurrent user processes. Just don’t do it.

Making the Right Choice

So, which one should you use? Here is my decision tree.

Use a Local Temp Table (#) when:

  • You have a large amount of data ( > 100 rows) and need Index/Statistics support for performance.
  • You need to access the data in a child stored procedure without passing parameters.
  • You need to alter the table structure dynamically after creation.

Use a Table Variable (@) when:

  • You have a very small dataset ( < 100 rows).
  • You want to guarantee that no other procedure can modify your data (Strict Scope).
  • You are in a function (UDF) where temp tables are not allowed.

Use a Global Temp Table (##) when:

  • You are writing a specialized admin script to share data across separate connections.
  • (Honestly, try to find another way first).

Conclusion

Understanding SQL Server temp table scope is the difference between writing fragile, buggy code and writing robust, scalable database applications.

  • #Local is for your session and your children.
  • ##Global is for everyone (handle with care).
  • @Variable is for right here, right now, and nowhere else.

By knowing these scopes, you prevent data collisions, memory leaks, etc.

You may also like the following articles: