SQL Views

A view can be thought of as either a virtual table or a stored query. The data accessible through a view is not stored in the database as a distinct object. What is stored in the database is a SELECT statement. The result set of the SELECT statement forms the virtual table returned by the view. A user can use this virtual table by referencing the view name in Transact-SQL statements the same way a table is referenced. A view is used to do any or all of these functions:

• Restrict a user to specific rows in a table.

  For example, let an employee see only the rows recording their work in a labor-tracking table.

• Restrict a user to specific columns.

  For example, let employees who do not work in payroll see the name, office, work phone, and department columns in an employee table, but do not let them see any columns with salary information or personal information.

• Join columns from multiple tables so that they look like a single table.
• Aggregate information instead of supplying details.

  For example, present the sum of a column, or the maximum or minimum value from a column.

Views are created by defining the SELECT statement that retrieves data to be presented by the view. The data tables referenced by the SELECT statement are known as the base tables for the view. titleview in the pubs database is an example of a view that selects data from three base tables to present a virtual table of commonly needed data.

CREATE VIEW titleview

AS

SELECT title, au_ord, au_lname, price, ytd_sales, pub_id

FROM authors AS a

     JOIN titleauthor AS ta ON (a.au_id = ta.au_id)

     JOIN titles AS t ON (t.title_id = ta.title_id)

You can then reference titleview in statements in the same way you would reference a table.

SELECT *

FROM titleview

A view can reference another view. For example, titleview presents information that is useful for managers, but a company typically only discloses year-to-date figures in quarterly or annual financial statements. A view can be built that selects all the titleview columns except au_ord and ytd_sales. This new view can be used by customers to get lists of available books without seeing the financial information:

CREATE VIEW Cust_titleview

AS

SELECT title, au_lname, price, pub_id

FROM titleview

Views in Microsoft® SQL Server™ are updatable (can be the target of UPDATE, DELETE, or INSERT statements) so long as the modification only affects one of the base tables referenced by the view.

-- Increase the prices for publisher ‘0736’ by 10%.

UPDATE titleview

SET price = price * 1.10

WHERE pub_id = '0736'

GO

See Also

Views