Home > Articles > Data > SQL

Practical SQL: Making the Best Use of Views

  • Print
  • + Share This
Views are virtual tables that focus, simplify, and customize your "view" of the data, presenting just the information you (or others) need. This article starts with a brief review of view construction and then moves on to examine three potential danger points for views.
From the author of

View Vulnerabilities

Views are virtual tables that focus, simplify, and customize your "view" of the data, presenting just the information you (or others) need. This article, based on material from The Practical SQL Handbook 4th edition (Addison-Wesley) and Practical SQL: The Sequel (Addison-Wesley), starts with a brief review of view construction and then moves on to examine three potential danger points for views:

  • Changes to underlying objects
  • Breaks in object chains
  • Performance losses

Unless otherwise noted, code was run on Microsoft SQL Server 7.0.

View Basics

Essentially, a view freezes a SELECT statement, naming it and storing the statement in system catalogs. Here's the simplified syntax of a view definition statement:

Syntax

CREATE VIEW view_name [(column_name [, column_name]...)]
AS
SELECT_statement

The SELECT statement can be as simple or complicated as you choose, involving one or many tables and/or views (collectively called "underlying objects"). Take a look at an example of a view involving a three-table join:

create view titlevu
as
select substring (title,1,35) as 'book title', au_ord, 
 substring (au_lname, 1, 20) as writer, price, ytd_sales, pub_id
from authors, titles, titleauthors
where authors.au_id = titleauthors.au_id
 and titles.title_id = titleauthors.title_id

When you want to know which books a particular author wrote, you can query the view just as if it were a table.

"select *
from titlevu
where writer = 'Green'

book title                               au_ord writer    price    ytd_sales  pub_id 
--------------------------------------   ------ --------- -------- ---------- ------- 
The Busy Executive's Database Guide      2      Green     9.9900   4095       1389
You Can Combat Computer Stress!          1      Green     12.9900  18722      0736
(2 row(s) affected)

Views are not separate copies of data. During a view query, the database system processes the "frozen" CREATE VIEW...SELECT statement, and finds the current data in the underlying tables. Although the view definition remains the same, the rows you retrieve through it always reflect the latest changes in the data.

A view presents only the end results, hiding all the technical underpinnings. You don't need to make complicated joins or understand the relationship between tables. A view aids naive users (they aren't frightened by joins), crafty users (they don't see data that is none of their business), and impatient users (they aren't slowed down by typing long SQL statements).

Despite all these advantages, there are some view problem areas to look out for, all connected to the way views are created and stored.

  • + Share This
  • 🔖 Save To Your Account