SQL/MX 2.x Installation and Management Guide (G06.24+, H06.03+)

Understanding and Planning SQL/MX Tables
HP NonStop SQL/MX Installation and Management Guide—523723-004
4-11
Using Views
•
All other SQL/MX objects (views, indexes, constraints, and so on) are directly or
indirectly dependent on the tables.
•
Recovery and management methods are simplified.
Disadvantages include:
•
Although you can restrict access to specified table columns when you use GRANT
to provide a user with UPDATE or REFERENCES privileges to a table, the GRANT
SELECT, DELETE, INSERT, and ALL privileges provide the user with access to all
columns in the table. To protect your tables from access by unauthorized users,
provide GRANT privileges to table views.
•
Alterations to tables generally have a greater impact on application code that uses
the tables directly. For example, an application should not use SELECT * to
retrieve values from a table, because adding a column to the table would mean
that the application must change.
Using Views
A view is a logical table derived from one or more tables. A view can include any SQL
query from one or more tables or other views. You can create more than one view on a
table or combination of tables.
Use views to externalize some or all of the data to the user.
A view can have the same structure as an underlying table, or the view can be
different. Views do not store data physically on the disk.
In general, views have these advantages and uses:
•
To enable the apparent structure of retrieved data to be different from the actual
stored data
•
To enable logical renaming of columns
•
To redefine headings for columns
•
To present only the columns and rows a user needs to work with instead of all the
columns and rows in underlying tables or views.
ANSI Views
SQL/MX views conform to the SQL:1999 standard and have these attributes:
•
You can change the data in the underlying table through a view if the view is
updatable. A view that references more than one table is not updatable.
Note. A view does not insulate the programs that use it from being invalidated by changes to
the table definition. Even changes that do not directly affect the view require that the programs
be explicitly SQL compiled to be revalidated. The requirement for explicit compilation is the
same as if the programs used the table directly.