Professional SQL Server™ 2005 Programming

9.2. More Complex Views

Perhaps one of the most common uses of views is to flatten data — that is, the removal of complexity that we outlined at the beginning of the chapter. Imagine that we are providing a view for management to make it easier to check on sales information. No offense to managers who are readingthis book, but managers who write their own complex queries are still a rather rare breed — even in the information age.

For an example, let's briefly go back to using the AdventureWorks database. Our manager would like to be able to do simple queries that will tell him or her what orders have been placed for what items and how many sold on each order and related pricing information. So, we create a view that the he or she can perform very simple queries on — remember that we are creating this one in AdventureWorks:

USE AdventureWorks
GO

CREATE VIEW CustomerOrders_vw
AS
SELECT   o.SalesOrderID,
         o.OrderDate,
         od.ProductID,
         p.Name,
         od.OrderQty,
         od.UnitPrice,
         od.LineTotal
FROM Sales.SalesOrderHeader AS o
JOIN   Sales.SalesOrderDetail AS od
    ON o.SalesOrderID = od.SalesOrderID
JOIN   Production.Product AS p
    ON od.ProductID = p.ProductID

Now do a SELECT:

SELECT *
FROM CustomerOrders_vw

You wind up with a bunch of rows — over 100,000 — but you also wind up with information that is far simpler for the average manager to comprehend and sort out. What's more, with not that much training, the manager (or whoever the user might be) can get right to the heart of what he or she is looking ...