| |
 |
|
SQL Server
Tips by Burleson |
SQL Views Transformed
"In 1985, Codd published a set of 12 rules to be used as "part of a
test to determine whether a product that is claimed to be fully
relational is actually so". His Rule No. 6 required that all views
that are theoretically updatable also be updatable by the system."
-- C. J. Date, Introduction To Database Systems
IBM DB2 v 8.1, Microsoft SQL Server 2000, and Oracle9i all support
views (yawn). More interesting is the fact that they support very
similar advanced features (extensions to the SQL-99 Standard), in a
very similar manner.
Syntax
As a preliminary definition, let's say that a view is something that
you can create with a CREATE VIEW statement, like this:
CREATE VIEW <View name>
[ <view column list> ]
AS <query expression>
[ WITH CHECK OPTION ]
This is a subset of the SQL-99 syntax for a view definition. It's
comforting to know that "The Big Three" DBMSs — DB2, SQL Server, and
Oracle — can all handle this syntax without any problem. In this
article, I'll discuss just how these DBMSs "do" views: what
surprises exist, what happens internally, and what features The Big
Three present, beyond the call of duty.
I'll start with two Cheerful Little Facts, which I'm sure will
surprise most people below the rank of DBA.
Cheerful Little Fact #1:
The CHECK OPTION clause doesn't work the same way that a CHECK
constraint works! Watch this:
CREATE TABLE Table1 (column1 INT)
CREATE VIEW View1 AS
SELECT column1 FROM Table1 WHERE column1 > 0
WITH CHECK OPTION
INSERT INTO View1 VALUES (NULL) <-- This fails!
CREATE TABLE Table2 (column1 INT, CHECK (column1 > 0))
INSERT INTO Table2 VALUES (NULL) <-- This succeeds!
The difference, and the reason that the Insert-Into-View statement
fails while the Insert-Into-Table statement succeeds, is that a
view's CHECK OPTION must be TRUE while a table's CHECK constraint
can be either TRUE or UNKNOWN.
Cheerful Little Fact #2:
Dropping the table doesn't cause dropping of the view! Watch this:
CREATE TABLE Table3 (column1 INT)
CREATE VIEW View3 AS SELECT column1 FROM Table3
DROP TABLE Table3
CREATE TABLE Table3 (column0 CHAR(5), column1 SMALLINT)
INSERT INTO Table3 VALUES ('xxxxx', 1)
SELECT * FROM View3 <-- This succeeds!
This bizarre behavior is exclusive to Oracle8i and Microsoft SQL
Server — when you drop a table, the views on the table are still out
there, lurking. If you then create a new table with the same name,
the view on the old table becomes valid again! Apart from the fact
that this is a potential security flaw and a violation of the SQL
Standard, it illustrates a vital point: The attributes of view View3
were obviously not fixed in stone at the time the view was created.
At first, View3 was a view of the first (INT) column, but by the
time the SELECT statement was executed, View3 was a view of the
second (SMALLINT) column. This is the proof that views are reparsed
and executed when needed, not earlier.
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
|
