Differences between views and temp tables

In a relational database like SQL Server, views provide a way of giving users a way to work with specific portions of a larger schema. Temporary tables are another means of providing end users with a subset or collation of data from base tables, and at first glance it seems that these are similar methods of achieving the same end. So it is important to understand what the difference is between a view and a temp table.

A temp table is a base table that is not stored in the database. Instead it only exists while the database session remains active, and it must be populated each session with data using SQL INSERT commands. Similarly, a view is not stored with data but with a query that will retrieve data. However, views exist only for a single query, and each time you generate a view it is recreated from current data. In contrast, a temp table exists for the entire database session, and once populated it retains those records until the session ends.

Therefore, the data in a view is always current because it is generated dynamically, whereas the data in a temp table reflects the state of the database at the time it was populated. Because temp tables are actually base tables, they can be updated in the same way as a base table, but only views that meet certain criteria can be used for DML operations.

The key determining factor in choosing between a view or a temp table is performance. If you are going to use data only once, then a view will perform better than a temp table because you don't need to create and populate the base structure, plus a view will guarantee that the results returned are always current. But if you are going to use data repeatedly during a database session then a temp table provides better performance because it only needs to be created once. As always, a trade-off is involved, and you need to be aware of the risk of outdated data with a temp table, and the restrictions on views for manipulating data.

For more information on #temp tables and @table variables go to http://databases.aspfaq.com