|
Creates a view on the database.
CREATE [ OR REPLACE ] VIEW [ owner.]view-name [ ( column-name, ... ) ] AS query-expression [ WITH CHECK OPTION ]
Specifying OR REPLACE (CREATE OR REPLACE VIEW) creates a view or replaces an existing view with the same name. Existing privileges are preserved when you use the OR REPLACE clause, but INSTEAD OF triggers on the view are dropped.
If you execute a CREATE OR REPLACE VIEW statement on a view that has one or more INSTEAD OF triggers, an error is returned. Drop the trigger before altering or dropping the view.
The SELECT statement on which the view is based. The SELECT statement must not refer to local temporary tables. Also, query-expression can have a GROUP BY, HAVING, WINDOW, or ORDER BY clause, and can contain UNION, EXCEPT, INTERSECT, or a common table expression.
Query semantics dictate that the order of the rows returned is undefined unless the query combines an ORDER BY clause with a TOP or FIRST clause in the SELECT statement.
The WITH CHECK OPTION clause rejects any updates and inserts to the view that do not meet the criteria of the view as defined by its query-expression.
Views do not physically exist in the database as tables. They are derived each time they are used. A view is derived as the result of a SELECT statement specified in a CREATE VIEW statement. In a view, specifying the user ID of the table owner is recommended to distinguish tables with the same name.
A view name can be used in place of a table name in SELECT, DELETE, UPDATE, and INSERT statements.
SELECT * can be used in the main query, a subquery, a derived table, or a subselect of the CREATE VIEW statement.
A query can specify a TOP n, FIRST, or LIMIT clause even if there is no ORDER BY clause. At most the specified number of rows are returned, but the order of the rows returned is not defined, so an ORDER BY could be specified but it is not required. When a view is used in a query, the ORDER BY in the view does not determine the order of rows in the query, even if there are no other tables in the FROM clause. That means that an ORDER BY should only be included in a view if it is needed to select which rows are included by a TOP n, FIRST, or LIMIT clause. Otherwise, the ORDER BY clause has no effect and it is ignored by the database server.
Views can be updated unless the query-expression defining the view contains a GROUP BY clause, a WINDOW clause, an aggregate function, or involves a set operator (UNION, INTERSECT, EXCEPT). An update to the view updates the underlying table(s).
The view's columns are given the names specified in the column-name list. If the column name list is not specified, view columns are given names from the SELECT list items. All items in the SELECT list must have unique names. To use names from the SELECT list items, each item must be a simple column name or have a specified alias.
The database server does permit unnamed expressions in the SELECT list of the query-expression referenced in the CREATE VIEW statement. Unnamed expressions in the SELECT list of the query-expression are assigned the name expression, concatenated with an integer value if more than one such expression exists. For example, the following statement would define view V with three columns (expression, expression1, and expression2), and these names would appear in the SYSCOLUMN system view for the created view V.
CREATE VIEW V AS SELECT DATEADD( DAY, 1, NOW() ), DATEADD( DAY, 2, NOW() ), DATEADD( DAY, 2, NOW() ) FROM SYS.DUMMY;
Relying on these generated names is not recommended since other views with unnamed SELECT list expressions have the identical assigned names.
Typically, a view references tables and views (and their respective attributes) that are defined in the catalog. However, a view can also reference SQL variables (with the exception of TABLE REF variables). When variables are used in the definition, when a query that references the view is executed, the value of the SQL variable is substituted for the variable. Views that reference SQL variables are called parameterized views since the variables act as parameters to the execution of the view. Parameterized views offer an alternative to embedding the body of an equivalent SELECT block in a query as a derived table in the query's FROM clause. Parameterized views can also be useful for queries that are embedded in stored procedures where the SQL variables referenced in the view are input parameters for the procedure.
It is not necessary for the SQL variable to exist when the CREATE VIEW statement is executed. However, if the SQL variable is not defined when a query that refers to the view is executed, an error is returned indicating that the column (variable) could not be found.
Variables of type TABLE REF (table reference variables) are not permitted in the outermost SELECT list of a view definition.
You must have the CREATE VIEW system privilege to create views owned by you. You must have the CREATE ANY VIEW or CREATE ANY OBJECT system privilege to create views owned by others.
To replace an existing view, you must be the owner of the view, or have one of the following:
CREATE ANY VIEW and DROP ANY VIEW system privileges.
CREATE ANY OBJECT and DROP ANY OBJECT system privileges.
ALTER ANY OBJECT or ALTER ANY VIEW system privileges.
If the tables referenced by the view are owned by other users, you must have the SELECT object-level privileges on those tables.
Automatic commit.
Core Feature, but some features of a view's embedded SELECT statement are optional Language Features. The ability to specify an ORDER BY clause with the top-level SELECT statement in the view definition is optional Language Feature F852. Restricting the result set of a view using SELECT TOP or LIMIT is optional Language Feature F859. Specifying WITH CHECK OPTION on a view that is not updatable, for example, the view's SELECT statement contains a derived table involving aggregation or DISTINCT, or a set operator (INTERSECT, EXCEPT, or UNION), is optional Language Feature T111.
The following extensions are also not in the ANSI/ISO SQL Standard: parameterized views, the optional OR REPLACE syntax, and the automatic generation of names for unnamed SELECT list expressions.
The following example creates a view showing information for male employees only. This view has the same column names as the base table:
CREATE VIEW MaleEmployees AS SELECT * FROM GROUPO.Employees WHERE Sex = 'M';
The following example creates a view showing employees and the departments they belong to:
CREATE VIEW EmployeesAndDepartments AS SELECT Surname, GivenName, DepartmentName FROM GROUPO.Employees JOIN GROUPO.Departments ON Employees.DepartmentID = Departments.DepartmentID;
The following example replaces the EmployeesAndDepartments view created in the previous example. After replacing the view, the view shows the city, state, and country location for each employee:
CREATE OR REPLACE VIEW EmployeesAndDepartments AS SELECT Surname, GivenName, City, State, Country FROM GROUPO.Employees JOIN GROUPO.Departments ON Employees.DepartmentID = Departments.DepartmentID;
The following example creates a parameterized view based on the variables var1 and var2, which are not attributes of the Employees or Departments tables:
CREATE VIEW EmployeesByState AS SELECT Surname, GivenName, DepartmentName FROM GROUPO.Employees JOIN GROUPO.Departments ON Employees.DepartmentID = Departments.DepartmentID WHERE Employees.State = var1 and Employees.Status = var2;
Variables can appear in the view's SELECT statement in any context where a variable is a permitted expression. For example, the following parameterized view utilizes the parameter var1 as the pattern for a LIKE predicate:
CREATE VIEW ProductsByDescription AS SELECT * FROM GROUPO.Products WHERE Products.Description LIKE var1;
To use this view, define the variable var1 before executing the query that references the view. For example, the following BEGIN statement could be placed in a procedure, function, or a batch statement:
BEGIN DECLARE var1 CHAR(20); SET var1 = '%cap%'; SELECT * FROM ProductsByDescription END