When you create an SQL view of SPD Server tables, a view file is created in the specified domain with the name view-name.view.0.0.0.spds9. The view creator is the only one who has access to the view, until an ACL is created that grants other users access. Then, users who are using explicit SQL pass-through to access the view can use the view as they would use a table in SQL queries. Users who access the view through implicit SQL pass-through or by using a SAS DATA step must have direct access to the component tables that are referenced in the view in addition to having access to the view in order to use it.

Including the keyword MATERIALIZED in the CREATE VIEW statement specifies to create the view as a materialized view. When you create a materialized view, an additional SPD Server table is created in the same domain as the standard SQL view file. This table contains a copy of the data that was available from the view when the view was created. The materialization process can add substantial time to the execution of a CREATE VIEW statement. If one or more simple indexes are defined on any of the input tables that are used to create the results table, the indexes are also created on the materialized view table, as long as the column that was indexed in the input table also exists in the materialized view table.

As long as the data from the component tables does not change, the materialized view returns the results from the temporary table when the view is referenced in an SQL statement. When any of the component tables that make up the view are modified, the materialized view recomputes the results the next time the view is referenced and refreshes the temporary table with the new results. The temporary results table for a materialized view exists for as long as the view exists. When the owner deletes or drops a view, the temporary results table is also deleted. It is not necessary to specify the MATERIALIZED keyword in the DROP VIEW statement.

A materialized view table is accessed with SQL statements. A materialized view table cannot be accessed by using PROC DATASETS or other SAS procedures.

For a regular SPD Server SQL view, the results are computed each time the view is referenced in a subsequent SQL statement. For views that contain costly operations such as multiple table joins or operations on very large tables, the execution time for queries containing a materialized view can be orders of magnitude less than a regular view. If the results table produced by the view is relatively small in comparison with the input tables, the execution time for queries that use a materialized view might be a few seconds versus several minutes for a standard view.

If a view is being referenced at least twice before any updates occur, then the materialized view can provide superior performance. If the input tables are frequently updated in comparison to how often the view is referenced, a standard view is probably more efficient.

Examples

Example 1: Creating a Regular SPD Server SQL View

connect to sasspds(dbq='temp' user='Stan');
execute(create view WinterSpring as
select * from SpringSales, WinterSales
where SpringSales.id = WinterSales.id) by sasspds;

Example 2: Creating a Materialized SPD Server SQL View

connect to sasspds(dbq='temp' user='Stan');
execute(create materialized View WinterSpringTable as
select * from SpringSales, WinterSales
where SpringSales.id = WinterSales.id) by sasspds;

Last updated: February 8, 2017