what is the difference (system resource wise) between using views and temporary tables?

Question

what is the difference (system resource wise) between using views and temporary tables? I have a complex report that will be built from numerous tables. I am trying to determine if I should use a series of views or temp tables (SQL 2008). Is there a difference?

Answer 1

A view is just an alias for the query.

A temporary table materializes the resultset.

Views, being just aliases, don't take time to be filled, but they may be less performant when being selected from.

Temporary tables take some time (and effort) to be populated but can be more efficient.

Note that SQL Server optimizer can create a temporary table in runtime from a view (which you will see in the query statistics and plan as Worktable in tempdb), index it and populate it with the rows, and even add the missing rows on demand (these operations are called Eager Spool and Lazy Spool)

This makes the view rewinding much more efficient: instead of reevaluating the whole view each time its results are needed, the results are stored in a temporary table either all at once (as in Eager Spool), or incrementally (as in Lazy Spool).

SQL Server, though, can materialize views by creating clustered indexes on them, though not all views can be indexed (to be indexable, the view must meet certain conditions described here).

Answer 2

View is just a "fixed" SELECT statement with a name. If you have complex query, or in other case, if you want to show only partial data from the table you use view. Views exists in the database you have created it in.

Temporary table is the table like any others, but it is stored in tempdb database and is deleted after you finish stored procedure, etc. (local table). There can be seen only in the scope of the procedure you use it in. There are also global temporary tables, which can be seen outside the procedure.

Answer 3

chances are that an indexed view would probably work for you more than inserting data into temp table/s. also, instead of using a series of views you'd be better of consolidating into one view. it's much easier to manage and will most likely improve performance. also, the right indexes on your source tables will also improve performance.