Apply Your Knowledge

Exercises

Exercise 7.1 Creating a Filtered View

This exercise demonstrates how to create two different views. These views implement vertical and horizontal filters. Many of the views that you will implement on databases will utilize some type of filtering.

Estimated Time: 10 minutes.

1. Open the Query Analyzer from the Start menu.

2. To create a new view using vertical filtering, select the Pubs database and enter the following query:

    CREATE VIEW SelectedColumns AS   SELECT Au_ID, State, au_FName     FROM Authors 

3. Step 2 creates a view that includes only the State and Au_fname columns . To create a view that includes only rows where the first name has the letter J as the first letter, enter the following code:

    CREATE VIEW SelectedRows AS   SELECT *     FROM Authors     WHERE au_fname LIKE 'J%' 

4. Test your view by running a SELECT against the view. Your SELECT statement should return all columns and rows so that you can see how much data is now available through the view. If you have not already done so, you should also SELECT the entire Authors table, in order to see how much data is in the base table.

Exercise 7.2 Renaming and Dropping Views

This example demonstrates renaming and dropping views using sp_rename and DROP VIEW . Any job that is done can either be done better or becomes obsolete.

This exercise shows you how to change an established view's name, as well as remove an obsolete view.

Estimated Time: 10 minutes.

1. Open the SQL Server Query Analyzer by selecting it from the Start menu. Connect to your server, and change to the Pubs database.

2. Examine the contents of the Titles table by selecting the entire contents.

3. You first have to create a view. To do this, enter the following code:

    CREATE VIEW ExampleView AS   SELECT *     FROM titles     WHERE type = 'business' 

4. Test your new view by selecting against it.

5. To change the previously created view's name to TestView , enter the following code:

    sp_rename 'ExampleView', 'TestView', OBJECT 

6. Examine what views exist in the Pubs database using the following statement:

    SELECT name   FROM sysobjects   WHERE type='V' 

7. To delete TestView (formerly ExampleView ), enter the following query:

    DROP VIEW TestView 

8. Re-examine the views that exist in the Pubs database using the same command that you used in Step 6.

Exercise 7.3 Creating a Partitioned View

This exercise demonstrates creating a partitioned view from member tables. You start by creating two base tables, and then you join them through the creation of a view.

Estimated Time: 15 minutes.

1. Open the SQL Server Query Analyzer by selecting it from the Start menu. Connect to your server, and change to the Pubs database.

2. To begin, you will need to create two tables that act as members the tables that will be joined together. To do this, follow this code:

    CREATE TABLE member1  (Identification INT      PRIMARY KEY      CHECK (Identification BETWEEN 1 and 20),    Name varchar(20)) CREATE TABLE member2 (Identification INT      PRIMARY KEY      CHECK (Identification BETWEEN 21 and 40),    Name varchar(20)) 

3. Now INSERT some example data into the member tables you created in Step 1. To do this, execute the following queries:

    INSERT member1   (Identification, Name)   VALUES ('7', 'Abraham') INSERT member1   (Identification, Name)   VALUES ('18', 'Jauna') INSERT member2   (Identification, Name)   VALUES ('23', 'John') INSERT member2   (Identification, Name)   VALUES ('36', 'Dennis') 

4. To create a view that combines these two member tables, execute the following:

    CREATE VIEW AllProducts AS   SELECT *     FROM member1     UNION ALL SELECT *     FROM member2 

5. Test your new view by running a SELECT against it. You should have a complete listing of all four records.

Exercise 7.4 Accessing a View

This exercise shows you how to access a view by displaying its definition and dependencies. Being able to research a view's definition and dependencies is useful when trying to find out why a view may not be working.

Estimated Time: 10 minutes.

1. Open the SQL Server Query Analyzer by selecting it from the Start menu. Log in and select the Pubs database.

2. Create a new view by executing the following query:

    CREATE VIEW BusinessTitleView AS   SELECT *     FROM titles     WHERE type = 'business' 

3. To access the definition of this view, execute the following:

    EXEC sp_helptext TitleView 

4. To display any view dependencies, use the following query:

    sp_depends TitleView 

Review Questions

1:	Describe in relative terms how much data a view is capable of containing and the storage requirements that may be necessary for a view.
A1:	A view refers to data that is found in a table. The view does not actually contain any data. Although it takes up some space in the sysobjects and syscomments tables, in relative terms, the storage requirements are very small.
2:	How does the CHECK constraint help in the implementation of partitioned views?
A2:	The CHECK constraint is used to identify which portion of the data can be found in which table. This published division speeds up searches for data through the view.
3:	What is the advantage of creating a view that is based on a view, rather than on a table?
A3:	If you base a view on an existing view, you can further restrict, filter, or merge data that is currently defined through the existing view or views.
4:	When would you want to use a partitioned view?
A4:	Generally , you would want to use a partitioned view when you want to gather information from many tables and make them appear as a single table. You may need to do this if you have split your data across several servers. This is done in some circumstances to improve data access performance.
5:	Generally speaking, when would you want to implement a view into your database?
A5:	You should implement a view in your database when you would like to disallow redundant data, enforce security, or export data easily.
6:	Which objects are not allowed to be associated with a view?
A6:	Defaults, triggers and rules are not allowed to be associated with a view. INSTEAD OF triggers may be defined on a view. Even though a view is defined by a SELECT statement, it is not allowed to use the COMPUTE BY or SELECT INTO clauses. The ORDER BY clause may only be used in conjunction with the TOP clause. Finally, views cannot be based on temporary tables.
7:	When modifying data through a view, what are you actually modifying?
A7:	When data is being modified, deleted, and updated through a view, the data definition in the view does not change; the data modification is aimed at the underlying referenced tables associated with the view.

Exam Questions

1:	Which of the following statements best describes a view? A table stored on physical memory whose contents are derived from a query A definition of what data should be extracted from a table or tables A normal table except that it can be partitioned The way a table is shown is SQL Server
A1:	B. A view does not contain data as a table does. It is often accessed with methods that are similar to those you use with a table, but the view is a definition or description of data that should be retrieved from a table or tables. For more information about view basics, see the section "Understanding Views."
2:	Tom is interested in examining ways to improve his data access performance through view implementations . He is currently managing a database that is replicated between five servers (for load balancing) and contains customer information for six sales divisions. The Customer table currently contains a consolidated list of 100,000 customer records. What could Tom try to implement to improve server performance? Aggregated views Partitioned views Complex views Full outer views
A2:	B. Partitioned views, and specifically distributed partitioned views, may improve the data access for Tom's database. Because a large number of rows could be separated by sales division per server (with one server maintaining information for two divisions), his database tables may be ideal candidates for distributed partitioned views. For more information about partitioned views, see the section "Creating Partitioned Views."
3:	Which of the following is a benefit provided by a view? Choose all that apply. Minimize unneeded data. Security is enforced using views. Data is easily transformable. Data can be easily exported using tools, such as BCP.
A3:	A, B, D. The main benefits of views are that they disallow unneeded data, improve security enforcement, ease data exporting and generally make querying easier. For more information about the benefits for views, see the section "View Benefits."
4:	Henry is the system administrator of the databases at his company. Users who access the database often query the Sales table, which has more than 50,000 rows and 15 columns. Most of the columns retrieved by users are unnecessarily retrieved. Henry needs to disallow the retrieval of the unnecessary columns and allow users to retrieve only the data that is needed. He decides to use a view. Which of the following tools can Henry use to create views? Choose all that apply. Query Analyzer SQL Server Enterprise Manager View Edit Wizard (VEW) None of the above
A4:	A, B. Both the Query Analyzer and the SQL Server Enterprise Manager can create views. For more information about creating views, see the section "Creating Views."
5:	John works in a major telecom company. Each table in his central database has numerous columns and he wants to create a view by joining data from four tables. He does this, so that he can enable users to retrieve data efficiently from a view rather than retrieving the different columns manually by using JOIN statements. What is the maximum number of columns that can be joined by the view John is creating? 250 1024 It depends on the number of tables being referenced An unlimited amount
A5:	B. A total of 1024 columns can be referenced in a view definition. For more information about what a view is and some of its restrictions, see the section "Understanding Views."
6:	Jauna works for a trading company and has a massive amount of data held in databases that are found on multiple servers. This was done because they thought that putting the tables on multiple servers would ensure more efficient data access. Which of the following would you use to gather all this data into a single unit? Indexed view Replication view Distributed partitioned view Extended partitioned view
A6:	C. A distributed partitioned view collects data from two or more instances of SQL Server, hence the name "distributed." Essentially, distributed partitioned views are used when you want to spread processing out over a group of servers, but want the data to be treated as a single unit. For more information about partitioned views, see the section "Alter Database Objects to Support Partitioned Views."
7:	Your company keeps track of all sales on a monthly basis. The company requires that the sales manager enter all sales that are greater than $20,000 into a database. They do not want the sales manager to be able to enter values that are less than $20,000 in the database. You are in charge of creating a view for the Sales table that includes the following columns: amount , date , and sales_person . You would like this view to enable the sales manager to enter the valid sales figures. Which of the following describes the view needed? CREATE VIEW ApprovedSales AS SELECT date, Sales_Person, Amount FROM Sales WHERE Amount<=20000 CREATE VIEW ApprovedSales AS SELECT Number, SalesPerson, Amount FROM Sales HAVING Amount<=20000 CREATE VIEW ApprovedSales AS SELECT date, SalesPerson, Amount FROM Sales WHERE Amount<=20000 WITH CHECK OPTION CREATE VIEW ApprovedSales AS SELECT Number, SalesPerson, Amount FROM Sales HAVING Amount<=20000 WITH CHECK OPTION
A7:	C. The WITH CHECK OPTION forces all data modification statements executed against the view to adhere to the criteria set within the view definition. When a row is modified through a view, the WITH CHECK OPTION guarantees that the data remains visible through the view after the modification has been committed. For more information about restrictions on data modifications, see the section "Updating Rows Through Views."
8:	Chris works as a SQL Server database developer in the technical department of a nationwide bookstore. Chris needs to create a view by filtering data for the 50 customers having greatest payments due from the Customers table. Which strategy would be the best for him to use? Retrieve slices of the Customers table horizontally Retrieve slices of the Customers table vertically Both A and B Use the filtered view, new to SQL Server 2000
A8:	A. Horizontally filtering the table will work because he can use a WHERE clause to query for the 50 customers having the greatest due payments. For more information about filtering data, see the section "Selecting Specific Columns and Rows."
9:	You are working on your new view called SummaryData , which uses complex aggregates and joins for a company database. The view SummaryData you have just created took you a good amount of time and effort. You don't want other users to see how you created your view. What is the best and easiest way to implement this type of security? Create it with the WITH ENCRYPTION option. Create it with the WITH CHECK option. Create it in Enterprise Manager and select Hidden Definition Create it with the HIDDEN DEFINITION option.
A9:	A. The WITH ENCRYPTION option protects the definition of your view. When you specify this, you encrypt the definition of your view because you may not want users to display it. Encrypting using WITH ENCRYPTION disallows users from seeing your view, whether they use sp_helptext or view it through the Enterprise Manager. For more information about encrypting your views, see the section "Accessing Views."
10:	You work with several developers who regularly CREATE , DROP , and ALTER views within your database. These actions have been causing many views to malfunction when dependent objects are altered . What option can you use to prevent some of your problems? CREATE INDEXED VIEW The WITH SCHEMABINDING option The WITH ENCRYPTION option The WITH CHECK option
A10:	B. WITH SCHEMABINDING specifies that the view be bound to the schema. When this is done, objects with dependent objects cannot be modified. For more information about options that are available when creating views, see the section "Creating Views."
11:	Jeffery has just created a view to enforce data security on the Sales table. You are an assistant developer and are receiving complaints relating to the view Jeffery created some time ago. You assume that he made mistakes when creating the view and so in an attempt to do some cleanup, you try to access the view definition. When trying to access the definition of the view, you find that you are not able to. What is the most common reason to not be able to access a view definition? Jeffery specified the Encrypt On View option when he created the view. Jeffery specified SECURE DEFINITION when he created the view. Jeffery did not repair the view since the first time it was created three months ago. Jeffery used WITH ENCYPTION when he created the view.
A11:	D. When Jeffery created the view for security reasons, he used the WITH ENCRYPTION option to secure his definition. Any user who tries to view the view definition will see encrypted lines of code, impossible to understand. For more information about options that are available when creating views, see the section "Creating Views."
12:	You are troubleshooting an access problem with a view. Mary is having problems using Bob's new view, Bob.BobView1 . Examine the following exhibits to figure out what is causing Mary's access problem. Exhibit 1 contains the tables that are in the database, Exhibit 2 contains the definitions of the views that are in the database, and Exhibit 3 are the permissions that were assigned to each object. After examining the exhibits, choose the best answer to explain Mary's access problem. Example Exhibit 1: Create Tables CREATE TABLE dbo.DBOTable1 (id int, name varchar(20)) CREATE TABLE bob.BobTable1 (id int, description varchar(20)) CREATE TABLE jane.JaneTable1 (id int, address varchar(20)) GO INSERT INTO dbo.DBOTable1 VALUES (1, 'Buddy') INSERT INTO Jane.JaneTable1 VALUES (1, '123 Some Street') INSERT INTO Bob.BobTable1 VALUES (1, 'What was his name?') Example Exhibit 2: Create Views CREATE VIEW Jane.JaneView1 AS SELECT dbo.DBOTable1.id, name, address FROM dbo.DBOTable1 INNER JOIN Jane.JaneTable1 ON dbo.DBOTable1.id = Jane.JaneTable1.id GO CREATE VIEW Jane.JaneView2 WITH ENCRYPTION AS SELECT id, description FROM Bob.BobTable1 GO CREATE VIEW Bob.BobView1 AS SELECT Jane.JaneView1.id, name, address, description FROM Jane.JaneView1 INNER JOIN Jane.JaneView2 ON Jane.JaneView1.id = Jane.JaneView2.id Example Exhibit 3: Assign Permissions REVOKE all ON dbo.DBOTable1 TO public REVOKE all ON Jane.JaneTable1 TO public REVOKE all ON Bob.BobTable1 TO public GRANT SELECT ON Bob.BobView1 TO Jane, Mary GRANT SELECT ON Jane.JaneView1 TO Mary, Bob, dbo GRANT SELECT ON dbo.DBOTable1 TO Jane GRANT SELECT ON Bob.BobTable1 TO Mary, Jane GRANT SELECT ON Jane.JaneTable1 TO Bob, dbo GRANT SELECT ON Jane.JaneView2 TO Mary When you create a view using WITH ENCRYPTION , you must use GRANT WITH DECRYPTION . One GRANT is missing for Mary. Two GRANT s are missing for Mary. There is nothing wrong with the code in the exhibits; the problem is likely with Mary's network connection.
A12:	B. Although Mary has not been granted the SELECT permission to DBOTable1 or JaneTable1 , she requires only a GRANT for SELECT on DBOTable1 . She will be automatically allowed to use JaneTable1 because it will be through Jane's view. For more information about permissions and ownership chains, see the section "Control Data Access by Using Views."
13:	Mary has created a view that is going to be used to create an employee phone list. She would like to sort the new view by the employees ' last names . She has tried to create the view using the ORDER BY clause, but it always fails. What can Mary do to create her view? Order the base table using a clustered index, then the required data will already be in the correct order. Use the TOP clause rather than the ORDER BY clause. Use the TOP clause in addition to the ORDER BY clause. This clause cannot be used.
A13:	C. If you need to use the ORDER BY clause, then you also have to specify the TOP clause, such as TOP 100 PERCENT . For more information about restrictions on creating views, see the section "View Creation Guidelines."

Suggested Readings and Resources

1. Inside SQL Server 2000 Kalen Delaney (www.insidesqlserver.com)

   This is not a beginner book, but it fills in many of the gaps left out of the SQL Server Books Online documentation. Explains fully how SQL Server stores and processes data internally.

2. SQL Server 2000 Books Online

   • SQL Server Architecture: Database Architecture: Logical Database Components: SQL Views. This contains basic information about the creation and definition of views.

   • Creating and Maintaining Databases: Views: Creating a View: Creating a Partitioned View. This document introduces you to partitioned views.

   • Administering SQL Server: Managing Security: Managing Permissions: Using Ownership Chains. These documents cover how ownership chains work and how to manage security with them.