Home

	Expand Search

Transact-SQL User's Guide

Chapter 9 Views: Limiting Access to Data

Creating views

Using the select statement with create view

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.

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.

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.

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".

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

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.

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" 

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:

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.

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.