Creates a view with the specified name on existing tables or views.

Syntax

CREATE VIEW [owner_name.]view_name
 [ ( column_name, column_name,... ) ]  
 AS [ ( ]query_expression[ ) ]
 [ WITH CHECK OPTION ] ;

Parameters

owner_name

Owner of the created view.

( column_name, column_name,... )

Specifies column names for the view. These names provide an alias for the columns selected by the query specification. If the column names are not specified, then the view is created with the same column names as the tables or views on which it is based.

WITH CHECK OPTION

Checks that the updated or inserted row satisfies the view definition. The row must be selectable using the view. The WITH CHECK OPTION clause is only allowed on an updatable view.

To determine the authorization (privileges) of a view and to enable effective employment of the view, you must execute two GRANT statements along with the CREATE VIEW statement. The GRANT statements give access to the view and enables the view to access the underlying database tables.

The syntax for these statements is given below:

CREATE VIEW ... 
GRANT {privilege[, privilege] , ... | ALL [ PRIVILEGES]} 
ON schema_name view_name TO
  { username [, username ] , ... | PUBLIC }
GRANT {privilege[, privilege] , ... | ALL [ PRIVILEGES]} 
ON table_name TO schema_name;
privilege

Uses the following syntax:

{ SELECT | INSERT | DELETE | INDEX
  | UPDATE [( column , column , ... )
  [| REFERENCES [( column , column , ... )]}

Notes

  • A view is deletable if deleting rows from that view is allowed. For a view to be deletable, the view definition must satisfy the following conditions:
    • The first FROM clause contains only one table reference or one view reference.
    • There are no aggregate functions, DISTINCT clause, GROUP BY clause, or HAVING clause in the view definition.
    • If the first FROM clause contains a view reference, then the view referred to is deletable.
  • A view is updatable if updating rows from that view is allowed. For a view to be updatable, the view has to satisfy the following conditions:
    • The view is deletable (it satisfies all the previously specified conditions for deletability).
    • All the select expressions in the first SELECT clause of the view definition are simple column references.
    • If the first FROM clause contains a view reference, then the view referred to is updatable.
  • A view is insertable if inserting rows into that view is allowed. For a view to be insertable, the view has to satisfy the following conditions:
    • The view is updatable (it satisfies all the previously specified conditions for update ability).
    • If the first FROM clause contains a table reference, then all NOT NULL columns of the table are selected in the first SELECT clause of the view definition.
    • If the first FROM clause contains a view reference, then the view referred to is insertable.
  • CREATE VIEW does not support a scalar subquery in the SELECT list, which is an inner SELECT expression that returns a single value. If the view definition includes this construct, CREATE VIEW fails with an error. For example, the following example code shows the unsupported pattern that results in an error:
    CREATE VIEW OrderTotals_Bad
    (OrderNum, OrderDate, TotalPrice) AS
SELECT
    o."OrderNum",
    o."OrderDate",
    (
        SELECT
            SUM(ol."Price")
        FROM
            pub."OrderLine" AS ol
        WHERE
            ol."OrderNum" = o."OrderNum"
    ) AS TotalPrice
FROM
    pub."Order" AS o;
    To achieve equivalent behavior, as a workaround, rewrite the statement by moving the subquery into a derived table and joining it. The following examples shows the supported rewrite:
    CREATE VIEW OrderTotals_Good
    (OrderNum, OrderDate, TotalPrice) AS
SELECT
    o."OrderNum",
    o."OrderDate",
    d.TotalPrice
FROM
    pub."Order" AS o
LEFT OUTER JOIN
    (
        SELECT
            ol."OrderNum" AS OrderNum,
            SUM(ol."Price") AS TotalPrice
        FROM
            pub."OrderLine" AS ol
        GROUP BY
            ol."OrderNum"
    ) AS d
    ON d.OrderNum = o."OrderNum";

    The unsupported scalar subquery enforces that exactly one value is returned; otherwise, it errors. When rewriting with a derived table, ensure the derived table logic, such as aggregation with GROUP BY, or TOP 1 with appropriate filters, returns the intended single-row result for each joined row.

Examples

CREATE VIEW statement

The following examples illustrate CREATE VIEW statements defined by query expressions:

CREATE VIEW ne_customers AS
          SELECT name, address, city, state
          FROM customer
          WHERE state IN ( 'NH', 'MA', 'ME', 'RI', 'CT', 'VT' )
          WITH CHECK OPTION ; 
CREATE VIEW OrderCount (custnum, numorders) AS
          SELECT CustNum, COUNT(*)
          FROM Order
          GROUP BY CustNum;

Granting privileges on a view

The below statement creates a view mtInvoiceView1 for the table pub.mtinvoice:

CREATE VIEW mtInvoiceView1 AS
SELECT CustNum,InvoiceDate,Amount,TotalPaid,Adjustment,OrderNum,ShipCharge from
pub.mtinvoice;

The below statement grants only SELECT privileges on the view:

GRANT SELECT PRIVILEGES ON Schema1.mtInvoiceView1 TO PUBLIC;

The below statement grants only UPDATE privileges on the view:

GRANT UPDATE PRIVILEGES ON Schema1.mtInvoiceView1 TO PUBLIC;

The below statement grants the schema that owns the view the authorization to select data from the table that the view accesses. Other privileges can also be granted. All table accessed by the view must be granted to the schema that owns the view.

GRANT ALL PRIVILEGES ON pub.mtInvoice TO Schema1;