Comprehensive Guide to Creating and Using Temporary Tables in SQL Server

Basic Concepts and Classification of Temporary Tables

In SQL Server database development, temporary tables serve as an important data storage mechanism, primarily used for storing temporary result sets. Based on scope and creation methods, temporary tables are mainly categorized into three types: local temporary tables, global temporary tables, and table variables.

Creation and Usage of Local Temporary Tables

Local temporary tables are identified by a single hash symbol (#) prefix, with their scope limited to the current session. The system automatically drops these tables when the session ends. The creation syntax is similar to regular tables:

CREATE TABLE #LocalTempTable (
    id INT,
    name VARCHAR(50),
    created_date DATETIME
);

This type of temporary table is widely used in stored procedures and batch statements, particularly suitable for storing intermediate results that need to be referenced multiple times.

Characteristics of Global Temporary Tables

Global temporary tables use a double hash symbol (##) prefix, with their scope extending to all sessions. The system automatically drops global temporary tables only when the creating session disconnects and no other sessions are referencing them. Creation example:

CREATE TABLE ##GlobalTempTable (
    user_id INT,
    action_type VARCHAR(20),
    timestamp DATETIME2
);

Global temporary tables are suitable for scenarios requiring shared temporary data across multiple sessions, but attention must be paid to data consistency issues in concurrent access.

Declaration and Application of Table Variables

Table variables are declared using the @ symbol, with their scope limited to the current batch, stored procedure, or function. Compared to temporary tables, table variables have certain functional limitations, such as not supporting direct index creation. Declaration syntax:

DECLARE @TableVariable TABLE (
    sequence_id INT IDENTITY(1,1),
    product_code VARCHAR(10),
    quantity INT
);

Table variables are typically used for scenarios with small data volumes that don't require complex index operations. Due to their memory storage characteristics, they offer performance advantages for small dataset processing.

Creating Temporary Tables Using SELECT INTO

In addition to explicit structure definition, temporary tables can also be created based on existing table structures using the SELECT INTO statement:

SELECT 
    customer_id,
    order_total,
    order_date
INTO #RecentOrders
FROM orders
WHERE order_date >= DATEADD(day, -30, GETDATE());

This method is particularly suitable for scenarios requiring quick replication of table structures and data, simplifying the temporary table creation process.

Comparison and Selection of Various Temporary Table Types

In practical development, choosing which temporary table type requires comprehensive consideration of the following factors:

• Data Volume: Table variables suit small datasets, temporary tables suit large datasets
• Scope Requirements: Local temporary tables are limited to current session, global temporary tables can span sessions
• Functional Requirements: Temporary tables support advanced features like indexes and statistics
• Performance Considerations: Table variables typically offer better memory performance

By appropriately selecting temporary table types, query performance can be optimized to meet different business requirements.