What is the difference between a "Local Temporary Table" and "Global Temporary Table"?

1.  A Local Temporary Table  created by giving it a prefix of #  it is easy to represent whereas a Global Temporary Table  created by giving it a prefix of ##.
2.  A Local Temporary Table can not be shared among multiple users whereas a Global Temporary Table should be shared among multiple users.
3.  A Local Temporary Table is only present to the current DB connection for the current user and are cleared when the connection is closed whereas a Global Temporary Table is available to any connection once initialise. They are clean when the last connection is closed.

Introduction

Temporary tables are a powerful feature in SQL that allow users to store and manipulate intermediate results within a session. SQL provides two types of temporary tables:

• Local Temporary Table
• Global Temporary Table

Understanding their differences is crucial for efficient database management and query execution.

What is a Local Temporary Table?

A Local Temporary Table is created with a # prefix. It is only available to the session that created it and gets automatically deleted when the session is closed.

Key Characteristics:

• Created using a # prefix (e.g., #LocalTemp)
• Only accessible to the session that created it
• Automatically removed when the session ends
• Cannot be shared between multiple users

Syntax for Local Temporary Table:

CREATE TABLE #LocalTemp (
    UserID INT,
    Name VARCHAR(50),
    Address VARCHAR(150)
);
INSERT INTO #LocalTemp VALUES (1, 'John Doe', '123 Street');
SELECT * FROM #LocalTemp;

What is a Global Temporary Table?

A Global Temporary Table is created with a ## prefix. It remains accessible to all database sessions and is deleted only when the last session using it is closed.

Key Characteristics:

• Created using a ## prefix (e.g., ##GlobalTemp)
• Available to all users across multiple sessions
• Removed only when all active sessions that reference it are closed

Syntax for Global Temporary Table:

CREATE TABLE ##GlobalTemp (
    UserID INT,
    Name VARCHAR(50),
    Address VARCHAR(150)
);
INSERT INTO ##GlobalTemp VALUES (1, 'Jane Doe', '456 Avenue');
SELECT * FROM ##GlobalTemp;

Key Differences Between Local and Global Temporary Tables

When to Use Local vs Global Temporary Tables?

Use Local Temporary Tables when:

• You need temporary storage that is session-specific.
• You want automatic cleanup after the session ends.
• Security and session isolation are important.

Use Global Temporary Tables when:

• Multiple users need to access the same temporary data.
• The data must persist beyond a single session.
• The table is required for batch processing.

Conclusion

Understanding the differences between Local Temporary Tables and Global Temporary Tables is essential for optimizing SQL queries and improving database performance. Local temporary tables are session-bound and isolated, whereas global temporary tables can be shared across multiple connections. Choosing the right type based on your requirements can help improve efficiency and resource management in SQL-based applications.