When you create a materialized view, its definition is stored in the database. The database server validates the definition to make sure it compiles properly. All column and table references are fully qualified by the database server to ensure that all users with access to the view see an identical definition. After successfully creating a materialized view, you populate it with data, also known as initializing the view, using a REFRESH MATERIALIZED VIEW statement. See REFRESH MATERIALIZED VIEW statement.

Before creating, initializing, or refreshing materialized views, ensure that all materialized view restrictions have been met. See Restrictions on materialized views.

To obtain a list of all materialized views in the database, including their status, use the sa_materialized_view_info system procedure. See sa_materialized_view_info system procedure.

After you finish creating the definition for the materialized view, it appears in the Views folder in Sybase Central.

See also

• CREATE MATERIALIZED VIEW statement
• REFRESH MATERIALIZED VIEW statement
• SQL Anywhere sample database

To create a materialized view (Sybase Central)

1. Connect to the database as a user with DBA or RESOURCE authority.

2. In the left pane, right-click Views and choose New » Materialized View.

3. Follow the instructions in the Create Materialized View Wizard.

4. Initialize the materialized view so that it contains data. See Initialize materialized views.

To create a materialized view (SQL)

1. Connect to the database as a user with DBA or RESOURCE authority.

2. Execute a CREATE MATERIALIZED VIEW statement. The database server creates and stores the view definition in the database, and sets the view's status to ENABLED. See CREATE MATERIALIZED VIEW statement.

3. You must initialize the materialized view so that it contains data. See Initialize materialized views.

Example

The following statement creates a materialized view, EmployeeConfid16, containing information about employees, and then initializes it to populate it with data.

CREATE MATERIALIZED VIEW EmployeeConfid16 AS
   SELECT EmployeeID, Employees.DepartmentID, SocialSecurityNumber, Salary, ManagerID,
      Departments.DepartmentName, Departments.DepartmentHeadID
   FROM Employees, Departments
   WHERE Employees.DepartmentID=Departments.DepartmentID;
REFRESH MATERIALIZED VIEW EmployeeConfid16;