Local and global temporary tables do not mean the same thing in every database. SQL Server’s global temporary tables can be visible across sessions; Oracle’s “global” temporary tables share a definition but keep each session’s rows private. PostgreSQL and MySQL use different temporary-table behavior again. To compare them correctly, separate table-definition visibility from row visibility, then check when the table or its rows are removed and what a commit does.
What “local” and “global” mean
Ask two separate questions: can another session see the table’s definition, and can it see the rows? A shared definition does not necessarily mean shared data. The words LOCAL and GLOBAL are database-specific vocabulary, not portable guarantees.
For example, SQL Server uses # for a local temporary table and ## for a global one. Oracle’s global temporary table has a definition available to multiple sessions, while its rows are private to each session. PostgreSQL accepts GLOBAL and LOCAL before TEMPORARY, but documents that those keywords currently make no difference. MySQL 8.0 documents session-local temporary tables. Consult the relevant product documentation: SQL Server, Oracle AI Database 26, PostgreSQL 19, and MySQL 8.0.
How the four engines compare
| Engine | Definition and row visibility | Lifetime and commit behavior | Important qualification |
|---|---|---|---|
| SQL Server | #name is visible only to the current session. ##name is visible to all sessions. |
A local table created in a stored procedure is dropped when the procedure ends; other local tables are dropped when the session ends. By default, a global table is dropped after its creating session ends and active statement references finish. | A database-scoped setting can change global-table auto-drop behavior. In Azure SQL Database, global temporary tables are scoped to that database rather than the whole SQL Server instance. See Microsoft Learn. |
| Oracle | A global temporary table’s definition is visible to multiple sessions, but each session sees and modifies only its own rows. Oracle also has 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. Private temporary tables can use ON COMMIT DROP DEFINITION or ON COMMIT PRESERVE DEFINITION. |
“Global” describes the shared definition, not shared row contents. See Oracle Managing Tables. |
| PostgreSQL | Each session creates its own temporary table; the table is session-specific. GLOBAL and LOCAL before TEMPORARY do not alter this behavior. |
Temporary tables are dropped at session end, or at transaction end with ON COMMIT DROP. The default is ON COMMIT PRESERVE ROWS; ON COMMIT DELETE ROWS is also available. |
The documentation says the GLOBAL and LOCAL keywords “presently make no difference in PostgreSQL” and marks them deprecated. See PostgreSQL CREATE TABLE. |
| MySQL 8.0 | CREATE TEMPORARY TABLE creates a table visible only in the current session. Different sessions can use the same temporary-table name. A temporary table can hide a permanent table of the same name in that session. |
The temporary table is dropped when the session closes. Unlike an ordinary CREATE TABLE, creating a temporary table does not cause an implicit commit. |
This documented behavior does not use SQL Server’s ## convention. See the MySQL 8.0 Reference Manual. |
Can another session see a global temporary table?
It depends on the database. In SQL Server, other sessions can see a ## global temporary table while it exists. In Oracle, other sessions can use the global temporary table’s shared definition, but they cannot see one another’s rows. PostgreSQL’s GLOBAL keyword does not turn a temporary table into a cross-session object, and MySQL’s documented temporary tables are session-local.
#1 Best Overall
Does committing a transaction clear temporary-table rows?
There is no universal commit rule. Oracle’s global temporary tables can be configured to delete rows at each commit or preserve them through the session. PostgreSQL’s default is to preserve rows at commit, but it also supports deletion at commit or dropping the table at transaction end. SQL Server’s documented temporary-table lifetimes are tied to procedure or session scope for local tables and to creator-session/active-reference behavior for global tables. MySQL 8.0’s cited documentation specifies session-end cleanup and that creating a temporary table does not implicitly commit; do not infer a row-clearing rule from the word “temporary.”
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Choose and migrate temporary-table code safely
Before using or porting temporary-table code, verify these points on the exact database product, version, and deployment target:
- Engine and version: syntax with the same name can have different semantics across products.
- Definition visibility: does another session need to reference the table structure?
- Row visibility: does another session need to read or change the same rows, or must each session remain isolated?
- Cleanup boundary: should the table or rows last for a transaction, stored procedure, session, or until references to a shared object end?
- Commit and rollback behavior: define whether rows should remain, be deleted, or cause the table definition to be removed.
- Connection pooling: if a session is reused, check whether retained rows can be visible to later work on that same connection.
- Deployment scope: for SQL Server, confirm whether the target is Azure SQL Database and whether global-table auto-drop configuration changes the default lifecycle.
These checks follow from the engines’ documented differences; they are not a performance comparison. The sources establish visibility and lifecycle behavior, not which engine or temporary-table type is faster.
Quick Recap
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.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
