A view is an alternative way of looking at the data in one or more tables. You can think of a view as a frame through which you can see the particular data in which you're interested. That is why one speaks of looking at data or changing data "through" a view.
A view is derived from one or more real tables whose data is physically stored in the database. The tables from which a view is derived are called its base tables or underlying tables. A view can also be derived from another view.
The definition of a view, in terms of the base tables from which it is derived, is stored in the database. No separate copies of data are associated with this stored definition.
When you modify the data you see through a view, you are actually changing the data in the underlying base tables. Conversely, changes to data in the underlying base tables are automatically reflected in the views derived from them.
The first example is a view derived from the titles table. Suppose you are interested only in books priced higher than $15.00 and for which an advance of more than $5000 was paid. This straightforward select statement would find the rows that qualify:
SELECT *
FROM titles
WHERE price > $15
AND advance > $5000
Now suppose you have a lot of retrieval and update operations to do on this collection of data. You could, of course, combine the conditions shown in the previous query with any command that you issue. However, for convenience, you can create a view in which just the records of interest are visible:
CREATE VIEW hiprice
AS SELECT *
FROM titles
WHERE price > $15
AND advance > $5000
When SQL Server receives this command, it does not actually execute the select statement that follows the keyword as . Instead, it stores the select statement, which is in fact the definition of the view hiprice , in the system table syscomments . Entries are also made in sysobjects and in syscolumns for each column included in the view.
Now, when you display or operate on hiprice , SQL Server combines your statement with the stored definition of hiprice . For example, you can change all the prices in hiprice just as you can change any other table:
UPDATE hiprice
SET price = price * 2
SQL Server actually finds the view definition in the system tables and converts this update command into the statement:
UPDATE titles
SET price = price * 2
WHERE price > $15
AND advance > $5000
CREATE VIEW pub_view2
AS SELECT Publisher = pub_name, city, state
FROM publishers
Note:
You can even re-name some attributes in the view ! In the above example, the attribute pub_name was given the name 'Publisher' in the view pub_view2.
Here is a view definition statement that creates a view with a computed column generated from the columns price , royalty , and total_sales :
CREATE VIEW accounts (title, advance, amt_due)
AS SELECT titles.title_id, advance, (price * royalty /100 ) * total_sales
FROM titles, roysched
WHERE price > $15
AND advance > $5000
AND titles.title_id = roysched.title_id
AND total_sales BETWEEN lorange AND hirange
In this example, a list of columns must be included in the create clause, since there is no name that can be inherited by the column computed by multiplying together price , royalty , and total_sales . The computed column is given the name amt_due . It must be listed in the same position in the create clause as the expression from which it is computed is listed in the select clause.
Similarly, a view definition that includes a built-in function must include column names in the create clause.
CREATE VIEW categories (category, average_price)
AS SELECT type, avg(price)
FROM titles
GROUP BY type
NOTE:
The operations update, insert or delete referring to any
view with a built-in function, or computed column, are ILLEGAL ! WHY ?.
You can create a view derived from more than one base table. Here's an example of a view derived from both the authors and the publishers tables. The view contains the names and cities of the authors that live in the same city as a publisher, along with each publisher's name and city.
CREATE VIEW cities (authorname, acity, publishername, pcity)
AS SELECT au_lname, authors.city, pub_name, publishers.city
FROM authors, publishers
WHERE authors.city = publishers.city
You can define a view in terms of another view, as in this example:
CREATE VIEW hiprice_computer
AS SELECT title, price
FROM hiprice
WHERE type = 'popular_comp'
Finally, if you want to delete a view, just use the drop view command. The usage is:
DROP VIEW hiprice_computer
Which will remove the view called hiprice_computer from the DB.
One reason is to dispaly only relevant information to selected users. An even more important reason is to allow different users to have access to different data in a a Database, or even within a table. You can use the commands grant and revoke to allow certain users to see only certain views.
In the following examples, we can see how you can set up restricted access to a database using views.
Example 1
You may want to allow all users to see some of the contents of the titles table. All users may be allowed to see (but not modify) title related information, but not to see the infomration related to money and sales (the attributes called price, advance, and total_sales). You may do this by creating a view called bookview which has all columns of the titles table except for the money related ones. Then you can use the following sequence of commands:
revoke all on titles to public
grant select on bookview to public
NOTES:
The revoke command takes all privileges from all users. (public is a recognized keyword, and stands for all users of the database.)
The grant command allows all users to see (using select) the view called bookview.
Of course, you may need to modify the data on the table when information changes. This authority (to modify data) is only provided, say, to users called sales. Then you may use the following:
grant all on titles to sales