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 ]

By specifying IS NULL or IS '' you remove the comment for the column. If the column (or table or view) is dropped, the associated comment is also removed.

It is not allowed or possible to add comments for columns of temporary tables in schema "tmp".

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

You cannot drop a view if it is referenced (e.g. from another view, a function or procedure or another db object except comments). Use option CASCADE to specify to also drop those referencing objects. System views can also not be dropped as they are needed by the system.

Associated system table: sys.tables where type = 1;

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

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.

Examples

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;
`DROP VIEW IF EXISTS "my_project"."my_View" CASCADE;`