Global临时表的明确用途、与Local临时表的区别及适用场景咨询

Let’s break this down clearly, since temp tables can be tricky to wrap your head around at first.

What Are Global Temporary Tables? #

Global temporary tables are identified by the double hash prefix: ##TableName. Here’s what makes them unique:

• Visibility: They’re accessible to all active sessions and users in the SQL Server instance, not just the one that created them.
• Lifetime: They stick around until the last session that’s referencing them is closed—including the original creating session. Even if the creator logs out, if another session is still using the table, it stays alive.
• Storage: Like all temp tables, they live in tempdb, but their name (with the ## prefix) is visible to everyone (SQL Server adds a unique suffix under the hood to avoid conflicts, but you don’t see that).

Key Differences from Local Temporary Tables #

Local temp tables use a single hash prefix: #TableName. Here’s how they stack up against global ones:

When to Use Which? #

Local Temporary Tables #

• Single-session processing: Use these when you need intermediate results for a stored procedure, ad-hoc query, or batch job that doesn’t need to be shared with other users/sessions. For example, filtering a large dataset to work with a subset only in your current session.
• Avoiding conflicts: Since they’re session-private, you don’t have to worry about other users overwriting your data or naming clashes.
• Auto-cleanup: They disappear automatically when your session ends, so you don’t have to manually drop them (though you can with DROP TABLE #TableName if needed).

Global Temporary Tables #

• Cross-session data sharing: Use these when multiple sessions or users need access to the same temporary dataset. For example, a nightly batch job that generates a list of pending orders, and multiple reporting scripts need to pull from that list throughout the night.
• Persistent temporary data: If you need temporary data to outlive the creating session but don’t want to use a permanent table (since global temps auto-drop when no one’s using them anymore).
• Coordination between processes: When different jobs or scripts need to share state or data temporarily without writing to a permanent table.

Special Notes to Keep in Mind #

• Global temp tables are still tied to tempdb—if SQL Server restarts, all temp tables (local and global) are wiped out. Don’t rely on them for long-term storage.
• Be cautious with global temps: If multiple sessions are modifying the same table, you need to handle locking/concurrency just like you would with a permanent table to avoid data issues.
• You can explicitly drop a global temp table at any time with DROP TABLE ##TableName, which will remove it immediately even if other sessions are referencing it (those sessions will get an error if they try to access it afterward).

Quick Example of a Global Temp Table #

-- Create a global temp table
CREATE TABLE ##PendingOrders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderTotal DECIMAL(10,2)
);

-- Insert sample data
INSERT INTO ##PendingOrders VALUES (1001, 500, 79.99), (1002, 501, 129.99);

-- Any other session can now query this table
SELECT * FROM ##PendingOrders;

内容的提问来源于stack exchange，提问作者Jignesh M. Mehta