“Local” and “global” temporary tables do not mean the same thing in every database. In SQL Server, a global temporary table can be visible to other sessions; in Oracle, a global temporary table shares its definition but keeps each session’s rows private. PostgreSQL and MySQL use different temporary-table rules. To understand what another connection can see—and when rows disappear—check the database engine’s definition, row visibility, and cleanup behavior separately.
What “local” and “global” mean
A temporary table is a table intended to exist for a limited scope, but the word “global” is not a portable guarantee that every connection can see its data. Ask two separate questions: who can see the table definition, and who can see the rows? Then check how long the table or rows last and what happens at commit.
The distinction is especially clear in SQL Server and Oracle: SQL Server’s global temporary tables are cross-session objects, while Oracle’s global temporary tables have a shared definition and session-private rows. PostgreSQL accepts the keywords GLOBAL and LOCAL before TEMPORARY but documents that they make no difference there. MySQL’s documented temporary tables are session-local.
How the four databases compare
| Database | Definition and row visibility | Lifetime and commit behavior | Important qualification |
|---|---|---|---|
| SQL Server | #name local temporary tables are visible only to the current session. ##name global temporary tables are visible to all sessions. |
A local temporary table created in a stored procedure is dropped when the procedure ends; other local temporary tables are dropped when the session ends. By default, a global temporary table is dropped after its creating session ends and active statement references finish. A database-scoped setting can change its automatic-drop behavior. | In Azure SQL Database, global temporary tables are scoped to the database, not the entire SQL Server instance. See Microsoft’s CREATE TABLE documentation. |
| Oracle AI Database 26 | A global temporary table’s definition is visible to multiple sessions, but each session sees and modifies only its own rows. Oracle also provides private temporary tables, whose definitions and contents are session-private. | For a global temporary table, ON COMMIT DELETE ROWS clears rows at each commit; ON COMMIT PRESERVE ROWS retains them through the session. A private temporary table can use ON COMMIT DROP DEFINITION or ON COMMIT PRESERVE DEFINITION. |
Here, “global” describes the shared table definition—not shared row contents. See Oracle’s Managing Tables documentation. |
| PostgreSQL 19 | Each session creates its own temporary table, which is session-specific. Adding GLOBAL or LOCAL before TEMPORARY does not change this behavior. |
Temporary tables are dropped at session end, or at transaction end if created with ON COMMIT DROP. The default is ON COMMIT PRESERVE ROWS; ON COMMIT DELETE ROWS is also available. |
PostgreSQL says the GLOBAL and LOCAL keywords “presently make no difference” and discourages them. See PostgreSQL’s CREATE TABLE documentation. |
| MySQL 8.0 | CREATE TEMPORARY TABLE creates a table visible only in the current session. Different sessions can use the same temporary-table name; within a session, a temporary table can hide a permanent table with the same name. |
The table is dropped when the session closes. Unlike ordinary CREATE TABLE, which normally causes an implicit commit, creating a temporary table does not. |
Do not assume SQL Server’s ## convention applies to MySQL. See the MySQL 8.0 Reference Manual. |
Can another session see a global temporary table?
It depends on the database. In SQL Server, another session can see a ## global temporary table while it exists. In Oracle, another session can see the global temporary table’s definition, but it cannot see the first session’s rows. PostgreSQL’s temporary tables and MySQL’s documented temporary tables are session-specific; the PostgreSQL GLOBAL keyword does not turn one into a cross-session table.
#1 Best Overall
Does committing a transaction clear temporary-table rows?
Not universally. Oracle global temporary tables can clear rows on every commit with ON COMMIT DELETE ROWS, or retain them through the session with ON COMMIT PRESERVE ROWS. PostgreSQL defaults to preserving rows at commit, with options to delete rows or drop the table at transaction end. MySQL’s temporary-table creation avoids the implicit commit associated with ordinary table creation; its temporary table otherwise lasts until the session closes. For SQL Server, the cited documentation establishes the table’s session, procedure, and global-table cleanup scopes; it does not define a general rule that committing a transaction clears temporary-table rows.
How to choose or migrate temporary-table code
Before adopting a pattern or moving SQL between engines, write down the required visibility and cleanup behavior. Familiar syntax is not proof of equivalent semantics, so verify the behavior on the exact target engine and deployment.
Quick Recap
Best Value
- Identify the engine and version. The documented behavior differs among SQL Server, Oracle AI Database 26, PostgreSQL 19, and MySQL 8.0; other engines are outside this comparison.
- Specify visibility separately. Decide whether another session needs to see the table definition, the rows, or both.
- Choose the cleanup boundary. Determine whether the table or rows should last for a stored procedure, transaction, session, or until a SQL Server global table’s creator session ends and active statements finish.
- Define commit and rollback expectations. Check the engine’s options and defaults rather than assuming that commit clears rows.
- Account for connection reuse. If a connection pool can reuse a session, consider whether rows preserved in that session could affect later work.
- Check hosting scope and settings. In particular, confirm SQL Server’s global-table scope in Azure SQL Database and whether automatic dropping is configured differently.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
