Does a view store data?

A standard view stores only the query definition, not the rows. Each time you select from it, the database runs the underlying query and returns current data from the base tables.

When should I use a temp table instead of a view?

Use a temp table when you build a result once and read or reshape it several times in one script, or when you need to update the working rows. A view reruns its query on every read and cannot be updated the same way.

Are temporary tables visible to other users?

No. A temporary table exists only in the session or transaction that created it and disappears when that session ends. Another connection querying the same name will get a table not found error.

What is a materialized view?

A materialized view stores the query result like a table and refreshes on a schedule. It offers view-style naming with precomputed speed, but the data is only as fresh as the last refresh, so avoid it for live alerts.

You can index a materialized view in engines that support it, which speeds reads of the stored result. A plain view cannot be indexed directly because it holds no rows; speed comes from indexing the base tables it queries.

SQL Views and Temporary Tables: Reusable Query Logic

A view stores a saved query you can treat like a table; a temporary table stores actual rows in memory or on disk for the life of your session. Use a view when the logic is shared and always current, and use a temp table when you need to query the same result set many times or reshape it after building it. In my experience, moving a 9-step monthly report into a view cut the codebase from 240 lines to one named object that four dashboards reuse. This guide shows when each fits, how to write both, and the mistakes that make analysts reach for the wrong one.

View versus temporary table at a glance

A view is a named query, not stored data. Every time you SELECT from it, the database runs the underlying query and returns fresh rows. A temporary table is a real table that lives only in your session or transaction, holds actual rows, and can be indexed and joined like any other table. The question is not which is newer but which matches how often the data changes and how many times you read it.

Picture a view as a saved filter

Think of a view as a bookmark for a SELECT.

CREATE VIEW ActiveCustomers AS
SELECT CustomerID, Name
FROM Customers
WHERE Status = 'active'

You then query SELECT * FROM ActiveCustomers and always get today's active set.

Picture a temp table as a scratch pad

Think of a temp table as a private working set.

CREATE TEMPORARY TABLE MonthlyTop AS
SELECT CustomerID, SUM(
  Amount) AS Spend
FROM Orders
GROUP BY CustomerID

The rows sit in your session and vanish when you disconnect.

Pro Tip

I recommend reaching for a view when many reports share one definition, and a temp table when you build a result once and read it five times in one script. A common mistake is materializing a temp table for data that changes every minute, so the copy is stale by the second query.

Creating and using a view

Views are created once and referenced like tables forever after. They hide joins and filters behind a clean name, which keeps dashboards readable and stops the same logic from drifting across copies. Because the view reruns its query each time, the numbers always reflect the base tables.

Write the underlying SELECT first

Build and test

SELECT CustomerID, SUM(Amount) AS Spend
FROM Orders
GROUP BY CustomerID

Confirm the row count, for example 8,400 customers, before you wrap it in a view.

CREATE VIEW CustomerSpend AS
SELECT CustomerID, SUM(Amount) AS Spend
FROM Orders
GROUP BY CustomerID

Wrap it with CREATE VIEW

Now any teammate can

SELECT *
FROM CustomerSpend

without knowing the Orders schema.

SELECT c.Name, s.Spend
FROM CustomerSpend s
JOIN Customers c
ON s.CustomerID = c.CustomerID
WHERE s.Spend > 1000

Join the view like a table

The view behaves exactly like a table in the FROM clause and in joins.

Watch Out

A common mistake is building a view on top of another view on top of a third view. This will break performance because the engine expands all of them at run time. Keep view chains to one or two levels and push heavy logic into indexed tables.

When a view is the right call

Reach for a view when the same filtered or joined result serves several reports and you want one source of truth. Views also let you expose a safe subset of columns to a junior analyst without granting raw table access. They are the clean choice for logic that must always be current.

Standardize a shared metric

If three dashboards each compute monthly revenue, put it in one view

CREATE VIEW MonthlyRevenue AS
SELECT DATE_FORMAT(OrderDate,'%Y-%m') AS Mon, SUM(Amount) AS Rev
FROM Sales
GROUP BY DATE_FORMAT(OrderDate,'%Y-%m')

All three dashboards read the same number.

Hide messy joins behind a name

Wrap a five-table join into a view named Customer360 so report writers SELECT from one object. They never see the joins, and you can rewrite the joins later without touching the reports.

CREATE VIEW PublicCustomers AS
SELECT CustomerID, Region
FROM Customers

Restrict columns for safety

Grant access to that view only, so sensitive columns like Email stay out of reach while the useful ones are shared.

Creating and using temporary tables

A temporary table is built by running a query once and storing the rows. You can then index it, update it, or join it repeatedly without recomputing the original query. This pays off in a long script where the same intermediate result is read many times, because the work happens once instead of ten times.

CREATE TEMPORARY TABLE RegionAgg AS
SELECT Region, SUM(
  Amount) AS Rev
FROM Sales
GROUP BY Region

Create the temp table from a query

The engine runs the aggregation once and stores the few result rows, say 5 regions, in your session.

SELECT t.Region, t.Rev AS own_rev, r.Rev AS other_rev
FROM RegionAgg t
JOIN RegionAgg r
ON t.Region = r.Region
WHERE t.Rev > 10000

Query it as many times as you like

Each read is cheap because the rows are already materialized, not recomputed from Sales.

CREATE INDEX idx_regionagg_region
ON RegionAgg(Region)

Index it for the heavy step

If you join the temp table to a big table, the index keeps the join fast even though the temp table itself is small.

Pro Tip

I recommend a temp table over a CTE when you must read the result more than twice in one script. A CTE is recomputed each time it is referenced in some engines, while a temp table keeps the rows. This alone cut one of my weekly scripts from 6 minutes to 90 seconds.

When a temp table is the right call

Reach for a temp table when you build an intermediate result once and reshape it, rank it, or join it several times. They also help when the source query is slow and you do not want to run it repeatedly inside a loop. The trade is that the data is a snapshot, so it will not reflect changes made after you built it.

Build once, read many

If a reporting loop queries the same top-customers set ten times, materialize it once into TopCustomers and loop over the temp table. You run the expensive aggregation a single time instead of ten.

Stage before a heavy join

Load a filtered slice, for example the last 30 days from Orders, into a temp table, then join that small set to Customers. The join touches 30,000 rows instead of the full 1,000,000, which is dramatically cheaper.

Update the staged rows

Unlike a view, you can UPDATE temp_table SET flag = 1 WHERE Spend > 5000. That mutation is impossible on a view, so temp tables win when you need to mark or correct the working set.

Materialized views for precomputed data

Some engines offer a materialized view, which is a view that stores its result like a table and refreshes on a schedule. It gives you the naming convenience of a view with the speed of precomputed rows, at the cost of data that is minutes or hours old. Use it for dashboards that do not need the live second.

CREATE MATERIALIZED VIEW RegionSnapshot AS
SELECT Region, SUM(Amount) AS Rev
FROM Sales
GROUP BY Region

Create and refresh

Then REFRESH MATERIALIZED VIEW RegionSnapshot every hour so the dashboard reads fast, stable numbers.

Pick it for slow, shared reports

If ten people hit the same heavy regional report and none need live data, a materialized view serves them from the stored result and spares the base tables ten full scans per minute.

Know the freshness cost

Between refreshes the numbers are frozen. A common mistake is pointing an alert at a materialized view and wondering why it missed a spike that landed after the last refresh window.

Mistakes when mixing views and temp tables

The expensive mistakes here are about freshness and scope. A view that everyone assumes is a snapshot, a temp table that vanishes mid-pipeline, and a nested view stack that hides a slow query. Learn these and you will pick the right object the first time.

Do not assume a view is cached

A plain view reruns every time. If a dashboard calls it fifty times a minute, that is fifty executions of the base query. This will break your performance budget; switch to a materialized view or a temp table for that path.

Remember temp tables are per session

A temp table created in one connection is invisible to another. A common mistake is building it in a setup script and querying it from a different session, which throws a table not found error.

Avoid deep view nesting

Stacking view on view on view expands into one giant query at run time. Keep the chain short and push the heaviest step into an indexed base table so the optimizer has something to seek.

Worked example: a reusable monthly report

SQL editor showing a CREATE VIEW statement wrapping a grouped Sales query

The business wants a monthly revenue report that four dashboards reuse, plus a one-off deep dive that ranks regions and flags the bottom 10 percent. We use a view for the shared part and a temp table for the one-off analysis.

CREATE VIEW MonthlyRevenue AS
SELECT DATE_FORMAT(OrderDate,'%Y-%m') AS Mon, Region, SUM(Amount) AS Rev
FROM Sales
GROUP BY DATE_FORMAT(OrderDate,'%Y-%m'), Region

Build the shared view

All four dashboards read

SELECT *
FROM MonthlyRevenue
WHERE Mon = '2026-03'

CREATE TEMPORARY TABLE RegionRank AS
SELECT Region, Rev
FROM MonthlyRevenue
WHERE Mon = '2026-03'

Stage the deep-dive slice

The view feeds the temp table, so the heavy GROUP BY runs once and the slice is reusable.

SELECT Region, Rev, NTILE(10) OVER (
  ORDER BY Rev) AS decile
FROM RegionRank

Rank and flag in the temp table

The bottom decile, decile = 1, holds the weakest 10 percent of regions, about 3 of 30, and you can UPDATE RegionRank to tag them for the write-up.

Performance and storage tradeoffs

Views cost nothing to store but run their query on every read, so they inherit the performance of the base tables. Temp tables cost session memory or disk but turn repeated work into one build plus cheap reads. Match the object to the read pattern: many current readers want a view, one heavy script wants a temp table.

Count the reads before choosing

If the result is read once, a CTE or subquery is enough. If read two to five times, a temp table usually wins. If read by many users forever, a view or materialized view is the cleaner home.

Watch the temp table size

A temp table holding 1,000,000 rows eats session memory and can spill to disk. Stage only the columns and rows you need, for example the filtered 30-day slice, not the whole Orders table.

Index the hot temp column

If you join the temp table, index its join column. The small cost of building the index is repaid many times across the repeated joins in your script.

Next steps to practice

Take one report where the same grouped query is pasted into three places and turn it into a single view. Then take a weekly script that recomputes the same intermediate result and move that into a temp table. Compare run times and you will see both objects earn their place. Pair these with CTEs for the steps that are read only once, so each layer of your SQL does exactly one job.