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?
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.
Best,
Tsvetelin