🗂️Temporary Table

NEW

Also known as: Temp Table

A transient database table used to store intermediate results within a session or transaction scope.

DESCRIPTION

Temporary tables hold intermediate results for complex queries, ETL steps, or procedural logic and are typically scoped to a session. They can improve performance by reusing computed results and enabling indexing. Implementation details vary across systems (e.g., local vs global temporary tables). They are automatically dropped at session end or explicitly by the user. Alternatives include CTEs, derived tables, and materialized views depending on persistence and optimization needs.

ALTERNATIVE DEFINITIONS

MySQL

A TEMPORARY table is visible only to the current session, and is dropped automatically when the session ends.

Defines session visibility and automatic drop semantics.

Source

PostgreSQL

Temporary tables exist in a special schema, so a name used for a temporary table cannot be used again for a non-temporary table.

Highlights scoping and naming behavior in PostgreSQL.

Source

Microsoft SQL Server

Local temporary tables are visible only in the current connection and are dropped when the connection is closed.

Describes SQL Server local temporary table scope and lifecycle.

Source

RELATED TERMS

Staging Table Session Transaction Index Query Optimization Stored Procedure Extract-Transform-Load

COMPETING TERMS

Common Table Expression Materialized View Derived Table

RELATIONS

• Broader: Table

KNOWLEDGE AREAS

Data Storage & Operations Data Engineering Data Warehousing & BI Programming Languages

TARGET AUDIENCE

Data Engineer Analytics Engineer Software Developer Data Architect BI Specialist

EXTERNAL SOURCES

• MySQL - MySQL temporary tables reference
• PostgreSQL - PostgreSQL CREATE TEMPORARY TABLE
• Snowflake - Snowflake temporary tables documentation

TOP QUESTIONS

When should you use a Temporary Table?

Use it to store intermediate results in complex queries, reuse computed state, or apply indexes during ETL or procedural workflows.

Category: Usage

How long do Temporary Tables persist?

Typically for the duration of a session or transaction; many systems drop them automatically when the session ends.

Category: Facts

What are common mistakes with Temporary Tables?

Assuming cross‑session visibility, neglecting indexes when beneficial, and not cleaning up can hurt performance or cause naming conflicts.

Category: Mistakes

Are Temporary Tables faster than CTEs?

They can be faster when materialization and indexing help; CTEs may be optimized differently depending on the database engine.

Category: Opinion

Do Temporary Tables support transactions?

Yes, most systems treat temporary tables like regular tables regarding transactional behavior within a session.

Category: Facts

Can Temporary Tables be indexed?

In many databases, you can add indexes to temporary tables to accelerate joins and filters.

Category: Requirements

How do Temporary Tables affect concurrency?

Because they are session‑scoped, concurrency issues are reduced; global temporary tables may require coordination.

Category: Management

What is a competing concept to Temporary Tables?

CTEs and derived tables can avoid materialization, while materialized views persist results for broader reuse.

Category: Alternatives

What is the biggest misunderstanding about Temporary Tables?

That they are always ephemeral; some systems also provide global temporary tables with broader visibility and different lifecycle behavior.

Category: Misunderstanding