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.
Zen supports grouped views, defined as views using any of the following in the SELECT statement:
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.
View definitions cannot contain procedures.
ORDER BY
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.
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:
SELECT lastn, firstn FROM vw_Person
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:
SELECT lastn, firstn FROM vw_trusted_Person
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.
SELECT lastn, firstn FROM vw_Person
============
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).
Last_Name First_Name Phone
========= ========== ==========
Vqyles Rex 2105551871
Qulizada Ahmad 2105552233
Ragadio Ernest 2105554654
Luckey Anthony 2105557628
============
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).
Last_Name First_Initial GPA
========================= =============== ======
Abuali I 4.000
Adachi K 4.000
Badia S 4.000
Rowan A 4.000
Ujazdowski T 4.000
Wotanowski H 4.000
Gnat M 3.998
Titus A 3.998
Mugaas M 3.995
============
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).
ID First_Name Last_Name
========= ========== ==========
100062607 Janis Nipart
100285859 Lisa Tumbleson
100371731 Robert Mazza
100592056 Andrew Sugar
100647633 Robert Reagen
100822381 Roosevelt Bora
101042707 Avram Japadjief
10 rows were affected.
============
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).