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 ...
Get Professional SQL Server™ 2005 Programming now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.