Local and Global Temporary Tables in SQL Server

In SQL Server, temporary tables are used to store data temporarily, typically during the execution of a batch or session. SQL Server supports local and global temporary tables, each with its own scope and usage.

• Local Temporary Tables
• Global Temporary Tables

Local Temporary Tables (#)

Features

• Name Prefix: # (e.g., #TempTable).
• Scope: Visible only to the session that created it.
• Lifetime: Exists until the session that created it ends or the table is explicitly dropped.
• Use Case: Temporary storage of intermediate results for a single session or procedure.

-- Create a local temporary table
CREATE TABLE #TempTable (
    ID INT,
    Name NVARCHAR(50)
);

-- Insert data into the local temporary table
INSERT INTO #TempTable (ID, Name)
VALUES 
    (1, 'Om'),
    (2, 'Hema');

-- Select data from the local temporary table
SELECT * 
FROM #TempTable;

-- Drop the table (optional, as it is dropped automatically at session end)
DROP TABLE #TempTable;

Global Temporary Tables (##)

Features

• Name Prefix: ## (e.g., ##GlobalTempTable).
• Scope: Visible to all sessions and connections.
• Lifetime: Exists until the last session referencing it ends or it is explicitly dropped.
• Use Case: Temporary storage of data that needs to be shared across multiple sessions.

-- Create a global temporary table
CREATE TABLE ##GlobalTempTable (
    ID INT,
    Name NVARCHAR(50)
);

-- Insert data into the global temporary table
INSERT INTO ##GlobalTempTable (ID, Name)
VALUES 
    (1, 'Harish'),
    (2, 'Seema');

-- Select data from another session
-- Run this query in a new session or connection:
SELECT * 
FROM ##GlobalTempTable;

-- Drop the table when done (important for global temporary tables)
DROP TABLE ##GlobalTempTable;

Use case 1. When the current session

Use case 2. when one session using a global temp table and not dropping that table. The same temp table can be used in another query window session.

Key Differences Between Local and Global Temporary Tables
 

Feature	Local Temporary Table (#)	Global Temporary Table (##)
Name Prefix	#	##
Scope	Session-specific	Shared across all sessions
Lifetime	Ends with session	Ends when the last session ends
Visibility	Only for creating a session	All sessions
Use Case	Session-specific operations	Sharing data across sessions

Key points need care when using a temporary table.

The temporary table will not be destroyed automatically. You need to destroy it when there is no use in a statement.

Considerations

• Performance: Temporary tables are stored in the tempdb database, so frequent creation and deletion can impact its performance.
• Indexing: You can create indexes on temporary tables for faster query execution.
• Naming Conflicts: Local temporary tables with the same name in different sessions are isolated, but global temporary tables must have unique names.
• Alternative: For larger applications or distributed systems, consider using table variables (@TableName) or persistent tables with proper cleanup.

Temporary tables are a powerful tool in SQL Server for managing intermediate or session-specific data in an efficient and organized way.