Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 12.0.0 » SQL Anywhere Server - SQL Usage » Working with database objects » Working with materialized views


Restrictions on materialized views

The following restrictions apply when creating, initializing, refreshing, and view matching materialized views:

  • When creating a materialized view, the definition for the materialized view must define column names explicitly; you cannot include a SELECT * construct as part of the column definition.

  • Do not include columns defined as TIMESTAMP WITH TIME ZONE in the materialized view. The value of the time_zone_adjustment option varies between connections based on their location and the time of year, resulting in incorrect results and unexpected behavior.

  • When creating a materialized view, the definition for the materialized view cannot contain:

    • references to other views, materialized or not

    • references to remote or temporary tables

    • variables such as CURRENT USER; all expressions must be deterministic

    • calls to stored procedures, user-defined functions, or external functions

    • Transact-SQL outer joins

    • FOR XML clauses

  • The following database options must have the specified settings when a materialized view is created; otherwise, an error is returned. These database option values are also required for the view to be used by the optimizer:

    • ansinull=On
    • conversion_error=On
    • divide_by_zero_error=On
    • sort_collation=Internal
    • string_rtruncation=On
  • The following database option settings are stored for each materialized view when it is created. The current option values for the connection must match the values stored for a materialized view in order for the view to be used in optimization:

    • date_format
    • date_order
    • default_timestamp_increment
    • first_day_of_week
    • nearest_century
    • precision
    • scale
    • time_format
    • timestamp_format
    • timestamp_with_time_zone_format
    • default_timestamp_increment
    • uuid_has_hyphens
  • When a view is refreshed, the connection settings for all the options listed in the bullets above are ignored. Instead, the database option settings (which must match the stored settings for the view) are used.

 Specifying an ORDER BY clause in a materialized view definition
 Additional restrictions for immediate views
 See also