Creating and Optimizing Views in SQL Server

In This Chapter

• Defining Views

• Creating and Modifying Views

• Using Views to Update Data

• Indexed Views

• Optimizing Views and Queries

Defining Views

Views are often puzzling to Access developers. It can take a while to understand exactly what views can and cannot do. Think of views as saved SELECT statements, with just a couple of limitations. The biggest problem is that just when you get used to accepting a limitation, Microsoft comes up with an amazing, new feature that overcomes it! It's great to get the extra features, but it makes it harder to nail down exactly what you can and cannot do with views.

Views have never in the past been able to contain parameters; however, as shown later in the chapter, user-defined functions can now be used like views, effectively allowing you to create parameterized views that return different results depending on the parameter values that get passed in each time the function is run. Unlike stored procedures (which also support parameters), these parameterized functions can be updateable, as you'll see later in this chapter.

The SQL-92 standard also mandates that views cannot be sorted with an ORDER BY clause. Like tables in a relational database, the order of the records in a view is undefined. However, Transact-SQL includes an extension to the language that allows you to select TOP values with a view; to support that, it also supports sorting. To return the top 10 customers or the top 10% of customers, based on sales over the past year, SQL Server needs to sort customers by sales. So, a view that returns the top 100% by sales would essentially be a sorted view. The TOP syntax is covered in Chapter 8, "Introduction to Transact-SQL (T-SQL)."

Views have also never been able to contain triggers. In SQL Server 2000, however, a view can contain one INSTEAD OF trigger for each triggering action (INSERT, UPDATE, and DELETE). If any of these actions is performed on a view, SQL Server calls the INSTEAD OF trigger "instead of" making any direct changes to the data. INSTEAD OF triggers in views are covered later in this chapter.

Another limitation of views that you may be familiar with is that a view cannot be indexed. Guess what? In SQL Server 2000, you can indeed create indexed views, as shown later in this chapter.

Views still, however, don't allow you to perform any of the other major SQL actions besides selecting—views can't contain INSERT, UPDATE, or DELETE statements or DDL (Data Definition Language).

View Syntax

View syntax is very simple—it's nothing more than a SELECT statement with a CREATE VIEW...AS wrapper that causes the statement to be saved as a view. Listing 9.1 shows a simple view that selects certain columns from the Customers table in the Northwind database.

Listing 9.1 A Simple View

CREATE VIEW vwCustomerAddressList
AS
SELECT CompanyName, ContactName, Address,
 City, Region, PostalCode, Country

FROM Customers

Later in the chapter, the special options WITH ENCRYPTION and WITH CHECK OPTION are discussed. These can be added to the basic SELECT syntax when defining a view.

What Views Are Good For

The primary use of views is to present data to users of an application. Views can be used in place of tables in your Access front-end application, whether you're using an Access database or an Access project. Views can also reference functions and other views.

Join Tables

A user can query a single view instead of having to learn complex join syntax and understand the structure of your database. As far as the user is concerned, the view looks just like a table. The following Transact-SQL statement selects all the data for the view shown in Listing 9.1, sorting the result set on the CompanyName column:

SELECT * from vwCustomerAddressList
ORDER BY CompanyName

In this case, the underlying view didn't contain any joins. But even if it had, the SELECT statement wouldn't have to change. The information about where the data in a view comes from and how it is pulled together from various tables is all encapsulated inside the view.

Customize Data

Tables can be customized and tailored to the needs of the users by way of views. For example, you could create a view for a salesperson that only displays her own orders, or you might create a view for the manager, who is only interested in seeing the total sales for each salesperson. Views give you a mechanism for presenting the same data to different users in different ways.

Restricting Columns and Rows

Columns and rows can be restricted, allowing users to focus on specific data that interests them. Unnecessary data is left out. This also helps network performance because less data is traveling over the wire to client applications. Using a view to filter data on the server is much more efficient than fetching an entire table and then applying a filter on the client. To restrict the rows in a view, you use the same kinds of criteria in a WHERE clause that you are already familiar with. To restrict the columns, you simply leave certain columns out of the SELECT clause. For example, the following view limits the result set by selecting only the employee first name, last name, and hire date columns from the Employees table, and it limits the rows returned to employees hired this year:

CREATE VIEW vwEmployeesHiredThisYear
AS
 SELECT LastName, FirstName, HireDate
 FROM Employees
 WHERE Year(HireDate) = Year(Getdate())

Preaggregate Data

One of the most powerful uses of views is for reporting. A view can be used to wrap up complex queries containing subqueries, outer joins, and aggregation. This simplifies access to the data because the underlying query does not have to be written on the client and then submitted each time a report is run.

You may already have discovered that you can often greatly speed up Access reports by first saving the results of a complex record source query into a temporary table and then basing the report on that table rather than on the query. Similar performance benefits can be gained with SQL Server by basing your reports on views. For example, the following view selects a customer's total orders and contains joins between the Customers table, the Orders table, and the Order Details table. When a user selects data from the view, only the result set is passed over the network—all of the joins and aggregations are performed on the server, not on the client.

CREATE VIEW vwCustomerOrders
AS 
SELECT dbo.Customers.CompanyName, 
 SUM(dbo.[Order Details].UnitPrice * 
 dbo.[Order Details].Quantity) AS Total
FROM dbo.Customers 
 INNER JOIN
 dbo.Orders 
 ON 
 dbo.Customers.CustomerID = dbo.Orders.CustomerID 
 INNER JOIN
 dbo.[Order Details] 
 ON 
 dbo.Orders.OrderID = dbo.[Order Details].OrderID
GROUP BY dbo.Customers.CompanyName

Rules for Creating and Using Views

Views are created inside individual databases, however, views can reference tables and views in other databases or even on other servers (if the view is defined using distributed queries). Here are some general guidelines for creating views:

• Names of views must follow the same rules for identifiers, discussed in Chapter 7, "Designing and Creating a SQL Server Database," and they must be unique for each owner in the database. However, your best bet is to make sure that all objects are owned by dbo so that there are no breaks in the ownership chain. Later in this chapter, views, security, and ownership chains are discussed.

• Views can be built on other views and can be nested up to 32 levels deep.

• To create a view, you must be granted permission to do so by the database owner, and you must have appropriate permissions on any tables or views referenced in the view definition.

Things You Can't Do in Views

Views are much more restricted in their capabilities than Access queries. Here's a list of the things you can't do in a view:

• Rules, defaults, and triggers cannot be associated with views. An exception to this is INSTEAD OF triggers, which are new to SQL Server 2000 and are discussed later in this chapter.

• ORDER BY, COMPUTE, and COMPUTE BY clauses or the INTO keyword can't be used in views. An exception to this is the ORDER BY clause, which is allowed only in conjunction with the TOP keyword. ORDER BY and TOP are covered later in this chapter.

• Full-text indexes can't be defined on views. However, other types of indexes can be defined on views in SQL Server 2000. (This feature is not available in SQL Server 7.0.)

• Views cannot be based on temporary tables.

• You cannot issue full-text queries against a view, although a view definition can include a full-text query if the query references a table that has been configured for full-text indexing.

• You can't pass parameters to views, although you'll learn later in the chapter how user-defined functions in SQL Server 2000 enable you to skirt this restriction.

Views and Security

Views are frequently used as security mechanisms to prevent users from having direct access to tables while still allowing them to work with data. All permissions can be removed from the underlying tables, and as long as the owner of the table is the same as the owner of the view, the user will be able to interact with the data based on the permissions granted to the view. Figure 9.1 shows the Permissions dialog box for vwCustomeraddressList. As long as both the view and the Customers table are owned by dbo, the permissions granted on the view will take precedence over permissions removed from the Customers table.

Figure 9.1
Setting permissions on a view.

Because views allow you to restrict the rows and columns that are available, using them as a security tool is very convenient. For example, you could create two views on an Employees table, one with a sensitive salary column included that only managers had permissions on, and a second view with that column omitted for everyone else.

Chain of Ownership Issues

One reason it's recommended that the dbo own all objects in a database is that problems arise when the chain of ownership is broken. This happens when the view and the underlying table do not have the same owner. When the chain of ownership is broken, you need to grant users the same permissions on the underlying tables as you want them to have on the view, which nullifies the effectiveness of views as security mechanisms. Another problem you'll encounter with Access projects (ADPs) is that if you want users to be able to update data using a view, you also have to grant them permissions on the underlying tables.

Using Views to Implement Column-Level or Row-Level Security

Although you can set column-level security in SQL Server, it is tricky to maintain because it's pretty well buried in the table properties. Row-level security must be defined at runtime because it's impossible to know what values a table will contain until then. A simple solution to the issue of implementing row-level and column-level security is to revoke all permissions to the underlying tables. Instead, define views with only selected columns to implement column-level security and define views with a WHERE clause to implement row-level security. Then grant appropriate permissions on the views for the users and roles you want to be able to access the data.

Hiding Underlying Table or Column Names

Views can also be used to hide the real column names or underlying schema of your tables. This can be implemented using the AS keyword to alias columns in your SELECT statements:

CREATE VIEW vwObscure
AS
SELECT CompanyName AS C, ContactName AS CN
FROM Customers

Anyone using the view would query it using the following syntax and would never know which actual tables or columns were being used:

SELECT C, CN
FROM vwObscure

A second technique is to use a column list:

CREATE VIEW vwObscure (C, CN)
AS
SELECT CompanyName, ContactName
FROM Customers

The following query will also work with either view:

SELECT * FROM vwObscure

Encrypting Views

The definition of a sensitive view can be encrypted to ensure that its definition cannot be obtained by anyone, including the owner of the view, by using the WITH ENCRYPTION option when the view is created. Listing 9.2 shows the syntax for creating an encrypted view.

Listing 9.2 Creating an Encrypted View

CREATE VIEW vwEncrypted
WITH ENCRYPTION
AS
 SELECT CompanyName AS C, ContactName AS CN

 FROM Customers AS CL

Once the view is created, its definition will be unreadable, as shown in Figure 9.2. Therefore, make sure that you save your view definition in a script file in case you ever need to modify it.

Figure 9.2
The definition of an encrypted view.