Super learner
This user is a Super Learner. To become a Super Learner, you need to reach Level 8.
Last answered:

22 May 2023

Posted on:

21 May 2023


What are advantages of using temporary tables compared to views?

Do we need to use temporary tables if we have views which live longer than during current session?

1 answers ( 0 marked as helpful)
Posted on:

22 May 2023


Hi Ruslan!
Thanks for reaching out.

Arguments to use a temporary table:
- you can run several queries against it
- you can fill it using several consecutive queries (because they are easier to handle than a large UNION, or because you have to build it up in steps)
- you can put indexes on the temp table to speed up the following queries

Arguments to use a view:
- The underlying query is simple, yet complicated enough to be hidden in a view
- All its use will happen in a single query, so you can put your view into a "with" clause, which you can refer to from several points in your query

- a view is like a macro or alias to an underlying query, so when you query the view, you are guaranteed to see the current data in the source tables. Whereas temporary tables make a copy of data, but don't keep the copy in sync with the source tables

- a view does not use extra storage, whereas temporary table uses

There are too many different scenarios and exception cases. You should learn how each feature works, and make your decision according to the task.

Hope this helps.

Submit an answer