Transact-SQL User's Guide

Home

Transact-SQL User's Guide

Chapter 9 Views: Limiting Access to Data

Creating views

View names must be unique for each user among the already existing tables and views. If you have set quoted_identifier on, you can use a delimited identifier for the view. Otherwise, the view name must follow the rules for identifiers given in "Identifiers".

You can build views on other views and procedures that reference views. You can define primary, foreign, and common keys on views. However, you cannot associate rules, defaults, or triggers with views or build indexes on them. You cannot create temporary views or views on temporary tables.

create view syntax

Here is the full syntax for create view:

create view [owner .]view_name  
     [(column_name  [, column_name ]...)] 
     as select [distinct] select_statement
     [with check option]

As illustrated in the create view example in "View examples", you need not specify any column names in the create clause of a view definition statement. Adaptive Server gives the columns of the view the same names and datatypes as the columns referred to in the select list of the select statement. The select list can be designated by the asterisk (*), as in the example, or it can be a full or partial list of the column names in the base tables.

To build views that do not contain duplicate rows, use the distinct keyword of the select statement to ensure that each row in the view is unique. However, you cannot update distinct views.

You can always specify column names. However, if any of the following are true, you must specify column names in the create clause for every column in the view:

One or more of the view's columns are derived from an arithmetic expression, an aggregate, a built-in function, or a constant.
Two or more of the view's columns would otherwise have the same name. This usually happens because the view definition includes a join, and the columns being joined have the same name.
You want to give a column in the view a different name than the column from which it is derived.
You can also rename columns in the select statement. Whether or not you rename a view column, it inherits the datatype of the column from which it is derived.

Here is a view definition statement that makes the name of a column in the view different from its name in the underlying table:

create view pub_view1 (Publisher, City, State) 
as select pub_name, city, state 
from publishers

Here is an alternate method of creating the same view but renaming the columns in the select statement:

create view pub_view2 
as select Publisher = pub_name, 
City = city, State = state 
from publishers

The examples of view definition statements in the next section illustrate the rest of the rules for including column names in the create clause.

Local variables cannot be used in view definitions.

Using the select statement with create view

The select statement in the create view statement defines the view. You must have permission to select from any objects referenced in the select statement of a view you are creating.

You can create a view using more than one table and other views by using a select statement of any complexity.

There are a few restrictions on the select statements in a view definition:

You cannot include order by or compute clauses.
You cannot include the into keyword.
You cannot reference a temporary table.

View definition with projection

To create a view with all the rows of the titles table, but with only a subset of its columns, enter:

create view titles_view 
as select title, type, price, pubdate 
from titles

Note that no column names are included in the create view clause. The view titles_view inherits the column names given in the select list.

View definition with a computed column

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

There is no name that can be inherited by the column computed by multiplying together price, royalty, and total_sales, so you must include the list of columns in the create clause. The computed column is named 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.

View definition with an aggregate or built-in function

A view definition that includes an aggregate or built-in function must include column names in the create clause. For example:

create view categories1 (category, average_price) 
as select type, avg(price) 
from titles 
group by type

If you create a view for security reasons, be careful when using aggregate functions and the group by clause. The Transact-SQL extension that does not restrict the columns you can include in the select with group by may also cause the view to return more information than required. For example:

create view categories2 (category, average_price) 
as select type, avg(price) 
from titles 
where type = "business"

In the above case, you may have wanted the view to restrict its results to "business" categories, but the results have information about other categories. For more information about group by and this group by Transact-SQL extension, see "Organizing query results into groups: the group by clause".

View definition with a join

You can create a view derived from more than one base table. Here is 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

Views used with outer joins

If you define a view with an outer join, and then query the view with a qualification on a column from the inner table of the outer join, the query behaves as though the qualification were part of the WHERE clause of the view, not part of the ON clause of the outer join in the view. Thus the qualification operates only on rows AFTER the outer join is complete. For example, the qualification operates on NULL extended rows if the outer join condition is met, and eliminates rows accordingly.

The following rules determine what types of updates you can make to columns through join views:

delete statements are not allowed on join views.
insert statements are not allowed on join views created with check option.
update statements are allowed on join views with check option. The update fails if any of the affected columns appears in the where clause, in an expression that includes columns from more than one table.
If you insert or update a row through a join view, all affected columns must belong to the same base table.

Views derived from other views

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"

distinct views

You can ensure that the rows contained in a view are unique, as in this example:

create view author_codes
as select distinct au_id
from titleauthor

A row is a duplicate of another row if all of its column values match the same column values contained in another row. Two null values are considered to be identical.

Adaptive Server applies the distinct requirement to the view's definition when it accesses the view for the first time, before it does any projecting or selecting. Views look and act like any database table. If you select a projection of the distinct view (that is, you select only some of the view's columns, but all of its rows), you can get results that appear to be duplicates. However, each row in the view itself is still unique. For example, suppose that you create a distinct view, myview, with three columns, a, b, and c , that contains these values:

a	b	c
1	1	2
1	2	3
1	1	0

When you enter this query:

select a, b from myview

the results look like this:

a    b
---  ---
1    1
1    2
1    1
 
(3 rows affected)

The first and third rows appear to be duplicates. However, the underlying view's rows are still unique.

Views that include IDENTITY columns

You can define a view that includes an IDENTITY column by listing the column name, or the syb_identity keyword, in the view's select statement. For example:

create view sales_view
as select syb_identity, stor_id
from sales_daily

However, you cannot add a new IDENTITY column to a view by using the identity_column_name = identity(precision) syntax.

You can select the IDENTITY column from the view using the syb_identity keyword, unless the view:

Selects the IDENTITY column more than once
Computes a new column from the IDENTITY column
Includes an aggregate function
Joins columns from multiple tables
Includes the IDENTITY column as part of an expression

If any of these conditions is true, Adaptive Server does not recognize the column as an IDENTITY column with respect to the view. When you execute sp_help on the view, the column displays an "Identity" value of 0.

In the following example, the row_id column is not recognized as an IDENTITY column with respect to the store_discounts view because store_discounts joins columns from two tables:

create view store_discounts
as
select stor_name, discount
from stores, new_discounts
where stores.stor_id = new_discounts.stor_id

When you define the view, the underlying column retains the IDENTITY property. When you update a row through the view, you cannot specify a new value for the IDENTITY column. When you insert a row through the view, Adaptive Server generates a new, sequential value for the IDENTITY column. Only the table owner, Database Owner, or System Administrator can explicitly insert a value into the IDENTITY column after setting identity_insert on for the column's base table.

After creating a view

After you create a view, the source text describing the view is stored in the text column of the syscomments system table. Do not remove this information from syscomments. Instead, encrypt the text in syscomments by using sp_hidetext, which is described in the Reference Manual. For more information, see "Compiled objects".

Validating a view's selection criteria using with check option

Normally, Adaptive Server does not check insert and update statements on views to determine whether the affected rows are within the scope of the view. A statement can insert a row into the underlying base table, but not into the view, or change an existing row so that it no longer meets the view's selection criteria.

When you create a view using the with check option clause, each insert and update through the view, is validated against the view's selection criteria. All rows inserted or updated through the view must remain visible through the view, or the statement fails.

Here is an example of a view, stores_ca, created using with check option. This view includes information about stores located in California, but excludes information about stores located in any other state. The view is created by selecting all rows from the stores table for which state has a value of "CA":

create view stores_ca
as select * from stores
where state = "CA"
with check option

When you try to insert a row through stores_ca, Adaptive Server verifies that the new row falls within the scope of the view. The following insert statement fails because the new row would have a state value of "NY", rather than "CA":

insert stores_ca
values ("7100", "Castle Books", "351 West 24 St.", "New York", "NY", "USA", "10011", "Net 30")

When you try to update a row through stores_cal, Adaptive Server verifies that the update will not cause the row to disappear from the view. The following update statement fails because it would change the value of state from "CA" to "MA". After the update, the row would no longer be visible through the view.

update stores_ca
set state = "CA"
where stor_id = "7066"

Views derived from other views

When a view is created using with check option, all views derived from the "base" view must satisfy its check option. Each row inserted through the derived view must be visible through the base view. Each row updated through the derived view must remain visible through the base view.

Consider the view stores_cal30, which is derived from stores_cal. The new view includes information about stores in California with payment terms of "Net 30":

create view stores_cal30
as select * from stores_ca
where payterms = "Net 30"

Because stores_cal was created using with check option, all rows inserted or updated through stores_cal30 must be visible through stores_cal. Any row with a state value other than "CA" is rejected.

Notice that stores_cal30 does not have a with check option clause of its own. This means that it is possible to insert or update a row with a payterms value other than "Net 30" through stores_cal30. The following update statement would be successful, even though the row would no longer be visible through stores_cal30:

update stores_cal30
set payterms = "Net 60"
where stor_id = "7067"


	Expand Search