Views vs. Temp Tables

[jflmeing]

Which is better to use, a view or a temp table? I know temp tables are built in tempdb, but where is the data for a view stored - ins't a view similar to a temp table?

Thank you,
Jennifer

 

[cehepker]

Hello,

A view is like a query, it does not store any information. A temp table does store information for a finite period of time (until you drop it or the connection is severed).

As to which is better to use, that depends on your needs.

Carla

Documentation: A Shaft of light into a Coded world

 

[mkrausnick]

Usually it is better to use a view for two reasons:
First, with a view the data is always current whereas with a temp table the data is a "snapshot" of the data taken at a point of time, and it never changes even if the data in the source or parent table changes, and
second, as Carla points out, there is no data stored, which, depending on your situation, could be a huge space savings.

The downside to using views is that the Server has to collect all the data into a result set (basically it has to re-run the query to retrieve all the data) every time you want to use the view, whereas in a temp table the data is already stored and waiting for you.

Even so, usually you want to use views because you usually want the most current data.


Mike Krausnick