Professional SQL Server™ 2005 Programming

9.1. Simple Views

The syntax for a view, in its most basic form, is a combination of a couple of things we've already seen in the book — the basic CREATE statement that we saw back in Chapter 4, plus a SELECT statement like the ones we've used over and over again:

CREATE VIEW <view name>
AS
<SELECT statement>

The preceding syntax just represents the minimum, of course, but it's still all we need in a large percentage of the situations. The more extended syntax looks like this:

CREATE VIEW [<schema name>].<view name> [(<column name list>)]
[WITH [ENCRYPTION] [, SCHEMABINDING] [, VIEW_METADATA]]
AS
<SELECT statement>
WITH CHECK OPTION

So, an extremely simple view on the Accounting database we used back in Chapter 5 might looksomething like:

USE Accounting
GO

CREATE VIEW CustomerPhoneList_vw
AS
   SELECT CustomerName,Contact, Phone
   FROM Customers

So, when you run:

SELECT * FROM CustomerPhoneList_vw

You get back exactly the same thing as:

SELECT CustomerName, Contact, Phone
FROM Customers

You are essentially saying to SQL Server: "Give me all of the rows and columns you get when you run the statement SELECT CustomerName, Contact, Phone FROM Customers."

We've created something of a pass-through situation — that is, our view hasn't really changed anything, but rather just "passed through" a filtered version of the data it was accessing. Think about the uses for this a bit, and you should be able to see how this concept can be utilized to do things like simplify the data for inexperienced ...