What are the benefits of using a view on a temporary table in SQL Server


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:

  1. Performance-wise is it better to use a view than a temporary table?

  2. If the tables that the views are based on being constantly updated is a view still better than a temporary table?

  3. If I had to use a where clause against a view, am I better off using a temporary table?

  4. 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.