CREATE VIEW

The CREATE VIEW statement defines a stored view or virtual table.

CREATE VIEW view-name [ ( column-name [ , column-name ]...) ]

[ ORDER BY order-by-expression [ , order-by-expression ]... ]

order-by-expression ::= expression [ CASE (string) | COLLATE collation-name ] [ ASC | DESC ] (see SELECT syntax)

A view is a database object that stores a query and behaves like a table. Data returned by a view is stored in one or more tables, referenced by SELECT statements. Rows and columns in the view are refreshed each time it is referenced.

See Identifier Restrictions in Advanced Operations Guide for the maximum length of a view name. The maximum number of columns in a view is 256. View definitions have a 64 KB limit.

PSQL supports grouped views, defined as views using any of the following in the SELECT statement:

• Scalar Functions

• Scalar Subqueries

Grouped views may be used in a subquery provided that the subquery is an expression. A subquery is not considered an expression if it is connected with the operators IN, EXISTS, ALL, or ANY.

ORDER BY in a view works the same way as in a SELECT statement. Note especially the following:

•You may use aliases in an ORDER BY clause.

•You may use scalar subqueries in an ORDER BY clause.

•The use of TOP or LIMIT is recommended in views that use ORDER BY.

•If the engine uses a temporary table to return the ordered result of ORDER BY and the query uses a dynamic cursor, then the cursor is converted to static. For example, temporary tables are always required when ORDER BY is used on an unindexed column. Forward-only and static cursors are not affected.

A trusted view includes WITH EXECUTE AS ‘MASTER’. See Trusted and Non-Trusted Objects.

The following statement creates a non-trusted 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:

The user executing the view must have SELECT permissions on the Person table.

The following example creates a similar view, but a trusted one.

CREATE VIEW vw_trusted_Person (lastn,firstn,phone) WITH EXECUTE AS 'MASTER' AS SELECT Last_Name, First_Name,Phone FROM Person

Now assume that to user1 you grant SELECT permissions on vw_Person. User1 can use the column headings in a SELECT statement:

User1 is not required to have SELECT permissions on the Person table because the permissions were granted to the trusted view.

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, telphone) 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.

The example above can be changed to include an ORDER BY clause.

CREATE VIEW vw_Person_ordby (lastn, firstn, telphone) AS SELECT Last_Name, First_Name, Phone FROM Person ORDER BY phone

The view returns the following (for brevity, not all records are shown).

The following example creates a view that returns the grade point average (GPA) of students in descending order, and, for each GPA ordering, lists the students by last name descending.

CREATE VIEW vw_gpa AS SELECT Last_Name,Left(First_Name,1) AS First_Initial,Cumulative_GPA AS GPA FROM Person LEFT OUTER JOIN Student ON Person.ID=Student.ID ORDER BY Cumulative_GPA DESC, Last_Name

The view returns the following (for brevity, not all records are shown).

This example creates a view that returns the top 10 records from the Person table, ordered by ID.

CREATE VIEW vw_top10 AS SELECT TOP 10 * FROM person ORDER BY id;

The view returns the following (for brevity, not all columns are shown).

The following example creates a view to demonstrate that ORDER BY can be used with UNION.

CREATE VIEW vw_union_ordby_desc AS SELECT first_name FROM person UNION SELECT last_name FROM PERSON ORDER BY first_name DESC

The view returns the following (for brevity, not all records are shown).