Views and Derived Tables

Think of a view as a virtual table. Simply put, a view is a named SELECT statement that dynamically produces a result set that you can further operate on. A view doesn't actually store any data. It acts as a filter for underlying tables in which the data is stored. The SELECT statement that defines the view can be from one or more underlying tables or from other views. To relieve users of the complexity of having to know how to write an outer join properly, we can turn the previous outer-join query into a view:

CREATE VIEW outer_view AS
(
SELECT   'Author'=RTRIM(au_lname) + ', ' + au_fname, 'Title'=title
FROM (titleauthor AS TA
FULL OUTER JOIN titles AS T ON (TA.title_id=T.title_id))
RIGHT OUTER JOIN authors AS A ON (A.au_id=TA.au_id)
WHERE
A.state <> 'CA' OR A.state IS NULL
)

Now, instead of formulating the outer join, we can simply query the outer-join view, outer_view. Then we can do a search for author names starting with Ri.

SELECT * FROM outer_view WHERE Author LIKE 'Ri%' ORDER BY Author

A derived table is a fancy name for the result of using another SELECT statement in the FROM clause of a SELECT statement. This works because the result of a SELECT statement is a table, which is exactly what the FROM clause requires. You can think of a view as a named derived table. A view is named, and its definition is persistent and reusable; a derived table is a completely dynamic, temporal concept. To show the difference, here's an equivalent LIKE 'Ri%' query using a derived table instead of a view:

SELECT *  FROM
(SELECT  'Author'=RTRIM(au_lname) + ',' + au_fname, 'Title'=title
FROM (titleauthor AS TA
FULL OUTER JOIN titles AS T ON (TA.title_id=T.title_id))
RIGHT OUTER JOIN authors AS A ON (A.au_id=TA.au_id)
WHERE A.state <> 'CA' OR A.state IS NULL ) AS T
WHERE T.Author LIKE 'Ri%'

You can insert, update, and delete rows in a view but not in a derived table. Keep in mind that you're always modifying rows in the tables on which a view is based, because the view has no data of its own. The definition of the view is persistent, but the data is not. The data exists only in the view's base tables. Think of it as modifying data through a view rather than modifying data in a view. Some limitations to modifying data through a view exist; the SQL Server documentation explains them.

example:
CREATE VIEW CA_authors AS
(
SELECT * FROM authors WHERE state='CA'
)
GO

SELECT * FROM CA_authors
-- (returns 15 rows)

BEGIN TRAN

UPDATE CA_authors SET state='IL'
SELECT * FROM CA_authors
-- (returns 0 rows)

ROLLBACK TRAN

Altering Views
SQL Server allows you to alter the definition of a view. The syntax is almost identical to the syntax for creating the view initially:

ALTER VIEW view_name  [(column [,…n])]
[WITH ENCRYPTION] AS
select_statement [WITH CHECK OPTION]

Modifying Data Through View
You can specify INSERT, UPDATE, and DELETE statements on views as well as on tables, although you should be aware of some restrictions and other issues. Modifications through a view end up affecting the data in an underlying base table (and only one such table) because views don't store data. All three types of data modifications work easily for single-table views, especially in the simplest case, in which the view exposes all the columns of the base table. But a single-table view doesn't necessarily have to expose every column of the base table—it can restrict the view to a subset of columns only.

If your views or your desired data modification statements don't meet the requirements for actually updating through the view, you can create INSTEAD OF TRIGGERS on a view. The code in an INSTEAD OF TRIGGER is executed instead of a specified modification operation, and this is the only kind of trigger you can place on a view. (The other kind of trigger is an AFTER TRIGGER.) For example, suppose you have a view based on a join between two tables, and you want to update columns in both tables. An INSTEAD OF TRIGGER on UPDATE of your view can issue two separate UPDATE statements so that both tables can actually be updated. I'll discuss INSTEAD OF TRIGGERS along with AFTER TRIGGERS in. The remaining discussion in this section will assume there are no INSTEAD OF TRIGGERS on your views and that you want to modify the base table data directly through the view.
For any direct modification, all the underlying constraints of the base table must still be satisfied. For example, if a column in the base table is defined as NOT NULL and doesn't have a DEFAULT declared for it, the column must be visible to the view and the insert must supply a value for it. If the column weren't part of the view, an insert through that view could never work because the NOT NULL constraint could never be satisfied. And, of course, you can't modify or insert a value for a column that's derived by the view, such as an arithmetic calculation or concatenation of two strings of the base table. (You can still modify the nonderived columns in the view, however.)
Basing a view on multiple tables is far less straightforward. You can issue an UPDATE statement against a view that is a join of multiple tables, but only if all columns being modified (that is, the columns in the SET clause) are part of the same base table. An INSERT statement can also be performed against a view that does a join only if columns from a single table are specified in the INSERT statement's column list. Only the table whose columns are specified will have a row inserted: any other tables in the view will be unaffected. A DELETE statement can't be executed against a view that's a join because entire rows are deleted and modifications through a view can affect only one base table. Because no columns are specified in a delete, which table would the rows be deleted from?
In the real world, you would probably have little use for an INSERT statement against a view with a join because all but one table in the underlying query would be totally ignored. But the insert is possible. The following simple example illustrates this:

CREATE TABLE one
(   col11    int    NOT NULL,
    col12    int    NOT NULL
}
CREATE TABLE two
(
    col21    int    NOT NULL,
    col22    int    NOT NULL
)
GO

CREATE VIEW one_two

AS
(SELECT col11, col12, col21, col22
FROM one LEFT JOIN two ON (col11=col21))
GO

INSERT one_two (col11, col12)

VALUES (1, 2)
SELECT * FROM one_two

Here's the result:

col11   col12    col21    col22
-----       -----       -----        -----
1          2          NULL     NULL

Notice that this insert specifies values only for columns from table one, and only table one gets a new row. Selecting from the view produces the row only because LEFT OUTER JOIN is specified in the view. Because table two contains no actual rows, a simple equijoin would have found no matches. Although the row would still have been inserted into table one, it would have seemingly vanished from the view. You could specify the view as an equijoin and use WITH CHECK OPTION to prevent an insert that wouldn't find a match. But the insert must still affect only one of the tables, so matching rows would have to already exist in the other table. I'll come back to WITH CHECK OPTION in the next section; for now, here's how it would be specified:

CREATE VIEW one_two_equijoin

AS
(SELECT col11, col12, col21, col22
FROM one JOIN two ON (col11=col21))
WITH CHECK OPTION
GO

If you try to specify all columns with either view formulation, even if you simply try to insert NULL values into the columns of table two, an error results because the single INSERT operation can't be performed on both tables. Admittedly, the error message is slightly misleading.

INSERT one_two (col11, col12, col21, col22)

VALUES (1, 2, NULL, NULL)

Server: Msg 4405, Level 16, State 2, Line 1

View or function 'one_two' is not updatable because the modification

affects multiple base tables..

Similarly, a DELETE against this view with a join would be disallowed and results in the same message:

DELETE one_two

Server: Msg 4405, Level 16, State 1, Line 1

View or function 'one_two' is not updatable because the modification

affects multiple base tables referenced.

The UPDATE case isn't common, but it's somewhat more realistic. You'll probably want to avoid allowing updates through views that do joins (unless you have INSTEAD OF TRIGGERS), and the next example shows why.

Given the following view,

CREATE VIEW titles_and_authors

AS
( SELECT A.au_id, A.au_lname, T.title_id, T.title
FROM
authors AS A
FULL OUTER JOIN titleauthor AS TA ON (A.au_id=TA.au_id)
FULL OUTER JOIN titles AS T ON (TA.title_id=T.title_id)
)

selecting from the view

SELECT * FROM titles_and_authors
The following UPDATE statement works fine because only one underlying table, authors, is affected. This example changes the author's name from DeFrance to DeFrance-Macy.

UPDATE TITLES_AND_AUTHORS

SET au_lname='DeFrance-Macy'
WHERE au_id='722-51-5454'
(1 row(s) affected)

This UPDATE statement yields an error, however, because two tables from a view can't be updated in the same statement:

UPDATE TITLES_AND_AUTHORS
SET au_lname='DeFrance-Macy', title='The Gourmet Microwave Cookbook'
WHERE au_id='722-51-5454' and title_id='MC3021'
Server: Msg 4405, Level 16, State 2, Line 1

View or function 'TITLES_AND_AUTHORS' is not updatable because the modification affects multiple base tables referenced.

Download Artical