what are the advantages of using a view over temporary tables. I know that you should use a view if the data is re-used by other stored procedures but:
Performance-wise is it better to use a view than a temporary table?
If the tables that the views are based on being constantly updated is a view still better than a temporary table?
If I had to use a where clause against a view, am I better off using a temporary table?
Finally, what are the advantages/disadvantages of using a view or temporary tables?
To discern, ask yourself if you need to reuse the information:
- a view is a glorified SELECT and it's used mainly for convenience
- you can materizalize a view, i.e. store it as a table and even index it. See this question
- use a temp table if you will not reuse the structure many times, like in a script that runs every now and then
- views will take space (especially if materialized) and having many views is difficult to maintain
Also note how temp tables are destroyed:
- if you create a temp table #tbl, it will be destroyed when it goes out of scope (e.g. at the end of the script).
- you can however create a temp table like ##tbl (with two #) and it will be destroyed when the connection ends.