create view

Description

Creates a view, which is an alternative way to look 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. 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.

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

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 that have 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


Restrictions on views


View resolution


Modifying data through views


IDENTITY columns and views


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

See “group by and having clauses.”


distinct clauses and views


with check option clauses and views


Getting information about views


Creating views from SQL-derived tables

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 successfully a view 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.

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 is in effect

See also

Documentation “Identifiers” in Chapter 4, “Expressions, Identifiers, and Wildcard Characters,” of Reference Manual: Building Blocks

Commands create schema, drop view, select, update

System procedures sp_depends, sp_help, sp_helptext, sp_rename