Documentation > USER GUIDE > SQL Manual > Data Definition > View Definition

View Definition

Regular SQL view definitions are supported. A view can be seen as a stored SELECT query with a unique name. It can be used in queries at all the places where you can normally use a table name. Views are useful to reduce user query complexity as they can include joins, computations, derivations, aggregations, selections, conditions, ordering, and more in the view definition, so the user doesn't have to define them in the queries again and again. Simply select from the predefined view. They are also very useful to standardize and simplify reporting.

Views do not contain or store data, so do not require disk space.

view_def:
    CREATE [ OR REPLACE ] VIEW [ schema_name '.' ] view_name
           [ '(' column_name [ ',' column_name ] [ ',' ...] ')' ]
           AS select_query_without_limit
           [ WITH CHECK OPTION ]

Updatable views are not supported, so it's not possible to insert, update, merge, delete or truncate data from a view.
The clause WITH CHECK OPTION is accepted for SQL compliance but has no effect as views are not updatable.

Recursive views and reference-able views are not supported.

Examples

CREATE VIEW user_views AS
SELECT schema_id, id as view_id, name, type, substring(query, 0, 80) as view_def
  FROM sys.tables
 WHERE NOT system
   AND type IN (SELECT table_type_id FROM sys.table_types WHERE table_type_name = 'VIEW')
 ORDER BY schema_id, name;

SELECT * FROM user_views;

DROP VIEW user_views;

Drop view statement

drop_view:
     DROP VIEW [ IF EXISTS ] [ schema_name '.' ] view_name [ RESTRICT | CASCADE ]

If a view is referenced (e.g. from another view, a function or procedure or another db object except comments) the view can not be dropped. So default drop action is RESTRICT. To find out which dependencies exist for a view query the system view: sys.dependencies_vw. You will need to drop the referencing objects first or use drop action CASCADE to specify to also drop those referencing objects cascading.