When building data-based applications using SQL Server™ as the backend, one design factor that can appreciably enhance recurrent data retrieval is the use of saved views.
A view is essentially a stored query that returns a result set in the form of a virtual table.
Like tables in a database, the data accessible in a view can be one or more named columns with one or more rows of data. Unlike actual tables, though, a view does not exist as a stored structure of fields and values. Instead, what is stored in the database is only the query ( the SELECT statement ) used to create the view. The virtual table returned by the view is dynamically built at run time when the view is referenced.
Views are employed mainly to simplify the retrieval of data from custom queries that are commonly shared or frequenly used within an application. For example, a complex query for a report that performs subqueries, aggregation, and joins to retrieve data from multiple tables can be defined once, and does not have to be submitted each time the report is needed. The view is used instead.
In general, queries suitable for defining into views are those that:
- involve more than one table,
- use columns with calculated expressions,
- use subqueries or aggregate functions, or
- are used often.
The following examples illustrate using views.
Now let’s explore how all these are done. The following sections assume you are familiar with basic SQL. If not, I suggest you go thru the brief backgrounder on Structured Query Language Basics. 