Chapter 1: Commands

create view

Description

Creates a view, which is an alternative way of looking at the data in one or more tables.

Syntax

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

Parameters

view_name: is the name of the view. The name cannot include the database name. If you have set quoted_identifier on, you can use a delimited identifier. Otherwise, the view name cannot be a variable and must conform to the rules for identifiers. For more information about valid view names, see “Identifiers” in Chapter 4, “Expressions, Identifiers, and Wildcard Characters,” of Reference Manual: Building Blocks. Specify the owner’s name to create another view of the same name owned by a different user in the current database. The default value for owner is the current user.

column_name

specifies names to be used as headings for the columns in the view. If you have set quoted_identifier on, you can use a delimited identifier. Otherwise, the column name must conform to the rules for identifiers. For more information about valid column names, see “Identifiers” in Chapter 4, “Expressions, Identifiers, and Wildcard Characters,” of Reference Manual: Building Blocks.

You can always supply column names, but they are required only:

A column is derived from an arithmetic expression, function, string concatenation, or constant
Two or more columns have the same name (usually because of a join)
You want to give a column in a view a different name than the column from which it is derived (see Example 3)

Column names can also be assigned in the select statement (see Example 4). If no column names are specified, the view columns acquire the same names as the columns in the select statement.

select: begins the select statement that defines the view.

distinct: specifies that the view cannot contain duplicate rows.

select_statement: completes the select statement that defines the view. The select statement can use more than one table and other views.

with check option: indicates that all data modification statements are validated against the view selection criteria. All rows inserted or updated through the view must remain visible through the view.

Examples

Example 1

Creates a view derived from the title, type, price, and pubdate columns of the base table titles:

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

Example 2

Creates “new view” from “old view.” Both columns are renamed in the new view. All view and column names that include embedded blanks are enclosed in double quotation marks. Before creating the view, you must use set quoted_identifier on:

create view "new view" ("column 1", "column 2")
as select col1, col2 from "old view"

Example 3

Creates a view that contains the titles, advances, and amounts due for books with a price less than $5.00:

create view accounts (title, advance, amt_due) 
as select title, advance, price * total_sales 
from titles 
where price > $5

Example 4

Creates a view derived from two base tables, authors and publishers. The view contains the names and cities of authors who live in a city in which there is a publisher:

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

Example 5

Creates a view with the same definition as in the previous example, but with column headings included in the select statement:

create view cities2 
as select authorname = au_lname,
acity = authors.city, publishername = pub_name, pcity = publishers.city 
from authors, publishers 
where authors.city = publishers.city

Example 6

Creates a view, author_codes, derived from titleauthor that lists the unique author identification codes:

create view author_codes
as select distinct au_id
from titleauthor

Example 7

Creates a view, price_list, derived from title that lists the unique book prices:

create view price_list (price)
as select distinct price
from titles

Example 8

Creates a view of the stores table that excludes information about stores outside of California. The with check option clause validates each inserted or updated row against the view’s selection criteria. Rows for which state has a value other than “CA” are rejected:

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

Example 9

Creates a view, stores_cal30, which is derived from stores_cal. The new view inherits the check option from stores_cal. All rows inserted or updated through stores_cal30 must have a state value of “CA”. Because stores_cal30 has no with check option clause, you can insert or update rows through stores_cal30 for which payterms has a value other than “Net 30”:

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

Example 10

Creates a view, stores_cal30_check, derived from stores_cal. The new view inherits the check option from stores_cal. It also has a with check option clause of its own. Each row that is inserted or updated through stores_cal30_check is validated against the selection criteria of both stores_cal and stores_cal30_check. Rows with a state value other than “CA” or a payterms value other than “Net 30” are rejected:

create view stores_cal30_check
as select * from stores_cal
where payterms = "Net 30"
with check option

Example 11

Uses a SQL-derived table in creating a view:

create view psych_titles as
   select *
      from (select * from titles
               where type = "psychology") dt_psych

Usage

You can use views as security mechanisms by granting permission on a view, but not on its underlying tables.
You can rename a view using sp_rename.
When you query through a view, Adaptive Server checks to make sure that all the database objects referenced anywhere in the statement exist, that they are valid in the context of the statement, and that data update commands do not violate data integrity rules. If any of these checks fail, you see an error message. If the checks are successful, create view “translates” the view into an action on the underlying tables.
For more information about views, see the Transact-SQL User’s Guide.

Restrictions on views

You can create a view only in the current database.
The number of columns referenced by a view cannot exceed 1024.
You cannot create a view on a temporary table.
You cannot create a trigger or build an index on a view.
You cannot use readtext or writetext on text or unitext, image columns in views.
You cannot include order by, compute clauses or the keyword into in the select statements that define views.
You cannot update, insert, or delete from a view with select statements that include the union operator.
If you create a view using a local or a global variable, Adaptive Server issues error message 7351: “Local or global variables not allowed in view definition.”
You can combine create view statements with other SQL statements in a single batch.
WARNING! When a create view command occurs within an if...else block or a while loop, Adaptive Server creates the schema for the view before determining whether the condition is true. This may lead to errors if the view already exists. To avoid this, verify that a view with the same name does not already exist in the database or use an execute statement, as follows:
```
if not exists
   (select * from sysobjects where name="mytable")
begin
execute "create table mytable(x int)"
end
```

View resolution

If you alter the structure of a view’s underlying tables by adding or deleting columns, the new columns do not appear in a view defined with a select * clause unless the view is dropped and redefined. The asterisk shorthand is interpreted and expanded when the view is first created.
If a view depends on a table or view that has been dropped, Adaptive Server produces an error message when anyone tries to use the view. If a new table or view with the same name and schema is created to replace the one that has been dropped, the view again becomes usable.
You can redefine a view without redefining other views that depend on it, unless the redefinition makes it impossible for Adaptive Server to translate any dependent views.

Modifying data through views

delete statements are not allowed on multitable views.
insert statements are not allowed unless all not null columns in the underlying table or view are included in the view through which you are inserting new rows. Adaptive Server cannot supply values for not null columns in the underlying table or view.
You cannot insert directly to a computed column through a view. The value of computed columns can only be generated internally by Adaptive Server.
insert statements are not allowed on join views created with distinct or with check option.
update statements are allowed on join views with check option. The update fails if any of the affected columns appear 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 cannot update or insert into a view defined with the distinct clause.
Data update statements cannot change any column in a view that is a computation, and cannot change a view that includes aggregates.

IDENTITY columns and views

You cannot add a new IDENTITY column to a view with the column_name = identity(precision) syntax.
To insert an explicit value into an IDENTITY column, the table owner, Database Owner, or System Administrator must set identity_insert table_name on for the column’s base table, not through the view through which it is being inserted.

group by clauses and views

When creating a view for security reasons, be careful when using aggregate functions and the group by clause. A Transact-SQL extension allows you to name columns that do not appear in the group by clause. If you name a column that is not in the group by clause, Adaptive Server returns detailed data rows for the column. For example, this Transact- SQL extended column query returns a row for every 18 rows—more data than you might intend:
```
select title_id, type, sum(total_sales)
from titles
group by type
```
While this ANSI-compliant query returns one row for each type (6 rows):
```
select type, sum(total_sales)
from titles
group by type
```
For more information about group by, see “group by and having clauses.”

distinct clauses and views

The distinct clause defines a view as a database object that contains no duplicate rows. A row is defined to be a duplicate of another row if all of its column values match the same column values in another row. Null values are considered to be duplicates of other null values.

Querying a subset of a view’s columns can result in what appear to be duplicate rows. If you select a subset of columns, some of which contain the same values, the results appear to contain duplicate rows. However, the underlying rows in the view are still unique. Adaptive Server applies the distinct requirement to the view’s definition when it accesses the view for the first time (before it does any projection and selection) so that all the view’s rows are distinct from each other.

You can specify distinct more than once in the view definition’s select statement to eliminate duplicate rows, as part of an aggregate function or a group by clause. For example:
```
select distinct count(distinct title_id), price
from titles
```
The scope of the distinct applies only for that view; it does not cover any new views derived from the distinct view.

with check option clauses and views

If a view is created with check option, each row that is inserted or updated through the view must meet the selection criteria of the view.
If a view is created with check option, all views derived from the “base” view must satisfy its check option. Each row inserted or updated through the derived view must remain visible through the base view.

Getting information about views

To create a report of the tables or views on which a view depends, and of objects that depend on a view, execute sp_depends.
To display the text of a view, which is stored in syscomments, execute sp_helptext with the view name as the parameter.

Creating views from SQL derived tables

To create a view using a SQL-derived table, add the derived table expression in the from clause of the select part of the create view statement (see Example 11).
A view created using a SQL-derived table can be updated if the derived table expression can be updated. The update rules for the derived table expression follow the update rules for the select part of the create view statement.
Data can be inserted through a view that contains a SQL-derived table if the insert rules and permission settings for the derived table expression follow the insert rules and permission settings for the select part of the create view statement.
Temporary tables and local variables are not permitted in a derived table expression that is part of a create view statement.
SQL-derived tables cannot have unnamed columns.
For more information about derived table expressions, see the Transact-SQL Guide.

Standards

ANSI SQL – Compliance level: Entry-level compliant.

The use of more than one distinct keyword and the use of “column_heading = column_name” in the select list are Transact-SQL extensions.

Permissions

create view permission defaults to the Database Owner, who can transfer it to other users.

Permissions on objects at view creation When you create a view, Adaptive Server makes no permission checks on objects, such as tables and views, that are referenced by the view. Therefore, you can create a view successfully even if you do not have access to its objects. All permission checks occur when a user invokes the view.

Permissions on objects at view execution When a view is invoked, permission checks on its objects depend on whether the view and all referenced objects are owned by the same user.

If the view and its objects are not owned by the same user, the invoker must have been granted direct access to the objects. For example, if the view performs a select from a table the invoker cannot access, the select statement fails.
If the view and its objects are owned by the same user, special rules apply. The invoker automatically has implicit permission to access the view’s objects even though the invoker could not access them directly. Without having to grant users direct access to your tables, you can give them restricted access with a view. In this way, a view can be a security mechanism. For example, invokers of the view might be able to access only certain rows and columns of your table. A detailed description of the rules for implicit permissions is discussed in the System Administration Guide.

Auditing

Values in event and extrainfo columns of sysaudits are:

Event	Audit option	Command or access audited	Information in extrainfo
16	create	create view	Roles – Current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – NULL Proxy information – Original login name, if set proxy in effect