In my previous article, ‘SQL View Basics‘, I discussed a  VIEW as being a virtual table that does not exist until it is invoked by name in SQL statements, and which disappears at the end of that statement. The strong advantage of a VIEW is that it will produce the correct results when it is invoked, based on the current data. Trying to do the same sort of things with temporary tables or computed columns within a table can be subject to errors and is likely to be slower to read from disk. Let’s keep going and look at some code.

Types of VIEWs

We can classify a view as being either read-only or updatable. This is handy, but we can also use the type of SELECT statement in the VIEWs to classify them. These two methods can overlap, but let’s start with the SELECT statements method.

Single-Table Projection and Restriction

In practice, many VIEWs are projections or restrictions on a single base table. This is a common method for implementing security-control by removing rows or columns that a particular group of users is not allowed to see. These VIEWs are usually updatable and represent the most common VIEW pattern.

Translated Columns

Another common use of a VIEW is to translate codes into text or other codes by doing table look ups. This is a special case of a joined VIEW based on a FOREIGN KEY relationship between two tables. For example, an order table might use a part number that we wish to display with a part name on an order entry screen. This is done with a JOIN between the Orders table and the Inventory table, thus:

The idea of JOIN VIEWs to translate codes can be expanded to show more than just one translated column. The result is often a “star” query with one table in the center, joined by FOREIGN KEY relations to many other tables to produce a result that is more readable than the original central table.

Missing values are a problem. If there is no translation for a given encoding, no row appears in the VIEW, or if an OUTER JOIN was used, a NULL will appear. The programmer should establish a referential integrity constraint to CASCADE changes between the tables to prevent loss of data.

Grouped VIEWs

A grouped VIEW is based on a query with a GROUP BY clause. Since each of the groups may have more than one row in the base from which it was built, these are necessarily read-only VIEWs. Such VIEWs usually have one or more aggregate functions and they are used for reporting purposes. They are also handy for working around weaknesses in SQL. Consider a VIEW that shows the largest sale in each state. The query is straightforward:

SQL does not require that the grouping column(s) appear in the select clause, but it is a good idea in this case.

These VIEWs are also useful for “flattening out” one-to-many relationships. For example, consider a Personnel table, keyed on the employee number (emp_nbr), and a table of dependents, keyed on a combination of the employee number for each dependent’s parent (emp_nbr) and the dependent’s own serial number (dep_id). The goal is to produce a report of the employees by name with the number of dependents each has.

The report is simply an OUTER JOIN between this VIEW and the Personnel table.

The OUTER JOIN is needed to account for employees without dependents with a NULL value, like this.

UNION-ed VIEWs

VIEWs based on a UNION or UNION ALL operation are read-only because there is no single way to map a change onto just one row in one of the base tables. The UNION operator will remove duplicate rows from the results. Both the UNION and UNION ALL operators hide which table the rows came from. Such VIEWs must use a <view column list>, because the columns in a UNION [ALL] have no names of their own. In theory, a UNION of two disjoint tables, neither of which has duplicate rows in itself should be updatable.

The problem given in the section on grouped VIEWs, could also be done with a UNION query that would assign a count of zero to employees without dependents, thus:

The report is now a simple INNER JOIN between this VIEW and the Personnel table. The zero value, instead of a NULL value, will account for employees without dependents.

Calculated Columns

The main reason for hiding computations in a VIEW is so that the computation is done one way, one place, one time and so it gets a unique data element name. One common use for a VIEW is to provide summary data across a row. For example, given a table with measurements in metric units, we can construct a VIEW that hides the calculations to convert them into English units.

It is important to be sure that you have no problems with NULL values when constructing a calculated column. For example, given a Personnel table with columns for both salary and commission, you might construct this VIEW:

Office workers do not get commissions, so the value of their commission column will be NULL, so we use the COALESCE() function to change the NULLs to zeros.

SQL Server introduced a computed column construct in their table declaration syntax, <expression> AS <column name>. This is proprietary and has some limitations in that it can only reference columns in the same row. This can be a way to avoid a VIEW and is a good idea to reduce the number of objects in a schema.

Computed VIEWs not just for fancy math; strings and temporal data also have computations. For example a VIEW can hide all but the last four digits of a 16-digit credit card number:

likewise, the following Monday from a weekend date is easy to put into a computation business_date

Updatable and Read-Only VIEWs

Unlike base tables, VIEWs are either updatable or read-only, but not both. INSERT, UPDATE, and DELETE operations are allowed on updatable VIEWs and base tables, subject to any other constraints. INSERT, UPDATE, and DELETE are not allowed on read-only VIEWs, but you can change their base tables, as you would expect.

For the computer science majors, this is an NP-complete problem. Since we have no general solution, we need a way to handle each VIEW with particular code. We have a way to do that!

The INSTEAD OF Trigger

The INSTEAD OF trigger is how to resolve the VIEW updating problem. It is possible for a user to write INSTEAD OF triggers on VIEWs, which catch the changes and route them to the base tables that make up the VIEW. The database designer has complete control over the way VIEWs are handled.

These triggers are added to a VIEW and are executed on base tables instead of making changes directly to the VIEW it self. If you think about it, how would a program change the VIEW anyway? It does not exist.

The basic syntax is fairly simple; T-SQL has a lot of other options but we will not be concerned with them.

Triggers have a global scope for obvious reasons. The options INSERT, UPDATE or DELETE are called “database events” and they cause the trigger to fire. As a general heuristic, you will want to have your SQL statement fire on all three events.

The trigger’s SQL statement is executed instead of the triggering SQL statement. The attempted INSERT, UPDATE or DELETE has no effect at all. If you feel fancy, you can define views on views where each view has its own INSTEAD OF trigger.

INSTEAD OF triggers are not allowed on updatable views that use WITH CHECK OPTION. SQL Server raises an error when an INSTEAD OF trigger is added to an updatable view WITH CHECK OPTION specified. The user must remove that option by using ALTER VIEW before defining the INSTEAD OF trigger.

For INSTEAD OF triggers, the DELETE option is not allowed on tables that have an ON DELETE CASCADE referential action. Similarly, the UPDATE option is not allowed on tables that have ON UPDATE CASCADE referential action.

This can be complicated, so let us start with a very example of a read-only VIEW and its base tables.

Now let’s declare a VIEW that can’t be updated.

As a simple example, when you do an insertion, put half of an amount into each of the two hidden tables, Alpha and Beta. If the amount is an odd number, favor one of the two hidden tables.

Put in one row of data via the view and not the base tables.

Now look at what happened by looking at the VIEW and the hidden tables in it.

Jerry

51

Jerry

25

Jerry

26

That was nice. Now let’s do a delete trigger

And fire the trigger:

Please notice the use of the MERGE in this trigger. You need to learn how to write standard SQL.  Also, when this fires you will get three messages, one for the VIEW and one for each table.

WITH CHECK OPTION Example

In my previous article, ‘SQL View Basics‘,, I mentioned the use of the WITH CHECK OPTION but did not give an example. Let us define a simple Organizational Chart table in a nested sets model. I am going to assume that you know this SQL idiom and so I will not explain it.

 Let us define a simple Organizational Chart table in a nested sets model. I am going to assume that you know this SQL idiom and I will not explain it in detail.

The (lft, rgt) pairs are like tags in a mark-up language, or parentheses in algebra, BEGIN-END blocks in Algol-family programming languages, etc. — they bracket a sub-set. This is a set-oriented approach to trees in a set-oriented language.

 Notice all of the constraints on the columns at the column and table level. But we are missing two constraints we need. The first is that we have a single root. The second is that the (lft, rgt) pairs do not overlap .. that is, we really have nesting.

This assumes that we start with a root node that has (lft = 1) and build from that. That could be enforced with another predicate in the  VIEW, if you wish.

 Try these statements and watch the WITH CHECK OPTION errors. The first one is a dangling node not under the root node, the second is an overlapping range.

giving the error…

SUMMARY

VIEWs are not as simple as most SQL programmers first think they are. But they are worth the effort because they are powerful and, because they are declarative, they can be optimized.