Practical SQL: Making the Best Use of Views
- View Vulnerabilities
- Changes to Underlying Objects
- Breaks In Chains
- Performance
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
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.