What are the differences between local and global temporary tables?

Question

Aryan Kumar · Answer

In SQL, a local temporary table lives until the connection is valid or until the duration of a compound statement. A global temporary table is permanently present in the database. However, the rows of the table are present until the connection is existent.

There are two types of temporary tables: local and global. Here are the differences between them:

Scope is the primary distinction between local and global temporary tables. While global temporary tables are visible during all sessions, local temporary tables are only viewable within the current session.
Name: The names of local temporary tables begin with a single pound symbol (#), while those of global temporary tables begin with two pound signs (##).
The CREATE TABLE statement creates local temporary tables for the current session; the CREATE TABLE statement with a double pound sign (##) prefix creates global temporary tables.
Local temporary tables are automatically removed at the conclusion of the current session, whereas global temporary tables are automatically deleted after the final session that made use of the table closes.
Global temporary tables are accessible to all users who have authorization to access them, whereas local temporary tables inherit permissions from the user who created them.

In summary, local temporary tables are created and destroyed within the current session and are only visible to the current user, while global temporary tables can be accessed across sessions and are visible to all users who have permissions to access them.

Krishnapriya Rajeev · Answer

Difference between local temporary tables and global temporary tables are as follows: LOCAL TEMPORARY TABLES GLOBAL TEMPORARY TABLES Local temporary tables can only be accessed within the current session or connection in which they are created. Global temporary tables can be accessed across multiple sessions or connections. Local temporary tables get destroyed when the session or connection that created them is terminated. Global temporary tables get destroyed when the last session or connection referencing them is terminated. Local temporary tables begin with '#' character. Global temporary tables begin with '##' character. Local temporary tables are more secure. Global temporary tables have security risks. Local temporary tables usually have better performance. Global temporary tables cause contention and locking issues.

LOCAL TEMPORARY TABLES	GLOBAL TEMPORARY TABLES
Local temporary tables can only be accessed within the current session or connection in which they are created.	Global temporary tables can be accessed across multiple sessions or connections.
Local temporary tables get destroyed when the session or connection that created them is terminated.	Global temporary tables get destroyed when the last session or connection referencing them is terminated.
Local temporary tables begin with '#' character.	Global temporary tables begin with '##' character.
Local temporary tables are more secure.	Global temporary tables have security risks.
Local temporary tables usually have better performance.	Global temporary tables cause contention and locking issues.

forum

What are the differences between local and global temporary tables?

Can you answer this question?

2 Answers