What is a Temp Table?

by

Need a high-performance, disposable workspace for your SQL queries? Meet temporary tables, also known simply as temp tables. As their name implies, these tables offer a short-lived storage solution, ideal for holding intermediate data or simplifying complex multi-step processing. They exist just long enough to get the job done, providing a handy scratch pad that vanishes automatically to keep your database clean.

The Basics

A temporary table is essentially a regular database table that lives in a special temporary storage area. It holds data just like any other table, with rows, columns, data types, and all that good stuff. But the difference is that a temp table is designed to be short-lived. Once you’re done with your database session or the specific process that created it, poof, it’s gone.

The beauty of temp tables lies in their disposable nature. You don’t have to worry about cluttering up your database with tables you only needed for five minutes, but forgot to drop once you were done with them.

Why Use Temp Tables?

You may be wondering why you would use a temp table when you could just as easily create a permanent table. Here are a few thoughts:

  • Breaking Down Complex Queries: Let’s say you’re analyzing customer purchase patterns across multiple product categories, and your query is turning into a nested monstrosity with subqueries inside subqueries. Instead of creating an unreadable mess, you can break it into logical chunks using temp tables. Store your intermediate results, make them readable, and build on them step by step.
  • Performance Optimization: You might have a complex join that runs slowly, but if you materialize some of the intermediate results into a temp table first, you can actually speed things up. This is especially true when you need to reference the same subset of data multiple times in a single process.
  • Data Transformation Pipelines: When you’re importing data from external sources or running ETL processes, temp tables give you a staging area. You can load raw data, clean it up, validate it, transform it, and then move it to permanent tables only when everything looks good.

Types of Temp Tables

Most database systems offer a couple of flavors of temporary tables, each with its own lifespan and visibility rules:

  • Local temporary tables are visible only to the session that created them. If you’re working in SQL Server, these are prefixed with a single hash mark (#). Create a table called #customer_summary, and only your connection can see it. Someone else could create their own #customer_summary at the same time, and you’d never collide.
  • Global temporary tables are visible to all sessions, but they’re still temporary. In SQL Server, these use a double hash mark (##). They are automatically dropped only when the creating session terminates and there are no other active sessions currently referencing the table. Other database systems handle this differently though. PostgreSQL and MySQL, for instance, treat all temporary tables as session-local by default.

Creating and Using Temp Tables

Here’s an example using the SQL Server syntax. Let’s say you’re analyzing employee performance data and want to identify top performers in each department.

Here’s how you might create and populate such a temporary table in SQL Server:

-- Create a temp table with aggregated metrics
CREATE TABLE #dept_performance (
    department_id INT,
    employee_id INT,
    total_projects INT,
    avg_completion_days DECIMAL(10,2),
    quality_score DECIMAL(5,2)
);

-- Populate it with calculated data
INSERT INTO #dept_performance
SELECT 
    e.department_id,
    e.employee_id,
    COUNT(p.project_id) as total_projects,
    AVG(DATEDIFF(day, p.start_date, p.completion_date)) as avg_completion_days,
    AVG(p.quality_rating) as quality_score
FROM employees e
JOIN projects p ON e.employee_id = p.assigned_to
WHERE p.completion_date >= DATEADD(year, -1, GETDATE())
GROUP BY e.department_id, e.employee_id;

In SQL Server we created the temp table using the standard CREATE TABLE statement (other RDBMSs use CREATE TEMPORARY TABLE for temp tables), and we populated it using the SELECT INTO ... SELECT statement. This populates the temp table from two existing permanent tables called employees and projects.

And here’s a query to find the top performers in each department:

SELECT 
    d.department_name,
    e.employee_name,
    dp.total_projects,
    dp.avg_completion_days,
    dp.quality_score
FROM #dept_performance dp
JOIN employees e ON dp.employee_id = e.employee_id
JOIN departments d ON dp.department_id = d.department_id
WHERE dp.quality_score >= 4.5
    AND dp.total_projects >= 3
ORDER BY d.department_name, dp.quality_score DESC;

You could technically do this in one massive query, but breaking it into steps can make it easier to debug, modify, and understand when you come back to it six months later.

To create the temporary table in other RDBMSs like PostgreSQL, MySQL, etc, you can use the following syntax:

CREATE TEMPORARY TABLE dept_performance (
...
);

So you’d drop the hash mark (#) and add the TEMPORARY keyword.

Table Variables

If you work with SQL Server, you may be familiar with table variables. These are similar to temp tables, but they’re not the same thing. A table variable (DECLARE @MyTable TABLE) is a lightweight, variable-scoped structure that SQL Server may keep entirely in memory for small datasets.

Because of this, table variables often work well for small or simple intermediate result sets, while temp tables are better when you need features like indexes, statistics, or larger data volumes.

Here’s an example of declaring a table variable and inserting data:

DECLARE @recent_orders TABLE (
    order_id INT,
    customer_id INT,
    order_total DECIMAL(10,2),
    order_date DATE
);

INSERT INTO @recent_orders
SELECT order_id, customer_id, total_amount, order_date
FROM orders
WHERE order_date >= DATEADD(day, -7, GETDATE());

Table variables do have some limitations. For example, you can’t create indexes on them after declaration (although you can define indexes implicitly via primary keys and UNIQUE constraints), and statistics aren’t maintained, which can lead to suboptimal query plans for larger datasets.

Indexes and Constraints on Temp Tables

You can add indexes, primary keys, and constraints to temp tables just like regular tables. This is especially useful when you’re working with large temporary datasets and need decent query performance.

CREATE TABLE #product_analysis (
    product_id INT PRIMARY KEY,
    category_name VARCHAR(100),
    revenue_last_quarter DECIMAL(12,2),
    units_sold INT,
    return_rate DECIMAL(5,2)
);

CREATE INDEX idx_category ON #product_analysis(category_name);
CREATE INDEX idx_revenue ON #product_analysis(revenue_last_quarter DESC);

These indexes help the query optimizer make better decisions when you’re joining or filtering on your temp table later.

Scope and Lifetime

Understanding when temp tables disappear is important, but the behavior does tend to vary across database systems:

  • SQL Server has the most straightforward model. Local temp tables (prefixed with #) exist until your session ends or you explicitly drop them. If you create a temp table inside a stored procedure, it automatically drops when the procedure finishes executing (which is usually what you want), but can catch you off guard if you’re trying to access it from outside the procedure. Global temp tables (##) stick around until the creating session ends and all other sessions stop referencing them, which can lead to situations where they linger longer than expected.
  • PostgreSQL takes a different approach. Temp tables are session-scoped by default, but you get more control through the ON COMMIT clause. Use ON COMMIT DELETE ROWS and the table structure persists but data gets wiped after each transaction. Use ON COMMIT DROP and the entire table disappears when the transaction ends. Without specifying, temp tables behave like regular tables within your session and drop when the session closes.
  • MySQL keeps it simple in that temporary tables exist for the duration of your connection and are automatically dropped when you disconnect. They’re completely invisible to other sessions, and there’s no concept of global temp tables. If your connection drops unexpectedly, MySQL cleans them up automatically.
  • Oracle uses a completely different approach. When you create a global temporary table, you’re actually creating a permanent structure that shows up in the data dictionary. Each session gets its own private set of data within that structure. You control data persistence with ON COMMIT DELETE ROWS (default, data clears after each transaction) or ON COMMIT PRESERVE ROWS (data persists until session ends). The table definition itself never automatically drops – you have to explicitly drop it like a regular table.

Temp Tables vs. Other Options

Temp tables aren’t your only option for handling intermediate results and complex queries. Here are a few others:

  • Common Table Expressions (CTEs) are great for making queries more readable, but they’re not materialized. They’re more like inline views that get evaluated as part of the main query. For simple cases, they’re cleaner than temp tables. For complex scenarios where you need to reference the same data multiple times, you might find temp tables easier to work with.
  • Derived tables (subqueries in the FROM clause) serve a similar purpose to CTEs but with messier syntax. They’re fine for quick one-offs but can get unwieldy fast.
  • Permanent staging tables are an option if you need more control over persistence and want to keep data around for debugging or auditing purposes. With this option you’ll have to manage cleanup yourself.
  • Table variables: If you’re using a DBMS that supports table variables (SQL Server), then this can be a viable option.

Best Practices

Here are some guidelines to keep in mind when working with temp tables:

  • Always explicitly drop temp tables when you’re done with them, especially in long-running processes or stored procedures that execute frequently. Waiting for automatic cleanup can lead to resource issues.
  • Be mindful of transaction log usage. Temp tables generate transaction log entries just like regular tables, which can become a problem if you’re moving massive amounts of data around.
  • Use appropriate data types and constraints. Just because it’s temporary doesn’t mean you should be sloppy. Proper data types prevent issues and help the query optimizer.
  • Consider whether you actually need a temp table. Sometimes a CTE or derived table is simpler and performs just as well. Temp tables can be very handy, but they’re not always the answer.

Common Mistakes

Here are some common mistakes that you should try to avoid when working with temp tables:

  • The most frequent mistake is creating temp tables inside loops. If you’re running a process that iterates through something (for example, processing orders one by one) and you create a temp table on each iteration without dropping it, you’re going to have problems. Some systems will error out because the table already exists. Others might create new instances with mangled names, leading to a mess.
  • Another issue is assuming temp tables are always faster. They can be, but they’re not magic. If you’re creating a temp table with millions of rows just to query it once, you might be better off with a well-written single query.
  • Lastly, forgetting about tempdb space (in SQL Server) or temp tablespace (in Oracle) can bite you. These special areas have finite space, and if your temp tables grow too large, you can fill them up and cause system-wide issues.

Summary

Temp tables are a fundamental tool in the SQL developer’s toolkit. They provide a middle ground between throwing everything into one gigantic query and creating permanent tables for every little thing. When you need to break down complex logic, stage data for processing, or optimize performance by materializing intermediate results, temp tables are often the right choice.

But don’t forget that they’re just a tool. Use them when they make sense, but don’t reach for them automatically just because they’re convenient. Sometimes another tool (such as a CTE or derived table) might be better suited to the job at hand.