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;

SQL Query

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 1

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.

Use case 2

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.