Creates a new view.

Syntax

---

CREATE VIEW <Name> AS
<View Definition>
[WITH CHECK OPTION|WITHOUT CHECK OPTION]
[DESCRIPTION <Description>]
[VERSION <VersionNumber>]
[ATTRIBUTES <CustomAttributes>]

Usage

---

Use this statement to create a new view. A view is a query expression that can act like a virtual table, and is useful when you want restrict which columns in a table (or tables) are visible to users. This can be accomplished by giving users (or roles) SELECT privileges on a view that only references a few select columns while not giving users (or roles) any SELECT privileges on the base table(s) that are referenced by the view.

The WITH CHECK OPTION clause is used with updateable views to specify whether INSERTS or UPDATES that would violate the WHERE clause will be permitted or not. If WITH CHECK OPTION is specified, then INSERTS or UPDATES that would violate the WHERE clause are not permitted.


By default, ElevateDB always tries to make a view updateable if possible. The rules for updateability are the same as those for sensitive query result sets, and are discussed in detail in the Result Set Cursor Sensitivity topic.


Any time the columns in any referenced base table or view change, ElevateDB will automatically reflect these changes in the view columns. You can always query this information via the ViewColumns Information schema table.

Examples

---

-- The following view selects the employee Name and
-- HireDate column from the Employees table.

CREATE VIEW "EmployeesList" AS
SELECT Name, HireDate
FROM Employees

-- The following view uses a derived table to retrieve
-- data.  It will be considered updateable, but will not
-- be updateable at runtime.

CREATE VIEW "DerivedSum" AS
SELECT *
FROM (SELECT CustNo, SUM(Orders.ItemsTotal) AS Total
      FROM Customer INNER JOIN Orders ON Orders.CustNo=Customer.CustNo
      GROUP BY CustNo) AS CustomerTotals
WHERE Total > 80000

Required Privileges

---

The current user must be granted the CREATE privilege on the current database in order to execute this statement. Please see the User Security topic for more information.

SQL 2003 Standard Deviations

---

This statement deviates from the SQL 2003 standard in the following ways: