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 database 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.
– Restrict a user to specific columns.
– Join columns from multiple tables so that they look like a single table.
– Aggregate information instead of supplying details.
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.
Example1:
CREATE VIEW SimpleView
AS
SELECT title, au_lname, ytd_sales, pub_id
FROM authors
GO
Although your database table may contain a lot fields, in the view you can specify only the ones that you need.
Example2:
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)
GO
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:
Example:
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.
Example:
— Increase the prices for publisher ‘0736’ by 10%.
UPDATE titleview
SET price = price * 1.10
WHERE pub_id = '0736'
GO