Great question—those tables starting with # are local temporary tables, a core feature of T-SQL for managing temporary data in SQL Server. Let’s break down what they are and why we use them in production environments.

What Does the # Signify? #

When you create a table like #tmp, you’re making a local temporary table that lives in the tempdb system database. Key traits include:

• Session-level scope: Only the session (e.g., your query window, a running stored procedure, or an application connection) that created the table can access it. Other users or sessions won’t even see it exist.
• Automatic cleanup: The table is automatically dropped when the creating session ends. You can also manually drop it early with IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP table #tmp; (the tempdb.. prefix tells SQL Server to look in the tempdb database, where all temp tables reside).

Why Use Local Temporary Tables in Production? #

These tables shine in several common production scenarios:

• Store intermediate results: For complex queries (like multi-step reporting or data transformations), temp tables let you break logic into manageable chunks. Instead of nesting 5 levels of subqueries, you can filter raw data into #tmp, then run aggregations or joins against that smaller, pre-processed dataset.
• Reduce blocking and locks: Since temp tables are isolated to your session, you don’t have to worry about locking or blocking other users who are working with the same production tables. This is critical in high-concurrency environments where even short locks can cause delays.
• Boost performance for repeated operations: If you need to reuse the same filtered dataset multiple times in a script, storing it in a temp table avoids re-running expensive queries (like large table scans or joins) every time. You can even add indexes to temp tables to speed up subsequent reads.
• Simplify debugging and maintenance: Temp tables make complex scripts easier to troubleshoot—you can check the contents of #tmp mid-execution to verify intermediate results, rather than trying to parse a single massive query.

A quick side note: If you see tables starting with ##, those are global temporary tables—they’re visible to all sessions until the creating session ends and no other sessions are using them. But # local temp tables are far more common in production since they avoid cross-session conflicts.

Hope that demystifies those # tables for you!

内容的提问来源于stack exchange，提问作者Chuck