Chapter 7. Working with Views

O BJECTIVES

This chapter meets the Views objectives for Microsoft's Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition exam. These objectives include:

Create and alter database objects. Objects include views.

• Specify schema binding and encryption for stored procedures, triggers, user -defined functions, and views.

• The purpose of this objective is to test your knowledge of how to create and alter views in SQL Server 2000. You have to know how and when to alter views and what options to change. You also need to know when to create views in a given situation. This chapter covers the different considerations that arise when creating and altering views.

Manage data manipulation by using views.

• This objective deals with data modification on a base table via a view. Even if you have no knowledge of views, you probably guessed that they can be used to view information from the database. However, not only can they act as data retrieval centers, but they can also be used to modify data residing on underlying base tables, and that's what this objective deals with.

Alter database objects to support partitioned views.

• Design a partitioning strategy.

• Design and create views.

• This objective looks at the implementation of partitioned views in SQL Server 2000. Partitioned views include distributed partitioned and locally partitioned data, which are covered near the end of the chapter.

Control data access by using views.

• Apply ownership chains.

• Use programming logic and objects. Considerations include implementing row-level security and restricting direct access to tables.

• Troubleshoot and optimize programming objects. Objects include views.

• This objective teaches you how to access data by using a view. This objective includes creating a view in such a way so that users actually benefit from its existence. Security mechanisms can be obtained by controlling data access, which is done by granting users access to a view but not granting them direct access to underlying tables. Many of the restrictions that apply to views deal with data manipulation, and these restrictions can prevent your views from operating as expected.

O UTLINE

Understanding Views

View Benefits

View Creation Guidelines

Create and Alter Views

Creating Views

Altering Views

Modifying Data Through Views

Inserting Rows Through Views

Deleting Rows Through Views

Updating Rows Through Views

Alter Database Objects to Support Partitioned Views

Partitioned View Considerations and Guidelines

Creating Partitioned Views

Control Data Access by Using Views

Granting Permissions and Ownership Chains

Dealing with Broken Ownership Chains

Apply Your Knowledge

Exercises

Review Questions

Exam Questions

Answers to Review Questions

Answers to Exam Questions

S TUDY S TRATEGIES

• This chapter contains pure objective- related information, making the entire chapter important, but focuses on the main elements of the view statement, including the WITH ENCRYPTION and WITH CHECK options. Also, make sure you have a good understanding of ownership chains.

• Indexed views are without doubt going to show up on the exam. However, they are examined in Chapter 3, "Physical Database Design," and Chapter 10, "Boosting Performance with Indexes." This chapter mentions view components that affect the creation of index views.

• Test out several types of views on a SQL Server. The more practical work you do, the more likely it is that you will remember the facts.