Web Hosting Java Blog

Chapter 7 WORKING WITH VIEWS 425 tinue to be visible through the view. User View Member Tables User Access View of Instances of SQL Server The next sections examine two more major areas of views. Before attempting to deal with partitioned views, you should be comfortable with the topics covered thus far. If not, review them one more time. The other area that is covered is using views to control data access and ownership chains. ALTER DATABASE OBJECTS TO SUPPORT PARTITIONED VIEWS . Alter database objects to support partitioned views. A partitioned view combines horizontally partitioned data from member tables across one or more servers (distributed partitioned views). A member table can be thought of as a segment from a larger table. There are two types of partitioned views in SQL Server: a local partitioned view and a distributed partitioned view. A local partitioned view is a partitioned view where all member tables reside on the local instance of SQL Server. Distributed partitioned views are new to SQL Server 2000 and are a bit more advanced than local partitioned views. The key difference between a distributed partitioned view and a local partitioned view is that in a local partitioned view, the data is collected from a single server alone. In contrast, a distributed partitioned view collects data from two or NOTE Defining Union All UNION ALL specifies that multiple queries are to be combined into a single resultset. The ALL argument specifies that all rows be incorporated, even duplicate value rows. If ALL isn t specified, duplicate values are removed.

For high quality java hosting services please check tomcat web hosting website.

424 Part I EXAM PREPARATION Updates, and Deletes, and how these are accomplished through the use of a view. You have seen what can be done, and what cannot be done because of the restrictions that are placed on data modifications through a view. Some of these restrictions are annoying, but most help you develop applications that maintain a higher quality of data. NOTE Server Federations With a distributed partitioned view, the data resides on several servers that work together to maintain and display the data. These servers may be called federated database servers. This term is used to refer to these servers that share the data-hosting job, but are independently administered. FIGURE 7.14 Updating data through a view. Coming to Terms with Views A view offers several benefits to both a database programmer and a database user. One benefit is ease of use, which comes from the view definition. The view definition may be based on a complex SELECT statement, and the view reduces the definition to a virtual table or record set. The created record set can be used in many areas where normal tables are. When working with a view definition, use standard commands that would be used with a table: CREATE, to create a view; ALTER, to change the view s definition; and DROP, to delete the view from the database. These commands are similar to the table commands of the same name, and serve the same function. The biggest difference is with the CREATE command, which references columns in a table, rather than storing the definition of each column. Because the view is based on the SELECT statement, you saw that options that can enhance SELECT statements can also enhance views. These options include aggregates, joins, and views based on views. The last section that you examined was on data manipulation through a view. These included inserts, updates, and deletes. Performing these tasks is similar to working with a table, but there are some restrictions on what can be done. You can increase these restrictions by using the WITH CHECK OPTION when creating your view. This enforces a requirement that all data modification will con

For reliable and cheap web hosting services please check cheap web hosting website.

Chapter 7 WORKING WITH VIEWS 423 1. Open the Query Analyzer by selecting it from the Start menu. REVIEW BREAK 2. INSERT a record into the view: INSERT INTO EmployeeView (emp_id, fname, lname, job_lvl) VALUES ( ARP21349M , Frandos , Fung , 10) 3. Assume that you mistook Frandos s last name with another name. To UPDATE the lname column on this record to his real last name, Thatch, run the following query: UPDATE EmployeeView SET lname = Thatch WHERE emp_id = ARP21349M 4. To see the updated record, run the following code. This process is shown in Figure 7.14. SELECT * FROM EmployeeView WHERE emp_id = ARP21349M With this discussion of the UPDATE statement, you have now examined all the different options for data modifications: Inserts,

For high quality jboss hosting services please check jboss web hosting website.

422 Part I EXAM PREPARATION re-create it. The next section leads you through the requirements for updating data through a view. FIGURE 7.13 Deleting data via a view. Updating Rows Through Views You can use an UPDATE statement to change one or more rows that a view references. Any changes that are undertaken through the view are actually applied to the underlying table in which the view is defined. The restrictions that applied to INSERT statements used with views also apply to UPDATE statements. These restrictions include the following: UPDATE statements must modify only one of the underlying tables at a time. If you want to UPDATE data that resides in more than one table, you have to perform the UPDATE in two or more statements. Updated data must conform to the view definition when WITH CHECK is specified on the view. Updated data cannot create FOREIGN KEY constraint violations. When updating a view, you may want to use the WITH CHECK OPTION to force all data modifications executed to be constrained by the view definition. This is important for the same reasons that were important when dealing with inserts: it reduces user confusion with disappearing data, and it enables users to always confirm the accuracy of their data UPDATE. To UPDATE a record, follow Step by Step 7.7. STEP BY STEP 7.7 Updating Data Through a View

For reliable and cheap web hosting services please check javaweb hosting website.

Chapter 7 WORKING WITH VIEWS 421 1. Open the Query Analyzer by selecting it from the Start menu. 2. You will DELETE data through the view you created in the EmployeeView example. To avoid deleting a row that will conflict with the WHERE clause condition, you need to see the definition of the view by running the following: SP_Helptext EmployeeView 3. Now, DELETE the row you added by running the following code: DELETE EmployeeView WHERE emp_id = AEE21349M 4. To make sure the record was deleted, try to query it using the following code. This process is shown in Figure 7.13. SELECT * FROM EmployeeView WHERE emp_id = AEE21349M You will often want to UPDATE data, rather than DELETE it and

If you looking for unlimited one inclusive web hosting plan please check cheap web hosting website.

420 Part I EXAM PREPARATION DELETE FROM TitleAuthors WHERE au_id = 213-46-8915 GO Server: Msg 4405, Level 16, State 1, Line 1 View or function TitleAuthors is not updatable because the modification affects multiple base tables. Another requirement for deleting data through a view is that the data specified in the DELETE statement must actually be visible in the view. For example, the following shows the creation of a view, and an attempted data deletion using state as part of the deletion criteria while it is not part of the view. This fails with the specified error. CREATE VIEW AuthorsInCA AS SELECT au_id, au_lname, au_fname FROM authors WHERE state = CA GO DELETE FROM AuthorsInCA WHERE au_lname = Burns and state .= CA GO Server: Msg 207, Level 16, State 3, Line 1 Invalid column name state . If the view was created with the state column visible, then the DELETE statement would have succeeded. All components of the DELETE statement must be defined in the view. The last requirement for deletion of data through a view is that the deletion of the record may not cause any foreign key violations. This type of deletion would not have been allowed outside the view, so it is not allowed through the view either. For example, in the Pubs database, you are not allowed to DELETE an author record if that author is assigned to a blog, because the FK_Constraints are declared on the TitleAuthor table with the Authors table. To DELETE a row through a view, go through the Step by Step 7.6. STEP BY STEP 7.6 Deleting a Row via a View

For high quality website hosting services please check tomcat web hosting website.

420 Part I EXAM PREPARATION DELETE FROM TitleAuthors WHERE au_id = 213-46-8915 GO Server: Msg 4405, Level 16, State 1, Line 1 View or function TitleAuthors is not updatable because the modification affects multiple base tables. Another requirement for deleting data through a view is that the data specified in the DELETE statement must actually be visible in the view. For example, the following shows the creation of a view, and an attempted data deletion using state as part of the deletion criteria while it is not part of the view. This fails with the specified error. CREATE VIEW AuthorsInCA AS SELECT au_id, au_lname, au_fname FROM authors WHERE state = CA GO DELETE FROM AuthorsInCA WHERE au_lname = Burns and state .= CA GO Server: Msg 207, Level 16, State 3, Line 1 Invalid column name state . If the view was created with the state column visible, then the DELETE statement would have succeeded. All components of the DELETE statement must be defined in the view. The last requirement for deletion of data through a view is that the deletion of the record may not cause any foreign key violations. This type of deletion would not have been allowed outside the view, so it is not allowed through the view either. For example, in the Pubs database, you are not allowed to DELETE an author record if that author is assigned to a blog, because the FK_Constraints are declared on the TitleAuthor table with the Authors table. To DELETE a row through a view, go through the Step by Step 7.6. STEP BY STEP 7.6 Deleting a Row via a View

For reliable and cheap web hosting services please check cheap web hosting website.

Chapter 7 WORKING WITH VIEWS 419 John , Mathew , 10) 4. To display the view with the updated record, enter and run the following: SELECT * FROM EmployeeView WHERE emp_id = AEE21349M 5. To see that the record has been added to the base table, Employee, run the following. The results of both of these SELECT statements are shown in Figure 7.12: SELECT * FROM Employee WHERE emp_id = AEE21349M Now that you have seen how to put data into a table through a view, you should also know how to get the data back out of the database through a view. FIGURE 7.12 Inserting data through a view. Deleting Rows Through Views In the same way data can be retrieved and inserted through a view, it can also be removed. Rows can be removed through a view in almost the same fashion that they can be inserted. In fact, deleting rows from a view is as easy as knowing how to DELETE rows from a regular table. The biggest rule that can never be broken when you DELETE a row through a view is that the affected row can reference only one table. In other words, the view can be based on only one table. If the view referred to or was based on more than one table, then every row would reference more than one table and could not be deleted. If you attempt to DELETE a row that is based on more than one table, then you receive an error message. Here is an attempted deletion and subsequent error message:

If you looking for unlimited one inclusive web hosting plan please check cheap web hosting website.

418 Part I EXAM PREPARATION STEP BY STEP 7.5 Adding Data Through a View 1. Open the Query Analyzer by selecting it from the Start menu. 2. You are going to add data through EmployeeView, which was the view you created in Step by Step 7.4. To avoid adding a row that will conflict with the WHERE clause condition, you need to see the definition of the view. Use sp_helptext to display the definition of the view, as shown in Figure 7.11: Sp_helptext EmployeeView FIGURE 7.11 The definition of EmployeeView. 3. Now, INSERT a record into the base table, making sure the new record does not conflict with the condition specified in the WHERE clause of the view definition: INSERT INTO EmployeeView (emp_id, fname, lname, job_lvl) VALUES ( AEE21349M ,

For high quality website hosting services please check java web hosting website.

Chapter 7 WORKING WITH VIEWS 417 that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint. The statement has been terminated. Because this INSERT results in data that was not visible through the view, it is not allowed. This may be advantageous for some databases if you want to restrict what values are inserted into the view. In this particular case, it prevents the addition of both Authors and Titles, because you can t use a single statement to INSERT into both tables, and either INSERT creates data that is not visible through the view. Data updates, other than inserts, are still allowed, as long as the resulting data is still visible through the view. Enabling users to add data to a view that will not then be visible leaves two problems. After the INSERT has been carried out, the data will not be visible, which causes some users to attempt additional updates (thinking the previous one failed). If the users are aware that the data INSERT did succeed, they are still unable to check the accuracy of the UPDATE, because the data is not visible. You should consider using the WITH CHECK OPTION with all views to limit data inserts and avoid user confusion. To add data through a view, follow Step by Step 7.5.

For high quality website hosting services please check java web hosting website.