create view

Syntax

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

Parameters

• create – creates a view if one does not already exist.
• or replace – replaces an existing view definition without changing any of a view's security attributes.
• 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.

  Only an existing view can be replaced. The object name and ID remain the same.

• 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:

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

  With the or replace clause, you can change column names for the view as follows:

  • If the previous definition of the view contained headings for columns names, then the new definition of the view can omit the headings, or have different headings for column names.
  • If the previous definition of the view did not contain headings for column names, the new definition can contain headings for the view column names.
  • You can change the number of column headings according to the column names in the select_statement.
• select – begins the select statement that defines the view.
• distinct – specifies that the view cannot contain duplicate rows.

  If the original definition of the view did not specify distinct clause, you can change this parameter so the new 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.

  The columns specified in the target list of the select_statement of the replaced view can be changed to drop or add columns.

• 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. If you create a view using with check option:

  • Each row that is inserted or updated through the view must meet the selection criteria of the view.

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

  A view created with the with check option clause can be replaced without this clause, and vice versa.

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

• Example 12 – Based on the view Current_Product_List, which lists all active products from the table Products. The view is defined as:

  create view Current_Product_List as
  select ProductID, ProductName   
  from Products  
  where Discontinued = “No” 
  
  select object_id("Current_Product_List")
  -----------       
  889051172   

  This next command adds the Category column to Current_Product_list using the or replace clause. The object ID of the view remains the same:

  create or replace view Current_Product_List as
  select ProductID, ProductName, Category   
  from Products  
  where Discontinued = “No” 
  
  select object_id("Current_Product_List")
  -----------       
  889051172  

• Example 13 – Replaces V1—a view that has dependent objects:

  create table T1(C1 int, C2 int)
  create table T2(C1 int, C2 int) 
        
  create view V1 as select * from T1   
  create view V2 as select * from V1
         
  create function foo1   
  returns int   
  as   
  begin     
      declare @number int     
      select @number = C1 from V2   
  end   
  return @number       
  select object_id("V1")       
  -----------       
  985051514 

  create or replace V1 as select * from T2
  
  select * from V2       
  
  select dbo.foo1()       
  
  select object_id("V1")       
  -----------       
  985051514 

  The replaced version of V1 references T2 instead of T1. Both V2 and foo1 will be recompiled. select * from v2 recompiles V2, but not foo1, which is recompiled when the UDF is invoked.

Usage

• You can use views as security mechanisms by granting permission on a view, but not on its underlying tables.

• You can use sp_rename to rename a view.

• When you query through a view, the SAP ASE 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 Users 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

When you create a view, the SAP ASE 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.

Any user who impersonates the view owner through an alias or setuser cannot replace the view.

Auditing

Values in event and extrainfo columns of sysaudits are: