SQL Server Reference Guide

I'm continuing a tutorial overview on database objects with the next in the line of data representation objects: the View. I'll explain the processes used to create a View, and then explain the types of Views and the ways they're used.

Views are simply the result of a SELECT statement. They don't hold any data at all — they simply point to the data somewhere else. so why use them instead of just using the SELECT statements that make them up?

The answer lies in the fact that a user (or more accurately, the code that the user is executing in a program) can treat a View as if it is a Table. So you can create complex joins, limit results with the WHERE statement and so on to hide all that complexity, and simply point the user to the View instead. They also simplify security, which I'll explain in a moment.

The statements that make up a View are stored as text inside the database on a SQL Server. As I mentioned, they don't hold any data themselves, they simply run when the SELECT statement references them. That means the View is treated by code as a table.

I'll mention a couple of things here before I dive in to more details, since they come up right away. Although the View is treated as a table, it isn't. There are some limitations on how they are used, specifically with INSERT operations. Many new Transact-SQL (T-SQL) writers want to join multiple tables in a View, and then insert data through the View into all of those tables at once, hoping that the keys will just automatically work. That's not how they work — while you can insert data into a View, it can only touch one table underneath the View (called a "base table") at a time. You should really think about a View by how it is named — as a "View on data". That will keep you out of trouble.

Aside from hiding the join syntax from a developer or user, Views allow you to limit data. In fact, there are two vectors for these limitations. The first is in the columns you select in the View. If you have a table with ten columns and you only select three of them, you've vertically limited the amount of data you show to the user. A typical use of this vector is when you have a personnel table with more data than many folks are allowed to see. You might want to expose the names and phone numbers, for instance, but not the home address. By including only the name and phone field and not allowing the users to access the base tables (more on that in a moment) you have limited your data to just the right folks.

The second vector is in the join operation itself and the WHERE clause you use. For instance, joining the personnel table to the addresses table in that simple example and using a WHERE private = "FALSE" would be one way to limit the number of rows coming back to those where the column "private" has been marked "FALSE". In this way you have limited the data horizontally. Views, then, can simplify the join syntax, and can limit the data vertically and horizontally as well.

Views also simplify security. All SQL Server objects have security attributes, and the View is no exception. You can GRANT, REVOKE or DENY permissions to the View just as you do on tables (see my article about object security). If the same owner (normally the developer) creates both the View and the tables, the user only needs rights to the View — not the base tables. SQL Server looks "though" the View, in this case. This is how the security is simplified for your users.

Now I'll explain the processes you can use to create a View, and then I'll explain the types of Views and the ways that they are used.

Creating Views

When you're getting started, you can use various graphical tools to create a View. In SQL Server 2000 and older, the Enterprise Manager has a "wizard" that will guide you through the process. From the main menu bar, select "Tools" and then "Wizards...". Once you see the "Select Wizard" panel, expand the Database object, and then select the "Create View Wizard" object from the tree.

The wizard will walk you through selecting databases, tables and columns to include in the View. When you've done that, you can also specify a WHERE clause to restrict the View. While the Create View Wizard is simple, it isn't very powerful. The most difficult part of a creating a View isn't selecting the columns; it's restricting the rows and performing the joins. The wizard doesn't help you very much with that.

In SQL Server 2005 and higher you can open the database in the SQL Server Management Studio (SSMS) Object Browser, and then right-click the "View" object. From there you can select "Create View" from the menu that appears. You're given a list of tables to choose, and if your Primary and Foreign Keys are set up on the tables you choose the system will even take a guess at the joins that relate them. You can then check the columns you want in your View, and limit the View results with a set of criteria.

Personally, I think in the case of Views the graphical tools can get in the way. It's actually far simpler to just create a View using the T-SQL language. The basic syntax to create a View is as follows:

 CREATE VIEW ViewName
AS
SELECT SomeColumn, SomeColumn 
FROM SomeTable 
WHERE SomeCondition

I'll talk about some of the restrictions on creating Views near the end of this article.

Once the View is created, it's a simple matter to change it. The command syntax for that process is:

 ALTER VIEW ViewName
AS
SELECT SomeColumn, SomeColumn 
FROM SomeTable 
WHERE SomeCondition

Looks a bit like the CREATE VIEW statement, doesn't it? Why not just drop the View, like this…

DROP VIEW ViewName

(You won't be asked to confirm the drop, so make sure that's really what you want to do!)

and then recreate it? The reason you want to use the ALTER statement is that it will keep the security intact — if you drop the View and re-create it, it's a new object, and has the security you create it with.

In fact, the process I normally follow is to write my T-SQL statement such that I get the result of data I want. Once I have it set up, then I "re-factor" (rewrite) the query so that it returns the data in the most optimal way it can. After I tune the query, I simply put CREATE VIEW Viewname AS in front of the T-SQL Statement and the View is ready.

Using Views

Now that you know how Views are created, altered and destroyed, let's spend a few minutes talking about the way Views are used.

The first type of View contains data from a single table. This type of View is useful for vertically or horizontally partitioning the data.

Let's consider two scenarios. Assume that you have a table of data that contains more information than a certain group of people should have access to. For instance, a table might contain the names and departments of employees in a company and their salary grade. You want everyone to see the names of the employees but not their salary grade. To accomplish this goal, you set up a View that selects only the columns of data you want, and then grant permissions on that View to the proper group of people. This is a vertical partition of the data.

Here's an example of vertical partitioning, using the pubs sample database:

USE pubs;
GO
CREATE VIEW TestView
AS
SELECT au_fname, au_lname FROM authors
GO

In the second situation, you might want to restrict that same table such that the users can only see the employee names of people in their own department. To accomplish this goal, the View would select rows of data where certain criteria are met. Again, you grant permission to the View to the appropriate group; the users access the data through this horizontally partitioned View.

Here's an example of horizontal partitioning:

 CREATE VIEW TestView
AS
SELECT * FROM authors
WHERE au_lname LIKE 'W&'
GO

Of course, you might combine vertical and horizontal partitioning to limit both the columns and rows displayed:

 CREATE VIEW TestView
AS
SELECT au_fname, au_lname FROM authors
WHERE au_lname LIKE 'W&'
GO

The second type of View spans multiple tables. Combining various tables in one select statement has the advantage of hiding the complexity of select joins and unions from the user.

The multiple-table join is quite common in programming shops where the database design is a modular step. In this situation, the database tables are designed from a performance or logical layout perspective, and Views are created to provide friendly names or abstract the tables from the development of an interface.

Here's an example of a multiple-table View:

CREATE VIEW TestView
AS
SELECT *
FROM authors AS a INNER JOIN publishers AS p
 ON a.city = p.city

I'm not going to spend time here describing the selection or join syntax, but note that we're referencing two tables here. In future View creation, I'll include even more tables.

Not only can you create Views across multiple tables, but you can also create Views across multiple databases. If the databases are on the same server, and the user has a login to both databases, and the permissions are set properly, then you can access the data in two databases as easily as one.

Assuming two databases, one called pubs and the other called pubs2, here's an example of a multiple database View:

CREATE VIEW TestView
AS
SELECT *
FROM pubs.dbo.authors AS a INNER JOIN pubs2.dbo.publishers AS p
 ON a.city = p.city

Notice that I've prefaced the table names this time with two items separated by periods. The first preface (pubs) is the database name, and the second (dbo) is the owner of the database. These are actually only two prefaces; there are three that you can use. The new preface is a server name, used in distributed queries, which I'll explain more about in another article.

Moving on from these View types, you can also include a View in another View. To do this, treat the referenced View just like any other table.

There are a few restrictions regarding creating Views.

You can only reference one base table on an insert, update or delete action. That means that if you're referencing more than one table in the View, you can only perform an update, insert or delete on one of them (at a time). Trying to perform these actions against more than one table at a time through a View is a classic "newbie" mistake. (Okay, I still forget and make that mistake from time to time!)

Also, you can't use the ORDER BY clause in your select statement when you create the View. You can use the ORDER BY statement when you select from the View, though. Remember, a View is just the result of a SELECT statement.

In a View, you can't use parameters to pass criteria for the View. If you want to do that, you really want a stored procedure. I'll explain those in an upcoming article.

Who knew that a simple subject like a View of data could be this involved! Actually, there's even more to know about Views. I'll show you more as we go along the design phase of our application.