15.00 - Views - Teradata Database

Teradata Database SQL Fundamentals

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1141-015K

Views

Views and Tables

A view can be compared to a window through which you can see selected portions of a database. Views retrieve portions of one or more tables or other views.

Views look like tables to a user, but they are virtual, not physical, tables. They display data in columns and rows and, in general, can be used as if they were physical tables. However, only the column definitions for a view are stored: views are not physical tables.

A view does not contain data: it is a virtual table whose definition is stored in the Data Dictionary. The view is not materialized until it is referenced by a statement. Some operations that are permitted for the manipulation of tables are not valid for views, and other operations are restricted, depending on the view definition.

Defining a View

The CREATE VIEW statement defines a view. The statement names the view and its columns, defines a SELECT on one or more columns from one or more underlying tables and/or views, and can include conditional expressions and aggregate operators to limit the row retrieval.

Using Views

The primary reason to use views is to simplify end user access to Teradata Database. Views provide a constant vantage point from which to examine and manipulate the database. Their perspective is altered neither by adding or nor by dropping columns from its component base tables unless those columns are part of the view definition.

From an administrative perspective, views are useful for providing an easily maintained level of security and authorization. For example, users in a Human Resources department can access tables containing sensitive payroll information without being able to see salary and bonus columns. Views also provide administrators with an ability to control read and update privileges on the database with little effort.

Restrictions

Some operations that are permitted on base tables are not permitted on views—sometimes for obvious reasons and sometimes not.

The following set of rules outlines the restrictions on how views can be created and used.

  • You cannot create an index on a view.
  • A view definition cannot contain an ORDER BY clause.
  • Any derived columns in a view must explicitly specify view column names, for example by using an AS clause or by providing a column list immediately after the view name.
  • You cannot update tables from a view under the following circumstances:
  • The view is defined as a join view (defined on more than one table)
  • The view contains derived columns.
  • The view definition contains a DISTINCT clause.
  • The view definition contains a GROUP BY clause.
  • The view defines the same column more than once.
  • Archiving Views

    Views are archived and restored as part of a database archive and restoration. Individual views can be archived or restored using the ARCHIVE or RESTORE statements of the ARC utility.