Temp Table vs View in SQL: A Saved Answer or a Saved Question

1. What each one is

Both are created from a SELECT and both are then queried like a table, which is exactly why they get confused.

CREATE VIEW v_region AS
  SELECT region, COUNT(*) AS orders, SUM(units*unit_price) AS revenue
  FROM orders GROUP BY region;

CREATE TEMP TABLE t_region AS
  SELECT region, COUNT(*) AS orders, SUM(units*unit_price) AS revenue
  FROM orders GROUP BY region;

The difference is what got stored. The view stored the text of the query and nothing else; it holds no rows and takes no space. The temp table ran the query once and stored the rows that came out.

Read either one and you get the same four rows:

East  | 100000 | 86499255
North | 100000 | 33500090
South | 100000 | 86999300
West  | 100000 | 68000200

Identical output, and from here on everything about them differs.

2. The measurement

Before the explanation: reading four rows from each. Say whether you expect a difference worth caring about.

SELECT * FROM v_region      467.6 ms
SELECT * FROM t_region        0.0 ms

Four rows either way. The view took nearly half a second because reading it means running a GROUP BY over 400,000 rows, every time. The temp table took no measurable time because the four rows are already sitting there.

That gap compounds with use. Reading each of them ten times in a row:

view        4688.8 ms
temp table     0.2 ms

The view did the whole aggregate ten times. This is the concrete reason a dashboard built on a stack of views can be slow in a way that looks mysterious: each panel reads a view, each view runs its query, and a view built on another view runs both.

Say out loud what you are actually paying for when you accept that cost. You are paying for the numbers to be current at the moment of reading, which is the next section.

3. The freshness trade, shown

One hundred new North orders are inserted into the base table. Neither the view nor the temp table is touched. Then both are read again.

the view       North | 100100 | 33600090
the temp table North | 100000 | 33500090

The view noticed. The temp table did not, and never will, because it is a copy of what the answer was at the moment it was made.

Both behaviours are correct and both are what somebody wants. A view is right when the answer must reflect the data now: an operational dashboard, a definition other people build on, anything where being out of date is the failure. A temp table is right when the answer must not move underneath you: a multi-step analysis where every step has to see the same numbers, a report that has to reconcile across ten queries, anything you will be asked to explain tomorrow.

The second case is underrated. If you run four queries against live data over an hour, they can legitimately disagree with each other, and you will spend the afternoon looking for a bug that is really the data arriving. Freezing the input at the start makes the whole analysis internally consistent, and that is a feature rather than staleness.

4. Refreshing a temp table

Being frozen is only a problem if you cannot easily unfreeze it, and you can. Two statements, and it is current again:

DELETE FROM t_region;
INSERT INTO t_region
  SELECT region, COUNT(*), SUM(units*unit_price) FROM orders GROUP BY region;

the temp table after the refresh    North | 100100 | 33600090

Now it matches the view. Which gives you the pattern that solves most of the tension on this page: refresh deliberately, at a moment you choose, and know when it happened. A temp table refreshed at the top of your script is a snapshot with a timestamp you can state, which is a much better thing to hand somebody than "it was live at whatever moment each query happened to run".

Keep the defining query in one place rather than typing it twice. In practice that means writing the aggregate once, as a view, and having the temp table load from the view: the view is the definition and the temp table is today's copy of it. That combination is worth more than either alone.

5. Only one of them can be indexed

Before the explanation: both behave like tables in a query. Say whether you can put an index on each.

CREATE INDEX ix_t ON t_region(region);      index created
CREATE INDEX ix_v ON v_region(region);      views may not be indexed

The error message is the explanation. An index is a sorted structure over stored rows, and a view has no stored rows to sort. There is nothing to build.

That matters more than it sounds when the intermediate result is large and you are going to join it to something. A temp table of a million rows with an index on the join key behaves like any other table; the same result as a view is re-derived on every join attempt and cannot be indexed at all. If you are doing anything repetitive with an intermediate result, that alone is often the deciding argument, and the rest of what indexes do is in when to index a table.

6. Scope: who else can see it

A view is a permanent object in the database. It appears in the catalogue, other people can query it, and it survives disconnection. A temporary table is private to your connection and disappears when you disconnect.

Opening a second connection to the same database and asking for each of them:

sees the view       : 4 rows
sees the temp table : no such table: t_region

Both halves of that are useful. The view being shared is the point of a view: it is how a definition gets used consistently by several people instead of being re-typed slightly differently in each of their queries. The temp table being private is also the point: you can create, drop and recreate it freely without affecting anybody, and you cannot leave a mess behind, because it cleans itself up when you disconnect.

The corollary catches people out. If your reporting tool opens a new connection per query, a temp table created in one query will not exist in the next. Where that is the case, the answer is usually a real table in a scratch schema rather than a temporary one.

7. CTE, temp table, view: which when

There is a third thing that does a similar-looking job, and choosing between all three is easier than it looks because they live on different timescales.

The rule of thumb that follows: use a CTE when the intermediate result is needed once, inside one statement, which is most of the time and is what named steps are for. Use a temp table when several statements need the same intermediate result, or when it is expensive and you will read it repeatedly, or when you need it to hold still. Use a view when other people, or other queries, need the same definition and must always get the current answer.

One case is worth calling out because it is common and slow. A CTE referenced three times inside one query may be computed three times, depending on the engine. If it is expensive and used more than once, that is a temp table, not a CTE.

8. Materialised views, which are both

A materialised view is a view that stores its rows: a defined query, kept in the catalogue like a view, with the result written down like a table. It can be indexed, it is shared, and it is stale until refreshed.

-- PostgreSQL
CREATE MATERIALIZED VIEW mv_region AS SELECT ... ;
REFRESH MATERIALIZED VIEW mv_region;

That is the combination most reporting setups actually want: the definition lives in one place, everybody reads the same object, reads are fast, and a scheduled refresh decides how fresh "fresh" means. The cost is that somebody has to own the refresh, and a materialised view nobody refreshes is the worst of all worlds, because it looks like a live object and is not.

Support varies. PostgreSQL and Oracle have them under that name. SQL Server has indexed views, which update automatically and come with restrictions on what the query may contain. MySQL and SQLite have neither, and the standard workaround is exactly the pattern in section four: a real table plus a scheduled job that reloads it.

Picture the slowest dashboard you have to look at. How many of its panels are reading a view that recomputes an aggregate over the whole table, and how current does each of them genuinely need to be?

Edge cases worth knowing

Six that come up.

Views built on views. Each layer re-runs, so a three-deep stack runs three queries per read. This is the commonest cause of an inexplicably slow reporting layer, and it is invisible until you read the definitions.

A temp table that outlives its usefulness. It lasts for the session, and a long-lived connection in a notebook can be a session for days. If numbers stop matching production, check when the snapshot was taken before checking anything else.

Connection pooling. Tools that hand out a different connection per query will lose your temp table between statements, and the error looks like the table was never created.

Temp table name collisions. They are per-session, so two people can each have a t_region and neither affects the other. That is convenient and it means a name in a shared script tells you nothing about whose data is in it.

Views hide their cost. A query joining two views is a query joining two aggregates over full tables, and its plan will show that, which is another reason to read plans rather than judge queries by their length.

Disk and memory. A temp table is real data somewhere, in memory or in a temporary file, so a snapshot of a hundred million rows is not free. When the intermediate result is that big, filter it down first; the rest of that argument is in handling large datasets.