Teradata Views

Views are database objects that are built by the query. Views can be created using a single table or multiple tables by way of joining.

Their definition is stored permanently in a data dictionary, but they don't save a copy. Data for the view is built dynamically.

A view may contain a subset of the table's rows or a subset of columns.

Create a View

Views are created using a special form of Data Definition Language (DDL). The CREATE requests a new VIEW, provides the name and the SELECT for the view. It is suggested the name of the view either start with "v_" or end with "_v" to identify it as a view name.

That way, it is visibly obvious to people that this is a view and not a table. The name of the view must be unique from the names of other objects in the database. The CREATE VIEW verifies that the name does not already exist and returns an error if it does.

Syntax

Following is the syntax for creating a view.

1. view_name: Name of the new view. If view_name is not fully qualified, the default database is used.
2. database_name: It divided into two sections
   • user_name: Name of the database or user to contain view_name if something other than the current database or user.
   • column_name: Name of a view column. If more than one column is specified, list their names in the order in which each column is to be displayed for the view.
3. AS: An introduction to the view definition.

NOTE

• Views that reference a row-level security table can include columns based on row-level security constraints, but it is not required. However, the view enforces any security constraints in the base table, whether or not they are included in the view definition.
• A view can reference both row-level security tables and non-row-level security tables, but all referenced row-level security tables must contain the same security constraint columns or subsequent requests to access the view fail with an error.

Example

Consider the following Employee table.

The following example creates a view on the Employee table.

Using Views

We can use a regular SELECT statement to retrieve data from Views.

Example

The following example retrieves the records from Employee_View;

Modifying Views

A current view can be modified using the REPLACE VIEW statement.

REPLACE VIEW redefines an existing view or, if the specified view does not exist, it creates a new view with the specified name.

Syntax

Following is the syntax to modify a view.

Example

The following example modifies the view Employee_View for adding additional columns.

Drop View

A current view can be dropped using the DROP VIEW statement.

Syntax

Following is the syntax of DROP VIEW.

Example

Following is an example to drop the view Employee_View.

Advantages of Views

Here are some advantages of using Views in Teradata, such as:

• Views provide an additional level of security by restricting the rows or columns of a table.
• Users can be given access only to views instead of base tables.
• Simplifies the use of multiple tables by pre-joining those using Views.
• Taking requires rows and columns from the tables.
• The view is used to reduce the net bond width.
• It provides better binding between the table and view data.