 |
|
SQL Server
Tips by Burleson |
Alternatives to Views
Think of the typical hierarchy: person, employee, manager.
Each of these items can easily be handled in individual tables if a
UNION ALL view is available when you want to deal with attributes
that are held in common by all three tables. But in future it might
be better to use subtables and supertables, since subtables and
supertables were designed to handle hierarchies. The decision might
rest on how well your organization is adjusting to your DBMS's new
Object/Relational features.
You cannot create a view with a definition that contains a
parameter, so you might have to make a view for each separate
situation:
CREATE VIEW View1 AS
SELECT * FROM Table1
WHERE column1 = 1
WITH CHECK OPTION
CREATE VIEW View2 AS
SELECT * FROM Table1
WHERE...
See code depot
And so on. But in future this too might become obsolete. It is
already fairly easy to make stored procedures that handle the job.
If you want to do a materialization but don't want (or don't have
the authority) to make a new view, you can do the job within one
statement. For example, if this is your view:
CREATE VIEW View1 AS
SELECT MAX(column1) AS view_column1
FROM Table1
GROUP BY column2
then instead of this:
SELECT AVG(view_column1)
FROM View1
do this:
SELECT AVG(view_column1)
FROM (SELECT MAX(column1) AS view_column1
FROM Table1 GROUP BY column2) AS View1
In fact, this is so similar to using a view that many people call it
a view —"inline view" is the common term — but in standard SQL the
correct term for [that thing that looks like a subquery in the FROM
clause] is: table reference.
This is a book excerpt from:
Advanced SQL Database Programmer Handbook
Donald K. Burleson, Joe Celko, John Paul Cook, Peter Gulutzan
ISBN: 0-9744355-2-X
http://www.rampant-books.com/ebook_dbazine_SQL_prog.htm
|
