CREATE VIEW <view name>
AS
<SELECT statement>

The above 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 <view name> [(<column name list>)]
[WITH [ENCRYPTION] [,SCHEMABINDING] [, VIEW_METADATA]]
AS
<SELECT statement>
WITH CHECK OPTION

We'll be looking at each piece of this individually, but, for now, let's go ahead and dive right in with an extremely simple view. We'll call this one our customer phone list, and create it as CustomerPhoneList_vw in our Accounting database:

USE Accounting
GO

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

Notice that when you execute the CREATE statement in the Query Analyzer, it works just like all the other CREATE statements we've done - it doesn't return any rows. It just lets us know that the view has been created:

The command(s) completed successfully.

Now switch to using the grid view (if you're not already there) to make it easy to see more than one result set. Then run a SELECT statement against your view - using it just as you would for a table - and another against the Customers table directly:

SELECT * FROM CustomerPhoneList_vw

SELECT * FROM Customers

What you get back looks almost identical - indeed, in the columns that they have in common, the two result sets are identical. To clarify how SQL Server is looking at your query on the view, let's break it down logically a bit.

The SELECT statement in your view is defined as:

SELECT CustomerName, Contact, Phone
FROM Customers

So when you run:

SELECT * FROM CustomerPhoneList_vw

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. What's nice about that is that we have reduced the complexity for the end user. In this day and age, where we have so many tools to make life easier for the user, this may not seem like all that big of deal - but to the user, it is.

Be aware that, by default, there is nothing special done for a view. The view runs just as if it were run from the command line - there is no pre-optimization of any kind. This means that you are adding one more layer of overhead between the request for data and the data being delivered. That means that a view is never going to run as fast as if you had just run the underlying SELECT statement directly.

Let's go with another view that illustrates what we can do in terms of hiding sensitive data. For this example, let's go back to our Employees table in our Accounting database. Take a look at the table layout:

Federal law in the US protects some of this information - we must limit access to a "need to know" basis. Other columns though are free for anyone to see. What if we want to expose the unrestricted columns to a group of people, but don't want them to be able to see the general table structure or data? One solution would be to keep a separate table that includes only the columns that we need:

While on the surface this would meet our needs, it is extremely problematic:

• We use disk space twice
• We have a synchronization problem if one table gets updated and the other doesn't
• We have double I/O operations (you have to read and write the data in two places instead of one) whenever we need to insert, update, or delete rows

Views provide an easy and relatively elegant solution to this problem. By using a view, the data is only stored once (in the underlying table or tables) - eliminating all of the problems described above. Instead of building our completely separate table, we can just build a view that will function in a nearly identical fashion.

Our Employees table is currently empty. To add some rows to it, load the Chapter10.sql file (supplied with the source code) into Query Analyzer and run it. Then add the following view to the Accounting database:

USE Accounting
GO

CREATE VIEW Employees_vw
AS
SELECT  EmployeeID,
    FirstName,
    MiddleInitial,
    LastName,
    Title,
    HireDate,
    TerminationDate,
    ManagerEmpID,
    Department
FROM Employees

We are now ready to let everyone have access - directly or indirectly - to the data in the Employees table. Users who have the "need to know" can now be directed to the Employees table, but we continue to deny access to other users. Instead, the users who do not have that "need to know" can have access to our Employees_vw view. If they want to make use of it, they do it just the same as they would against a table:

SELECT *
FROM Employees_vw

This actually gets into one of the sticky areas of naming conventions. Because I've been using the _vw suffix, it's pretty easy to see that this is a view and not a table. Sometimes, you'd like to make things a little more hidden than that, so you might want to deliberately leave the _vw off. Doing so means that you have to use a different name (Employees is already the name of the base table), but you'd be surprised how many users won't know that there's a difference between a view and a table if you do it this way.

Views as Filters

This will probably be one of the shortest sections in the book. Why? Well, it doesn't get much simpler than this.

You've already seen how to create a simple view - you just use an easy SELECT statement. How do we filter the results of our queries? With a WHERE clause. Views are no different.

Let's take our Employees_vw view from the last section, and beef it up a bit by making it a list of only current employees. To do this, there are really only two changes that need to be made.

First, we have to filter out employees who no longer work for the company. Would a current employee have a termination date? Probably not, so, if we limit our results to rows with a NULL TerminationDate, then we've got what we're after.

The second change illustrates another simple point about views working just like queries - the column(s) contained in the WHERE clause do not need to be included in the SELECT list. In this case, it doesn't make any sense to include the termination date in the result set as we're talking about current employees.

With these two things in mind, let's create a new view by changing our old view around just a little bit:

CREATE VIEW CurrentEmployees_vw
AS
SELECT  EmployeeID,
    FirstName,
    MiddleInitial,
    LastName,
    Title,
    HireDate,
    ManagerEmpID,
    Department
FROM Employees
WHERE TerminationDate IS NULL

In addition to the name change and the WHERE clause we've added, note that we've also eliminated the TerminationDate column from the SELECT list.

Let's test out how this works a little bit by running a straight SELECT statement against our Employees table and limiting our SELECT list to the things that we care about:

SELECT  EmployeeID,
    FirstName,
    LastName,
    TerminationDate
FROM Employees

This gets us back a few columns from all the rows in the entire table:

Now let's check out our view:

SELECT  EmployeeID,
    FirstName,
    LastName
FROM CurrentEmployees_vw

Our result set has become a bit smaller:

A few people are missing versus our first select - just the way we wanted it.