Microsoft Press microsoft sql server 2005 PHẦN 4 pps

C0662271X.fm Page 236 Friday, April 29, 2005 7:34 PM

237

Chapter 7

Implementing Views

A view is simply a SELECT statement that has a name and is stored in Microsoft SQL

Server. Views act as virtual tables to provide several benefits. A view gives developers

a standardized way to execute queries, enabling them to write certain common que￾ries once as views and then include the views in application code so that all applica￾tions use the same version of a query. A view can also provide a level of security by

giving users access to just a subset of data contained in the base tables that the view

is built over and can give users a more friendly, logical view of data in a database. In

addition, a view with indexes created on it can provide dramatic performance

improvements, especially for certain types of complex queries. Most views allow only

read operations on underlying data, but you can also create updateable views that let

users modify data via the view. This chapter shows you how to leverage the power and

flexibility of views by creating regular views, updateable views, and indexed views.

Exam objectives in this chapter:

■ Implement a view.

❑ Create an indexed view.

❑ Create an updateable view.

❑ Assign permissions to a role or schema for a view.

Lessons in this chapter:

■ Lesson 1: Creating a View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 240

■ Lesson 2: Modifying Data Through Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 245

■ Lesson 3: Creating an Indexed View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 248

Before You Begin

To complete the lessons in this chapter, you must have

■ SQL Server 2005 installed.

■ A copy of the AdventureWorks sample database installed in the instance.

C0762271X.fm Page 237 Friday, April 29, 2005 7:35 PM

238 Chapter 7 Implementing Views

Real World

Michael Hotek

A couple of years ago, I had a two-week project with a customer who was expe￾riencing performance issues. When I started looking into the database, I knew I

was in for a big challenge. There were tens of thousands of lines of code spread

among almost 2,000 stored procedures, functions, and triggers—along with

about 350 tables. What really stood out at first glance were the more than 800

views in the database.

Having a large number of views in a database isn’t necessarily a problem. But

having more than twice as many views as tables told me that either the tables

were poorly designed or the views were not being properly used. Unfortunately,

in this case, it was both—but that is a story for a different day.

As I investigated, I found views that did nothing more than select a handful of col￾umns from a single table by using a simple WHERE clause. After looking at about

the 50th view, I discovered that something wasn’t right. Cross-referencing back to

the views I already looked at, I found a duplicate. Then I found another and

another and another. In one instance, I found 23 views that all did the same thing.

It turns out that the developers were in a hurry to create applications and deploy

new features. At some point, one of the database administrators (DBAs) dictated

that all data access had to be through views because the DBA mistakenly

thought that a view gave a performance improvement. So several years later, the

company had hundreds of views embedded in the applications. And finding

anything was so difficult that developers simply created new views whenever

they needed anything, making a bad situation even worse.

Fortunately, the applications were not directly accessing tables or views; data

access was through stored procedures. So the first step in the process was to wade

through the stored procedure, function, and trigger code for references to dupli￾cate views. By removing all the duplicates, we could drop more than 400 views.

We then took the second step of eliminating anything that really shouldn’t have

been a view in the first place. We defined unnecessary views as views that

accessed only one table through a simple WHERE clause; views that imple￾mented things that did not belong in a view, such as a hard-coded list of states;

and views that contained simple logic that any developer should understand.

C0762271X.fm Page 238 Friday, April 29, 2005 7:35 PM

Before You Begin 239

The end result of this process was a database that contained only 34 views. The

only views that survived contained complex calculations or complex joins that

needed to be encapsulated either to ensure consistency or to avoid a significant

amount of effort in correctly constructing the query in the future.

The lesson learned by the developers was that SQL Server gives you a lot of tools

to accomplish a task. But just because you can do something doesn’t necessarily

mean that you should. Before creating an object in a database, you have to under￾stand how it will improve the application and be able to justify why creating the

object is the best approach.

C0762271X.fm Page 239 Friday, April 29, 2005 7:35 PM

240 Chapter 7 Implementing Views

Lesson 1: Creating a View

Certain SQL Server objects are necessary or generally recommended. For example,

you must have database tables to store data, and you should create certain indexes on

your tables to improve performance. However, you should create views only when

there is a clear advantage to having them. Views that don’t have demonstrated bene￾fits just take up space. Suppose that you need to return the name of a customer who

has a credit line in excess of $10,000. A view would provide no advantage in this case

because the SELECT statement to generate this result is simple and straightforward.

However, if you need to return the name of a customer with the primary address and

most recent payment, while keeping in the output all of the customers who have not

made a payment, creating a view is probably useful because generating this result

requires a combination of inner and outer joins to at least five different tables. In this

lesson, you see how to define a view over one or more tables. You also learn why it is

important to ensure that you have appropriate permissions assigned for the view and

any underlying tables the view is based on.

After this lesson, you will be able to:

■ Create a view.

■ Assign permissions to a role or schema for a view.

Estimated lesson time: 20 minutes

How to Create a View

You use the Transact-SQL CREATE VIEW command to create a view over one or more

tables. The syntax for the command follows:

CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ])]

[ WITH <view_attribute> [ ,...n ] ]

AS select_statement [ ; ]

[ WITH CHECK OPTION ]

<view_attribute> ::=

{

[ ENCRYPTION ]

[ SCHEMABINDING ]

[ VIEW_METADATA ] }

You begin by naming your view. As with all objects, a view must have a name that

meets the rules for identifiers.

C0762271X.fm Page 240 Friday, April 29, 2005 7:35 PM

Lesson 1: Creating a View 241

The command’s first WITH clause lets you apply three different options to the view:

ENCRYPTION, SCHEMABINDING, and VIEW_METADATA. ENCRYPTION specifies

that SQL Server should encrypt the definition of the view when it is stored in the data￾base. The definition of an encrypted view is not visible to anyone, including a member

of the sysadmin fixed server role. So when you encrypt a view, you must ensure that you

keep the original source code somewhere because you cannot decrypt the definition.

When you specify the SCHEMABINDING option, you cannot drop any tables, views,

or functions referenced by the view without first dropping the view.

BEST PRACTICES Schema binding trick

An old trick that many DBAs use in a production environment is to create a view for each table that

selects all columns in the table and specifies the SCHEMABINDING option. These views are never

used with any application or by any user. The only purpose of the views is to prevent a DBA from

accidentally dropping a table or a column within a table. This trick does not prevent a DBA from

purposefully dropping a table because the DBA can also drop the view and then drop the table.

But dropping an object on purpose that should not be dropped is a security issue.

The VIEW_METADATA option returns metadata about a view to client-side data

access libraries.

You use the command’s AS clause to specify the SELECT statement that defines the

view. The SELECT statement can be of any complexity as long as the query is valid and

can reference tables, views, user-defined functions (UDFs), and system functions. The

only restrictions are that the view’s SELECT statement CANNOT do the following:

■ Use the COMPUTE or COMPUTE BY clause

■ Use the INTO keyword

■ Use the OPTION clause

■ Reference a temporary table or table variable

■ Use the ORDER BY clause unless it also specifies the TOP operator

The command’s last option, WITH CHECK OPTION, is something you use to create an

updateable view. Lesson 2, “Modifying Data Through Views,” covers this option.

After you have created a view, you can use it just like any table in a database. However,

a view does NOT contain any data. A view is simply a SELECT statement that has a

name associated with it. So when a view is referenced in a SELECT statement, the

query optimizer substitutes the reference with the definition of the view in the

SELECT statement before generating an execution plan.

C0762271X.fm Page 241 Friday, April 29, 2005 7:35 PM

242 Chapter 7 Implementing Views

For example, consider the following code:

CREATE VIEW v_CustomerAddress

AS

SELECT a.CustomerID, a.CustomerName, c.AddressLine1, c.AddressLine2, c.AddressLine3,

c.City, d.StateProvince, c.PostalCode, e.Country

FROM dbo.Customer a INNER JOIN dbo.CustomerToCustomerAddress b ON a.CustomerID =

b.CustomerID

INNER JOIN dbo.CustomerAddress c ON b.CustomerAddressID = c.CustomerAddressID

INNER JOIN dbo.StateProvince d ON c.StateProvinceID = d.StateProvinceID

INNER JOIN dbo.Country e ON c.CountryID = e.CountryID;

SELECT a.CustomerName, b.CreditLine FROM v_CustomerAddress a INNER JOIN dbo.Customer b

ON a.CustomerID = b.CustomerID;

The optimizer would locate the reference to the v_CustomerAddress view and substi￾tute the view definition, rewriting the submitted query into a query similar to the

following:

SELECT a.CustomerName, f.CreditLine

FROM dbo.Customer a INNER JOIN dbo.CustomerToCustomerAddress b ON a.CustomerID =

b.CustomerID

INNER JOIN dbo.CustomerAddress c ON b.CustomerAddressID = c.CustomerAddressID

INNER JOIN dbo.StateProvince d ON c.StateProvinceID = d.StateProvinceID

INNER JOIN dbo.Country e ON c.CountryID = e.CountryID

INNER JOIN dbo.Customer f ON a.CustomerID = f.CustomerID;

Understanding Ownership Chains

Because a view references other objects, there is the potential for permission issues.

Consider the objects and object owners that the diagram in Figure 7-1 shows.

Figure 7-1 Defining an ownership chain

Let’s say that UserA grants SELECT permission to UserD on the v_CustomerAddress

view. Even though UserD has permission to execute a SELECT statement against the

view, this user would receive an error when he attempts to use the view because

the view is defined against the Customer and CustomerAddress tables, which are owned

by a different user than either UserA or UserD. When the ownership across a chain of

V_CustomerAddress

owned by UserA

CustomerAddress

owned by UserC

Customer

owned by UserB

C0762271X.fm Page 242 Friday, April 29, 2005 7:35 PM

Lesson 1: Creating a View 243

dependent objects causes an error due to insufficient permissions, you have a broken

ownership chain.

For UserD to be able to execute a SELECT statement against the v_CustomerAddress

view, the following has to occur:

■ UserA grants UserD SELECT permission to the view.

■ UserB grants UserD SELECT permission to dbo.Customer.

■ UserC grants UserD SELECT permission to dbo.CustomerAddress.

MORE INFO Ownership chains

For more information about ownership chains, see the SQL Server 2005 Books Online topic “Owner￾ship Chains.” SQL Server 2005 Books Online is installed as part of SQL Server 2005. Updates for SQL

Server 2005 Books Online are available for download at www.microsoft.com/technet/prodtechnol/sql/

2005/downloads/books.mspx.

Quick Check

■ What are the restrictions on the SELECT statement within a view?

Quick Check Answer

■ COMPUTE or COMPUTE BY clauses are not allowed. You cannot use the

INTO keyword or OPTION clause. Temporary tables and table variables

cannot be referenced. An ORDER BY clause cannot be specified unless the

TOP operator is also used.

PRACTICE Create a View

In this practice, you use the database that contains the tables you created in Chapter 3,

“Creating Tables, Constraints, and User-Defined Types,” to create a view to return cus￾tomer information for customers who live in Canada.

1. Launch SQL Server Management Studio (SSMS), connect to your instance, open

a new query window, and change context to the database containing the tables

you created in Chapter 3.

2. Create a view to return information for customers who live in Canada by execut￾ing the following statement:

CREATE VIEW v_CanadaCustomerAddress

AS

SELECT a.CustomerID, a.CustomerName, c.AddressLine1, c.AddressLine2, c.AddressLine3,

c.City, d.StateProvince, c.PostalCode, e.Country

C0762271X.fm Page 243 Friday, April 29, 2005 7:35 PM

244 Chapter 7 Implementing Views

FROM dbo.Customer a INNER JOIN dbo.CustomerToCustomerAddress b ON a.CustomerID =

b.CustomerID

INNER JOIN dbo.CustomerAddress c ON b.CustomerAddressID = c.CustomerAddressID

INNER JOIN dbo.StateProvince d ON c.StateProvinceID = d.StateProvinceID

INNER JOIN dbo.Country e ON c.CountryID = e.CountryID

WHERE e.Country = 'Canada'

AND PrimaryAddressFlag = 1;

3. Construct a SELECT statement to verify that the view returns only customers

from Canada.

Lesson Summary

■ A view is simply a SELECT statement that you name and store in SQL Server as

a sort of “virtual table” that lets you give users access to just a subset of data and

that lets you improve performance, especially for complex queries.

■ After it’s defined, the view can be referenced in a SELECT statement just like a

table, although it does not contain any data.

■ When granting permissions to a view, you must pay careful attention to the own￾ership chain to ensure that the user has access to the view as well as all underly￾ing objects that the view is built on.

Lesson Review

The following questions are intended to reinforce key information presented in this

lesson. The questions are also available on the companion CD if you prefer to review

them in electronic form.

NOTE Answers

Answers to these questions and explanations of why each answer choice is right or wrong are

located in the “Answers” section at the end of the book.

1. Which of the following options can prevent a table from being dropped?

A. CHECK OPTION

B. SCHEMABINDING

C. UNION

D. QUOTED_IDENTIFIER

C0762271X.fm Page 244 Friday, April 29, 2005 7:35 PM