![background image](http://html1.mh-extra.com/html/sybase/adaptive-server-iq-12-4-2/adaptive-server-iq-12-4-2_administration-and-performance-manual_1416503149.webp)
CHAPTER 3 Working with Database Objects
129
CREATE VIEW DepartmentSize AS
SELECT dept_ID, count(*)
FROM employee
GROUP BY dept_ID
The information in a view is not stored separately in the database. Each time
you refer to the view, the associated
SELECT
statement is executed to retrieve
the appropriate data.
On one hand, this is good because it means that if someone modifies the
employee
table, the information in the
DepartmentSize
view will be
automatically up to date. On the other hand, complicated
SELECT
statements
may increase the amount of time SQL requires to find the correct information
every time you use the view.
❖
To create a view in Sybase Central:
1
Connect to the database.
2
Click the Views folder for that database.
3
Double-click Add View.
4
Enter the tables and columns to be used. For instance, to create the same
view as in the SQL example shown above, enter employee and dept_ID.
5
From the File menu select Execute Script and from the File menu select
Close.
For more information, see the Sybase Central online Help.
Using views
When you use views, you need to be aware of certain restrictions, both on the
SELECT
statements you can use to create them, and on your ability to insert
into, delete from, or update them.
Restrictions on
SELECT statements
There are some restrictions on the
SELECT
statements that you can use as
views. In particular, you cannot use an
ORDER BY
clause in the
SELECT
query.
A characteristic of relational tables is that there is no significance to the
ordering of the rows or columns, and using an
ORDER BY
clause would impose
an order on the rows of the view. You can use the
GROUP BY
clause,
subqueries, and joins in view definitions.
To develop a view, tune the
SELECT
query by itself until it provides exactly the
results you need in the format you want. Once you have the
SELECT
query just
right, you can add a phrase in front of the query to create the view. For example:
Summary of Contents for Adaptive Server IQ 12.4.2
Page 1: ...Administration and Performance Guide Adaptive Server IQ 12 4 2 ...
Page 16: ...xvi ...
Page 20: ...Related documents xx ...
Page 40: ...Compatibility with earlier versions 20 ...
Page 118: ...Troubleshooting startup shutdown and connections 98 ...
Page 248: ...Importing data by replication 228 ...
Page 306: ...Integrity rules in the system tables 286 ...
Page 334: ...Cursors in transactions 314 ...
Page 396: ...Users and permissions in the system tables 376 ...
Page 438: ...Determining your data backup and recovery strategy 418 ...
Page 484: ...Network performance 464 ...
Page 500: ...System utilities to monitor CPU use 480 ...
Page 514: ...Characteristics of Open Client and jConnect connections 494 ...
Page 536: ...Index 516 ...