Regular views

When you browse data, a query operates on one or more database objects and produces a result set. Just like a base table, a result set from a query has columns and rows. A view gives a name to a particular query, and holds the definition in the database system tables.

When you create a regular view, the database server stores the view definition in the database; no data is stored for the view. Instead, the view definition is executed only when it is referenced, and only for the duration of time that the view is in use. This means that creating a view does not require storing duplicate data in the database.

Suppose you need to list the number of employees in each department frequently. You can get this list with the following statement:

SELECT DepartmentID, COUNT(*)
FROM Employees
GROUP BY DepartmentID;

Restrictions on SELECT statements for regular views

There are some restrictions on the SELECT statements you can use as regular views. In particular, you cannot use an ORDER BY clause in the SELECT query. A characteristic of relational tables is that there is no significance to the ordering of the rows or columns, and using an ORDER BY clause would impose an order on the rows of the view. You can use the GROUP BY clause, subqueries, and joins in view definitions.

To develop a view, tune the SELECT query by itself until it provides exactly the results you need in the format you want. Once you have the SELECT statement just right, you can add a phrase in front of the query to create the view:

CREATE VIEW view-name AS query;

Statements that update regular views

Updates can be performed on a view using the UPDATE, INSERT, or DELETE statements if the query specification defining the view is updatable. Views are considered inherently non-updatable if their definition includes any one of the following in their query specification:

UNION, EXCEPT, or INTERSECT.
DISTINCT clause.
GROUP BY clause.
WINDOW clause.
FIRST, TOP, or LIMIT clause.
aggregate functions.
more than one table in the FROM clause, when ansi_update_constraints option is set to 'Strict' or Cursor'.
ORDER BY clause, when ansi_update_constraints option is set to 'Strict' or Cursor'.
all SELECT list items are not base table columns.

The WITH CHECK OPTION clause

When creating a view, the WITH CHECK OPTION clause is useful for controlling what data is changed when inserting into, or updating, a base table through a view. The following example illustrates this.

Execute the following statement to create the SalesEmployees view with a WITH CHECK OPTION clause.

CREATE VIEW SalesEmployees AS
   SELECT EmployeeID, GivenName, Surname, DepartmentID
   FROM Employees
   WHERE DepartmentID = 200
   WITH CHECK OPTION;

Select to view the contents of this view, as follows:

SELECT * FROM SalesEmployees;

EmployeeID	GivenName	Surname	DepartmentID
129	Philip	Chin	200
195	Marc	Dill	200
299	Rollin	Overbey	200
467	James	Klobucher	200
...	...	...	...

Next, attempt to update DepartmentID to 400 for Philip Chin:

UPDATE SalesEmployees
SET DepartmentID = 400 
WHERE EmployeeID = 129;

Since the WITH CHECK OPTION was specified, the database server evaluates whether the update violates anything in the view definition (in this case, the expression in the WHERE clause). The statement fails (DepartmentID must be 200), and the database server returns the error, "WITH CHECK OPTION violated for insert/update on base table 'Employees'."

If you had not specified the WITH CHECK OPTION in the view definition, the update operation would proceed, causing the Employees table to be modified with the new value, and subsequently causing Philip Chin to disappear from the view.

If a view (for example, View2) is created that references the SalesEmployees view, any updates or inserts on View2 are rejected that would cause the WITH CHECK OPTION criteria on SalesEmployees to fail, even if View2 is defined without a WITH CHECK OPTION clause.

Regular views

Restrictions on SELECT statements for regular views

Statements that update regular views

The WITH CHECK OPTION clause

See also