Overview of Views

This topic covers concepts for using views.

In this Topic:

What is a View?

A view allows the result of a query to be accessed as if it were a table. The query is specified in the CREATE VIEW statement.

For example:

CREATE VIEW doctor_view AS
 SELECT patient_name, diagnosis, treatment FROM hospital_table;

CREATE VIEW accountant_view AS
 SELECT patient_name, billing_address, cost FROM hospital_table;

A view can be used almost anywhere that a table can be used (e.g. in a join or a subquery):

  • Show all of the types of medical problems patients have had:

    SELECT DISTINCT diagnosis FROM doctor_view;
    
  • Show the cost of each treatment (without showing personally identifying information about specific patients).

    SELECT treatment, cost FROM doctor_view AS dv, accountant_view AS av
      WHERE av.patient_ID = dv.patient_ID;
    

Advantages of Views

Views Enable Writing More Modular Code

Views help you to write clearer, more modular SQL code. For example, suppose that your hospital database has a table listing information about all employees. You can create views to make it convenient to extract information about only the medical staff or only the maintenance staff. You can even create hierarchies of views.

For example, you can create one view for the doctors, and one for the nurses, and then the medical_staff view can be created by referring to the doctors view and nurses view:

CREATE VIEW doctors as SELECT * FROM employees WHERE title = 'doctor';
CREATE VIEW nurses as SELECT * FROM employees WHERE title = 'nurse';
CREATE VIEW medical_staff AS
    SELECT * FROM doctors
    UNION
    SELECT * FROM nurses
    ;

In many cases, rather than writing one large and difficult-to-understand query, you can decompose the query into smaller pieces, and create a view for each of those pieces. This not only makes the code easier to understand, but in many cases it also makes the code easier to debug because you can debug one view at a time, rather than the entire query.

One view may be referenced by many different queries, so views help increase code re-use.

Views Allow Granting Access to a Subset of a Table

Views allow you to grant access to just a portion of the data in a table(s). For example, suppose that we have a table of medical patient records. You would like the medical staff to have access to all of the medical information (for example, diagnosis) but not the financial information (for example, not the patient’s credit card number). You’d like the accounting staff to have access to the billing-related information, such as the costs of each of the prescriptions given to the patient, but not to the private medical data, such as diagnosis of a mental health condition. You can create two separate views, one for the medical staff, and one for the billing staff, so that each of those roles sees only the information needed to perform their jobs. Views allow this because you can grant privileges on a particular view to a particular role, without the grantee role having privileges on the table(s) underlying the view.

So, in our medical example, the role named medical_staff would not have privileges on the data table(s), but would have privileges on the view showing diagnosis and treatment, while the accounting staff would not have privileges on the data table(s), but would have privileges on the view showing billing addresses and treatment costs.

Views Can Improve Performance

In some situations, views can have query performance benefits. When a view is referenced in a query, the Snowflake query optimizer uses internal optimizations to process the view and the query together, enabling the view to be evaluated more efficiently than if it were evaluated separately.

Types of Views

There are two types of views:

  • Standard/regular views (usually simply referred to as “views”).
  • Materialized views.

Standard/Regular Views

The term “view” generically refers to all types of views; however, the term is used here to refer specifically to non-materialized views.

A view is basically a named definition of a query. A non-materialized view’s results are created by executing the query at the time that the view is referenced in a query. The results are not stored for future use. Performance is slower than with materialized views. Non-materialized views are the most common type of view.

Any query expression that returns a valid result can be used to create a non-materialized view, such as:

  • Selecting some (or all) columns in a table.
  • Selecting a specific range of data in table columns.
  • Joining data from two or more tables.

In addition, Snowflake supports defining a non-materialized view as secure. For more details, see Working with Secure Views.

Materialized Views

Although a materialized view is named as though it were a type of view, in many ways it behaves more like a table. A materialized view’s results are stored, almost as though the results were a table. This allows faster access, but requires storage space.

In addition, materialized views have restrictions that non-materialized views do not have.

For more details, see Working with Materialized Views.

Limitations on Views

Updates to Views Are Not Supported

The definition for a view cannot be updated (i.e. you cannot use ALTER VIEW to change the definition of a view). Instead, you must recreate the view with the new definition.

Additional Limitations

Changes to a table are not automatically propagated to views created on that table. For example, if you drop a column in a table, the views on that table might become invalid.

Views are read-only (i.e. you cannot execute DML commands directly on a view). However, you can use a view in a subquery within a DML statement that updates the underlying base table. For example:

-- Basic example of supported use of a view in a DML statement:
DELETE FROM hospital_table WHERE cost > (SELECT AVG(cost) FROM accountant_view);