Services
Technology Web App Development Mobile App Development Windows App Development Database Development UI/UX Development Cloud Development SEO Digital Marketing Guest Posting Content Writing Artificial Intelligence Data Science ERP Development CRM Development CMS Development LMS Development Business Listing Blockchain Technology Startup Promotion AI Tools Integration
Products
Data Converter Cleaner TUC - Unit Converter Import Export Survey Manager Tweet Controller
Developer
Article Blog Forum Interview Beginner News Startup Quiz
Users
Career
Apply Job Internship
Pricing
Login Sign Up Get a Quote

forum

home / developersection / forums / what are the differences between local and global temporary tables?
Ask Question
Ashutosh Patel
Ashutosh Patel

Jr. Software Developer

I am a software developer at MindStick soft. Pvt. ltd. I joined this company in august 2021 after completing my post-graduation course. I love to see the historical places of my country.

Message

2 Answers

Aryan Kumar
Aryan Kumar 18 Apr 2023
Report Abuse

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:

  1. 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.
  2. Name: The names of local temporary tables begin with a single pound symbol (#), while those of global temporary tables begin with two pound signs (##).
  3. 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.
  4. 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.
  5. 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
Krishnapriya Rajeev 23 Mar 2023
Report Abuse

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.
advertising