What are the benefits of using a view on a temporary table in SQL Server
What are the benefits of using a view over temporary tables. I know you should use the view if the data is reused by other stored procedures, but:
-
Better to use a view than a temporary table?
-
If tables based on constantly updating views look better than a temporary table?
-
If I had to use a where clause for the view, would I be better off using a temporary table?
-
Finally, what are the advantages / disadvantages of using a view or temporary tables?
source to share
To find out, ask yourself if you need to reuse information:
- the view is the glorified SELECT and is mainly used for convenience
- you can process the view i.e. store it as a table and even index it. See question
- use a temporary table unless you will be reusing the structure many times, for example in a script that is executed from time to time by the View
- take up space (especially if they are materialized) and have many views that are difficult to maintain.
Also notice how the temporary tables are destroyed:
- if you create a temporary table #tbl it will be destroyed when it goes out of scope (like at the end of your script).
- you can create a temporary table like ## tbl (with two #s) and it will be destroyed when the connection ends.
source to share
-
In general, yes, since the view is only a stored selection, whereas a temporary table would require some use of tempdb.
-
It doesn't matter in this case. Views are not persisted - they return data from their underlying tables.
-
is also not different.
-
Views are persisted (i.e. the text of the select statement is saved) - they give you a layer above the database that allows you to modify the database without having to use clients using the view.
source to share