Transact-SQL User's Guide

Home

Transact-SQL User's Guide

Chapter 9 Views: Limiting Access to Data

Retrieving data through views

When you retrieve data through a view, Adaptive Server checks to make sure that all the database objects referenced anywhere in the statement exist and that they are valid in the context of the statement. If the checks are successful, Adaptive Server combines the statement with the stored definition of the view and translates it into a query on the view's underlying tables. This process is called view resolution.

Consider the following view definition statement and a query against it:

create view hiprice 
as select * 
from titles 
where price > $15 
and advance > $5000

select title, type 
from hiprice 
where type = "popular_comp"

Internally, Adaptive Server combines the query of hiprice with its definition, converting the query to:

select title, type 
from titles 
where price > $15 
and advance > $5000 
and type = "popular_comp"

In general, you can query any view in any way just as if it were a real table. You can use joins, group by clauses, subqueries, and other query techniques on views, in any combination. Note, however, that if the view is defined with an outer join or aggregate function, you may get unexpected results when you query the view. See "Views derived from other views".

You can use select on text and image columns in views. However, you cannot use readtext and writetext in views.

View resolution

When you define a view, Adaptive Server verifies that all the tables or views listed in the from clause exist. Similar checks are performed when you query through the view.

Between the time a view is defined and the time it is used in a statement, things can change. For example, one or more of the tables or views listed in the from clause of the view definition may have been dropped. Or one or more of the columns listed in the select clause of the view definition may have been renamed.

To fully resolve a view, Adaptive Server verifies that:

All the tables, views, and columns from which the view was derived still exist.
The datatype of each column on which a view column depends has not been changed to an incompatible type.
If the statement is an update, insert, or delete, it does not violate the restrictions on modifying views. These are discussed under "Modifying data through views".

If any of these checks fails, Adaptive Server issues an error message.

Redefining views

Adaptive Server allows you to redefine a view without forcing you to redefine other views that depend on it, unless the redefinition makes it impossible for Adaptive Server to translate the dependent view.

For example, the authors table and three possible views are shown below. Each succeeding view is defined using the view that preceded it: view2 is created from view1, and view3 is created from view2. In this way, view2 depends on view1 and view3 depends on both the preceding views.

Each view name is followed by the select statement used to create it.

view1:

create view view1
as select au_lname, phone 
from authors 
where postalcode like "94%"

view2:

create view view2
as select au_lname, phone 
from view1 
where au_lname like "[M-Z]%"

view3:

create view view3
as select au_lname, phone 
from view2 
where au_lname = "MacFeather"

The authors table on which these views are based consists of these columns: au_id, au_lname, au_fname, phone, address, city, state, and postalcode.

You can drop view2 and replace it with another view, also named view2, that contains slightly different selection criteria, such as:

create view view2
as select au_lname, phone
from view3
where au_lname like "[M-P]"

view3, which depends on view2, is still valid and does not need to be redefined. When you use a query that references either view2 or view3, view resolution takes place as usual.

If you redefine view2 so that view3 cannot be derived from it, view3 becomes invalid. For example, if another new version of view2 contains a single column, au_lname, rather than the two columns that view3 expects, view3 can no longer be used because it cannot derive the phone column from the object on which it depends.

However, view3 still exists and you can use it again by dropping view2 and re-creating view2 with both the au_lname and the phone columns.

In short, you can change the definition of an intermediate view without affecting dependent views as long as the select list of the dependent views remains valid. If this rule is violated, a query that references the invalid view produces an error message.

Renaming views

You can rename a view using sp_rename:

sp_rename objname , newname

For example, to rename titleview to bookview, enter:

sp_rename titleview, bookview

Follow these conventions when renaming views:

Make sure the new name follows the rules used for identifiers discussed under "Identifiers".
You can change the name only of views that you own. The Database Owner can change the name of any user's view.
Make sure the view is in the current database.

Altering or dropping underlying objects

You can change the name of a view's underlying objects. For example, if a view references a table entitled new_sales, and you rename that table to old_sales, the view will work on the renamed table.

However, if a table referenced by a view has been dropped, and someone tries to use the view, Adaptive Server produces an error message. If a new table or view is created to replace the one that was dropped, the view again becomes usable.

If you define a view with a select * clause, and then alter the structure of its underlying tables by adding columns, the new columns do not appear. This is because the asterisk shorthand is interpreted and expanded when the view is first created. To see the new columns, drop the view and re-create it.


	Expand Search