UNION and the View

Prior to SQL Server v6.5, the UNION keyword was not allowed in a view. UNION allows the combination, in a single result set, of the results from multiple SELECT statements. This query shows author last names and employee last names sorted together:

select     au_lname
from       authors
union
select    lname
from       employee
order   by   au_lname

au_lname 
--------------------
Accorti
Afonso
Ashworth
Bennet
Bennett
[…]
(64  row(s)  affected)

All last names from employees are listed together with last names from authors. All of the names are ordered. To order a unioned result set, list the ORDER BY last, and specify the column name from the first SELECT.

You can combine multiple result sets with several UNIONs. The result sets that each SELECT returns must be composed of the same number of columns, and those columns must return compatible data types.

Now, in 6.5, a view with a UNION opens up some powerful opportunities. It is especially useful in data warehousing operations. Consider this possibility: you have four databases, one for each factory your company runs, named chicago, newyork, detroit, and troy. Each of the databases contains identical table structures, and data relevant to each factory.

You want to get the sum of units produced for October. Operating on just the base tables, in each of the databases you would have to run a query like this:

select    sum(qty)
from       production
where      prod_date >=   ’10/01/96’
            and prod_date < ’11/01/96’

Then, you would need to take the four results and add them together yourself.

Instead, you could create a view like this one, which combines the data from the production tables in all four databases:

create     view     allproduction    as
select     *      from    chicago..production
union
select     *      from    newyork..production
union
select     *      from    detroit..production
union
select     *      from    troy..production

You now have a view that combines the data from all production tables in all four databases. You can use this view to calculate aggregates, like the query you saw earlier in this section, or to run any other kind of query against. You can select from this view and order the results, interspersing all factories’ data together in a single report.

Conditional Views

I’ll create two views in this section. Both views will be based on a mythical sales table, not related to the pubs database. Table 10.3 shows the table definition.

In case you were wondering, the individual items for my mythical database would be tracked in a salesdetail table, composed of one row for each item, with a foreign key to sales on sal_id. Here is the creation statement used to build the table:

create     table    sales(
           sal_id   int   identity(1,1),
           cust_id  int not null,
           uid smallint not null default user_id(CURRENT_USER),
           sal_date smalldatetime not null default
           CURRENT_TIMESTAMP, amt smallmoney null)

To insert into the table, there is an order entry application program that salesmen use. The salesman provides the customer and selects items for purchase. When he’s done taking the order, he submits the order.

The application program prepares INSERT statements for customer, salesdetail, and the sales table. Here is a sample INSERT statement for sales:

insert    sales    (cust_id,    amt)    values(112,    $206.94)

When this INSERT runs, the following data will be inserted into the sales table:

sal_id      cust_id       uid      sal_date                amt
---------   -----------   ------   -------------------     --------
2066        112           5        Dec 20 1996  3:22PM     206.94

For the unspecified columns, here are the values they receive: sal_id gets an identity value inserted, uid has the current salesman’s user ID inserted, and the sal_date is provided by a call to CURRENT_TIMESTAMP.

Now, the point of this exercise: to create a view so that a database user sees only rows for sales he made, and to create a view that returns rows based on the value of uid:

create  view  salesman  as
select  sal_id,  cust_id,  sal_date,  amt
from    sales
where   uid = user_id()
with    check option

The salesmen will not have any use for seeing their SQL Server user IDs, so this column is omitted from the view. Although they might not use the sal_id identity column, the order-entry application program might want that information. The program may keep it hidden but use the information to get rows from the salesdetail table.

The WITH CHECK OPTION is used to ensure that a salesman not be allowed to update or delete another user’s data. Although the current application may not directly alter the table, the far-sighted administrator creating this view knew that other, as yet unwritten, programs might use the view to alter the base tables.