CREATE VIEW

---

Use the CREATE VIEW statement to define a stored view on the database.

Syntax

CREATE VIEW view-name [ ( column-name [ , column-name ]...) ] AS query-specification 
 
view-name ::= user-defined-name 

Remarks

A view is a database object that stores a query and behaves like a table. A view contains a set of columns and rows. Data accessed through a view is stored in one or more tables; the tables are referenced by SELECT statements. Data returned by a view is produced dynamically every time the view is referenced.

The maximum length of a view name is 20 characters. The maximum number of columns in a view is 256. There is a 64KB limit on view definitions.

A grouped view is one that contains the GROUP BY clause and/or an aggregate function in the SELECT list. Grouped views are not allowed in the FROM clause of a SELECT statement with a join (that is, with multiple tables). Grouped views are not allowed in the FROM clause of a SELECT statement with a GROUP BY.

Grouped views may not be used in a subquery.

The WHERE clause against a grouped view is a HAVING clause, and appended to the HAVING clauses of the grouped view.

View definitions cannot contain UNION operators. The operator UNION cannot be applied to any SQL statement that references one or more views.

View definitions cannot contain procedures, nor can they contain an ORDER BY.

Examples

The following statement creates a view named vw_Person, which creates a phone list of all the people enrolled in a university. This view lists the last names, first names and telephone numbers with a heading for each column. The Person table is part of the DEMODATA sample database.

CREATE VIEW vw_Person (lastn,firstn,phone) AS SELECT Last_Name, First_Name,Phone FROM  Person 

In a subsequent query on the view, you may use the column headings in your SELECT statement, as shown in the next example.

SELECT lastn, firstn FROM vw_Person 

See Also

DROP VIEW