View definitions

View definitions mk Fri, 04/02/2010 - 10:39

Regular SQL view definitions are supported.

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

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 standardise and simplify reporting.


Note: The "WITH CHECK OPTION" is accepted for SQL compliance but has no effect.

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

Limitations: Recursive views and reference-able views are not (yet) supported.
Updatable views are not supported, so it's not possible to insert, update, merge, delete or truncate data from a view.

Tip: to find out which user created views are defined in your database run query:
  SELECT schema_id, name, type, substring(query, 0, 80) as query FROM sys.tables
   WHERE type IN (SELECT table_type_id FROM sys.table_types WHERE table_type_name = 'VIEW')
   ORDER BY schema_id, name;