To avoid unexpected or incorrect results when querying a view, observe the following rules when you create the view definition.
- An operation on a view that references multiple tables/views causes those tables/views to be joined. Efficiency depends on how the operation uses the view (for example, whether the operation uses indexes defined for the tables).
- Any user with the privilege to use a view has the privilege to override a lock specified by a view definition.
- If tables referenced by a view are modified to add or remove columns, or change data types of existing columns, then attempts to access the view can cause error messages or unexpected results. This is true whether the view references table columns by name or by SELECT *.
- You can create views that reference global temporary, global temporary trace, and volatile tables.
- You cannot create a view on a queue table (see CREATE TABLE Usage Notes (Queue Table Form)).
- You cannot refer to a recursive view, a WITH clause, or WITH RECURSIVE clause in a view definition.
- You can use BLOB, CLOB, Period, and Geospatial columns in views if doing so does not violate the restrictions on large object use with SQL or the semantics of creating or replacing a view.The restrictions are the following:
- You cannot create a base table with more than 32 LOB columns.
- You cannot alter a base table to have more than 32 LOB columns.
However, a view definition can include as many LOB columns as its underlying base tables support.
- You cannot specify BLOB, CLOB, Period, and Geospatial columns in the GROUP BY or HAVING clauses of a view definition.
- BLOB, CLOB, Period, and Geospatial columns can be components of a view subject to the restrictions provided in this list and the semantics of view definitions.
For more information about Geospatial data types, see Geospatial Data Types, B035-1181.
- Changing the DateForm to change the default Date format does not change the format of date columns in a view, which derive from the underlying tables and views. However, expressions in a CREATE VIEW statement that require date validation against the DateForm or default Date format can generate an error message if the DateForm or default Date format changes. To avoid date string validation errors, specify date constants as ANSI date literals and specify formats in a FORMAT phrase.
- The database checks the CREATE VIEW or REPLACE VIEW text for syntax errors, but not for semantic errors. Therefore, you can create a view that is valid syntactically but not semantically. No message is returned when this occurs.
- If there are semantic errors in the view definition, then the request aborts and the database returns a message to the requestor when you perform a DML request that accesses tables through the view.
- You can only specify an ORDER BY clause in a view definition if you also specify either the TOP n or TOP m PERCENT option. For details, see General Usage Guidelines for CREATE VIEW and REPLACE VIEW.
You cannot specify an ORDER BY clause in a derived table subquery within a view definition.