What we're doing with more complex views is really just adding joins, summarization, and perhaps some column renaming.

For those of you who will be applying this information to versions of SQL Server prior to 7.0, be aware that there is a limit to the number of joins that you can do. This might bring in the question of, "What that has to do with views?" Everything!

As I've mentioned before, views are nothing more than stored queries. For versions prior to 7.0, your queries that are part of views (and all queries for that matter) are limited to 16 joins. It doesn't stop there though - when you do a query that makes use of views, the joins that happen inside the view also count toward your 16 joins limit. That means that if you try to join 4 views that each join 5 tables, you're at 20 joins, and you're over the limit. Versions 7.0 and beyond have a limit of 256 tables - a limit you should never bump into.

You can get around this limit by performing multiple queries and creating temporary working tables that summarize some of the joins in your query. In the end, you join the working tables for a final result. Just be aware that there is a performance penalty to be paid when you take such a long route to your results.

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 reading this 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 Northwind 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 parts and who placed them. So, we create a view that they can perform very simple queries on - remember that we are creating this one in Northwind:

USE Northwind
GO

CREATE VIEW CustomerOrders_vw
AS
SELECT  cu.CompanyName,
    o.OrderID,
    o.OrderDate,
    od.ProductID,
    p.ProductName,
    od.Quantity,
    od.UnitPrice,
    od.Quantity * od.UnitPrice AS ExtendedPrice
FROM   Customers AS cu
INNER JOIN  Orders AS o
   ON cu.CustomerID = o.CustomerID
INNER JOIN  [Order Details] AS od
   ON o.OrderID = od.OrderID
INNER JOIN  Products AS p
   ON od.ProductID = p.ProductID

Now do a SELECT:

SELECT *
FROM CustomerOrders_vw

You wind up with a bunch of rows - over 2000 - 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 they are looking for:

SELECT CompanyName, ExtendedPrice
FROM CustomerOrders_vw
WHERE OrderDate = '9/3/1996'

The user didn't need to know how to do a four-table join - that was hidden in the view. Instead, they only need limited skill (and limited imagination for that matter) in order to get the job done.

However, we could make our query even more targeted. Let's say that we only want our view to return yesterday's sales. We'll make only slight changes to our query:

USE Northwind
GO

CREATE VIEW YesterdaysOrders_vw
AS
SELECT  cu.CompanyName,
    o.OrderID,
    o.OrderDate,
    od.ProductID,
    p.ProductName,
    od.Quantity,
    od.UnitPrice,
    od.Quantity * od.UnitPrice AS ExtendedPrice
FROM   Customers AS cu
INNER JOIN  Orders AS o
   ON cu.CustomerID = o.CustomerID
INNER JOIN  [Order Details] AS od
   ON o.OrderID = od.OrderID
INNER JOIN  Products AS p
   ON od.ProductID = p.ProductID
WHERE CONVERT(varchar(12),o.OrderDate,101) = 
   CONVERT(varchar(12),DATEADD(day,-1,GETDATE()),101)

All the dates in the Northwind database are old enough that this view wouldn't return any data, so let's add a row to test it. Execute the following script all at one time:

USE Northwind

DECLARE @Ident int

INSERT INTO Orders
(CustomerID,OrderDate)
VALUES
('ALFKI', DATEADD(day,-1,GETDATE()))

SELECT @Ident = @@IDENTITY

INSERT INTO [Order Details]
(OrderID, ProductID, UnitPrice, Quantity)
VALUES
(@Ident, 1, 50, 25)

SELECT 'The OrderID of the INSERTed row is ' + CONVERT(varchar(8),@Ident)

I'll be explaining all of what is going on here in our chapter on scripts and batches. For now, just trust me that you'll need to run all of this in order for us to have a value in Northwind that will come up for our view. You should see a result from the Query Analyzer that looks something like this:

Be aware that some of the messages shown above will only appear on the Messages tab if you are using Query Analyzer's Results In Grid mode.

The OrderID might vary, but the rest should hold pretty true.

Now let's run a query against our view and see what we get:

SELECT CompanyName, OrderID, OrderDate FROM YesterdaysOrders_vw

You can see that the 11087 does indeed show up:

Don't get stuck on the notion that your OrderID numbers are going to be the same as mine - these are set by the system (since OrderID is an identity column), and are dependent on just how many rows have already been inserted into the table. As such, your numbers will vary.

Continued...