Sybase Adaptive Server IQ 12.4.2, Administration And Performance Manual

Page: 148 / 536

Working with views
128
Similarities between
views and base tables
Views are similar to the permanent tables of the database (a permanent table is
also called a base table) in many ways:
• You can assign access permissions to views just as to base tables.
• You can perform
SELECT
queries on views.
• You can perform
INSERT
and
DELETE
operations on some views.
• You can create views based on other views.
Differences between
views and permanent
tables
There are some differences between views and permanent tables:
• You cannot create indexes on views.
• You cannot perform
INSERT
,
DELETE
, and
UPDATE
operations on all
views.
• You cannot assign integrity constraints and keys to views.
• Views refer to the information in base tables, but do not hold copies of that
information. Views are recomputed each time you invoke them.
Benefits of tailoring
access
Views are used to tailor access to data in the database. Tailoring access serves
several purposes:
• Improved security By not allowing access to information that is not
relevant.
• Improved usability
By presenting users and application developers
with data in a more easily understood form than in the base tables.
• Improved consistency By centralizing in the database the definition
of common queries.
Creating views
A
SELECT
statement operates on one or more tables and produces a result set
that is also a table: just like a base table, a result set from a
SELECT
query has
columns and rows. A view gives a name to a particular query, and holds the
definition in the database system tables.
Example Suppose that you frequently need to list the number of employees in each
department. You can get this list with the following statement:
SELECT dept_ID, count(*)
FROM employee
GROUP BY dept_ID
You can create a view containing the results of this statement as follows: