difference between a "Local Temporary Table" and "Global Temporary Table" in SQL -DeveloperIndian
#global temporary table in sql #global temporary table sql #global temporary table #global temp table sql
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:
Understanding their differences is crucial for efficient database management and query execution.
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.
#LocalTemp
)CREATE TABLE #LocalTemp (
UserID INT,
Name VARCHAR(50),
Address VARCHAR(150)
);
INSERT INTO #LocalTemp VALUES (1, 'John Doe', '123 Street');
SELECT * FROM #LocalTemp;
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.
##GlobalTemp
)CREATE TABLE ##GlobalTemp (
UserID INT,
Name VARCHAR(50),
Address VARCHAR(150)
);
INSERT INTO ##GlobalTemp VALUES (1, 'Jane Doe', '456 Avenue');
SELECT * FROM ##GlobalTemp;
Feature | Local Temporary Table | Global Temporary Table |
---|---|---|
Prefix |
# (Single Hash) |
## (Double Hash) |
Accessibility | Only available in the current session | Available to all sessions |
Persistence | Deleted when session ends | Deleted when last session using it ends |
User Scope | Restricted to one user | Shared among multiple users |
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.