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.