Views are used quite often within SQL Server for a number of reasons.  They can be used to restrict access to tables (restrict the columns viewable or the records returned) , combine multiple tables into a single 'virtual table', or apply specific sets of formulas(like avg cost or an item).  A view is a virtual object, in that its definition exists within SQL Server and it can be queried just like a table, but has no physical form.  It is 'materialized' at query time.  An often overlooked type of view is called an Indexed View.

An Indexed View is basically a view that has a clustered index attached to it.  This means that the view is no longer a virtual object instantiated at query time, but takes up space and exists outside of the any query.  You might be asking yourself, 'Why would I do this?  Why not just have a table with the necessary data?' The answer is simple: Performance

The situation in which I built an Indexed View is as follows:  We have a DSS server with a table that maintains a history of transactions going back 18 years, containing over 370 million rows.  The server has 16 CPUs, 32GB of ram, and is connected to a 'communal' SAN.  There are numerous applications that need to calculate life to date quantity sold  for any given book (I am in the publishing industry) at any given time.  We found that calculating this on the fly was unpredictable, so we built an Indexed View.  The view contains the necessary aggregates, which in this case is a sum of qty, to satisfy the applications.  The neat piece is that as data is inserted/updated/deleted from the base table this index is built off of, the aggregate is maintained as well since it is part of the view.  What took tens of thousands of I/Os now takes dozens.  There is a trade-off for this: maintenance of the base table.

 Our base table is managed nightly and is 99.99% inserts.  We did notice during testing that any heavy modification of this table was taking a considerable amount of time when the Indexed View was there.  This was due to the fact that SQL Server had to maintain it for each and every row modified on the base table (sum of qty sold of every ISBN ever sold over 18 years).  Our nightly load process performs well within our SLA even with the Indexed View so the applications that require this data do not need to be looked at for modification as we can satisfy their requirements not by redesigning, but through the view.

If you have a similar situation where you need to provide calculated information over a large dataset, Indexed Views might provide you with the performance boost you are looking for.