Web Hosting Java Blog

404 Part I EXAM PREPARATION FIGURE 7.6 Accessing the dependencies of a view. Enhancing Views You can create view definitions beyond mundane SELECT statements that simply place data statements into a resultset. With views, you can use joins to get data from multiple tables. This is useful because the desired data is not always stored on a single table (in fact, this is rarely the case). You can also leverage the complexity of aggregate functions in your views in the same way you would use them on a normal table. You can further refine data retrieval by building views on views, so that data is filtered on the appropriate conditions. Aggregates and Views Aggregates offer a great deal of calculation power and views can make use of them. These aggregates include AVG, COUNT, DISTINCT, and similar functions. Leveraging the power of aggregates enables you to create useful reports that can be produced based on data in a table. The following example outlines the importance of aggregates with the example of a user who requests to see a report on the minimum, maximum, average and total values of the Quantity column (grouped by orderID) from the Order Details table in the

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

Chapter 7 WORKING WITH VIEWS 403 STEP BY STEP 7.2 Accessing Views 1. Open the Query Analyzer by selecting it from the Start menu and logging in. 2. To view the definition of titleview from the Pubs database (see Figure 7.5), execute the following: USE pubs GO EXEC sp_helptext titleview 3. To display the view s associated tables, execute the following query. This process is shown in Figure 7.6. EXEC sp_depends titleview FIGURE 7.5 Accessing the definition of a view.

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

402 Part I EXAM PREPARATION FIGURE 7.4 Creating a row-based filtration on a view. required, and displays the view definition. As you ve already seen, protecting a view definition is possible if you use the WITH ENCRYPTION option. sp_helptext lets you know what tables and views your view references. This information is helpful if you are having trouble figuring out why your view does not work because you will see what tables or views your malfunctioning view uses. Knowing the actual structure of the tables that your view references is useful when troubleshooting or redesigning your view; but knowing what views or stored procedures reference your view is useful when you plan to make alterations to or drop a view. If you do not check on known dependencies, then you risk making other views or stored procedures unusable. To see what objects reference a view, use the sp_depends stored procedure. View dependencies are stored in the sysdepends system table. When the sp_depends stored procedure is executed, it queries the sysdepends table and summarizes the final result. Step by Step 7.2 shows you how to access views.

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

Chapter 7 WORKING WITH VIEWS 401 Base Table FIGURE 7.3 A sample view filtered by row. emp_id fname minit lname job_id job_lvl pub_id 1 PMA42628M Paolo M Accorti 13 35 877 2 PSA89086M Pedro S Afonso 14 89 1389 3 VPA30890F Victoria P Ashworth 6 140 877 4 H-B39728F Helen Bennett 12 35 877 5 L-B31947F Lesley Brown 7 120 877 6 F-C16315M Francisco Chang 4 227 9952 7 PTC11962M Philip T Cramer 2 215 9952 8 A-C71970F Aria Cruz 10 87 1389 9 AMD15433F Ann M Devon 3 200 9952 View 1 (WHERE lname LIKE ‘A%’ or lname LIKE ‘B%’) emp_id fname minit lname job_id job_lvl pub_id 1 PMA42628M Paolo M Accorti 13 35 877 2 PSA89086M Pedro S Afonso 14 89 1389 3 VPA30890F Victoria P Ashworth 6 140 877 4 H-B39728F Helen Bennett 12 35 877 5 L-B31947F Lesley Brown 7 120 877 View 2 (WHERE lname LIKE ‘C%’ or lname LIKE ‘D%’) emp_id fname minit lname job_id job_lvl pub_id 6 F-C16315M Francisco Chang 4 227 9952 7 PTC11962M Philip T Cramer 2 215 9952 8 A-C71970F Aria Cruz 10 87 1389 9 AMD15433F Ann M Devon 3 200 9952 The following query shows you what horizontal filtering looks like: SELECT * FROM SelectedRows After executing this statement, you should notice that the State, FirstName, and Au_ID columns are shown WHERE the State is CA. This is shown in Figure 7.4. You should now have a good grasp of the basics of a view. You know how to restrict the amount of data that is returned through the selection of columns and rows. The next section looks at further controlling the data that is returned through the view. Accessing Views Displaying the definition of a view may need to be done at some time. To access the exact definition of the view you created, use the sp_helptext system stored procedure. The actual definition of a view is stored in the syscomments system table. The sp_helptext procedure queries the syscomments, organizes the information

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

400 Part I EXAM PREPARATION FIGURE 7.2 Automated coding using the View Designer. Now that you have seen how to restrict data in a view by columns, you now see how to limit the number of rows that are returned. Selecting Specific Rows You can also define a view that references some, or all, of the rows of a table. Selecting certain rows involves the help of the WHERE clause. This filters out all entries that do not match the conditions defined in a WHERE clause (see Figure 7.3), and is known as horizontal filtration. Using the WHERE clause returns only relevant data and forces data security through the restricting of the returned data. With horizontal filtration, the types of attributes (or columns) returned in the view remains constant, but you can see the difference in the number of rows returned. This is like pulling out a few pages of a phone blog. Each entry in a phone blog has a name, address, and phone number (the columns), but the few pages that were pulled out represent the portion of the data that you are going to use. This is how a horizontally filtered view behaves, with all the columns, but not all the rows. To create a view based on horizontal filtration, see this code: CREATE VIEW SelectedRows AS SELECT Au_ID, State, au_FName FROM Authors WHERE State = CA

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

Chapter 7 WORKING WITH VIEWS 399 STEP BY STEP 7.1 Creating Views by Selecting Specific Columns Using the Enterprise Manager 1. Open the Enterprise Manager from the Start menu and connect to the default instance of SQL Server. 2. To create a new view in the Pubs database, expand Pubs from the right pane and then right-click the Views icon. Select New View to start designing a new view. 3. This example uses the Sales table as a base table for the new view and you will design it so that it selects all columns from the Sales table except for the Payterms column. To do this, right-click in the topmost pane and select Add Table; this initializes the Add Table dialog box. Scroll down the list to find Sales; click Add, and then click Close to close the Add Table dialog box. Notice that the table was added graphically on the top-most pane. 4. To add a new column to the view definition, select the check box next to it. For this example, select all columns except Payterms. Adding a column using this graphical method automates coding (see for yourself by looking at the code pane) and, thus, is a good solution for a user who is an SQL programming novice. This is shown in Figure 7.2. 5. After you have selected the desired columns to be referenced in your view, you can take it on a test run by clicking on the red exclamation mark on the toolbar. 6. You can now save your view by clicking on the disk button in the toolbar. Save the view with an appropriate name, such as SalesViews, and then you are finished. You can also specify desired columns using the Query Analyzer. Examine the following code: CREATE VIEW SelectedColumns AS SELECT Type, Price FROM Titles

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

398 Part I EXAM PREPARATION At a particular point in time, you may want to limit your view data to specific rows or columns of data. The following two sections look at selecting columns for your view and selecting rows for your view. The actual requirement for which data should be shown depends on your specific circumstances. Selecting Specific Columns You can define a view that is made up of some, or all, of the columns of a table. This process is sometimes known as vertically filtering a table (see Figure 7.1). Selecting certain columns is useful when you want to protect data from prying eyes. A good example of this might be when you want to hide a salary column of a table, and therefore create a view that references all columns except the salary column. Step by Step 7.1 shows you how to specify columns with the Enterprise Manager. Base Table emp_id fname minit lname job_id job_lvl pub_id 1 PMA42628M Paolo M Accorti 13 35 877 2 PSA89086M Pedro S Afonso 14 89 1389 3 VPA30890F Victoria P Ashworth 6 140 877 4 H-B39728F Helen Bennett 12 35 877 5 L-B31947F Lesley Brown 7 120 877 6 F-C16315M Francisco Chang 4 227 9952 7 PTC11962M Philip T Cramer 2 215 9952 8 A-C71970F Aria Cruz 10 87 1389 9 AMD15433F Ann M Devon 3 200 9952 View 1 View 2 emp_id fname minit lname 1 PMA42628M Paolo M Accorti 2 PSA89086M Pedro S Afonso 3 VPA30890F Victoria P Ashworth 4 H-B39728F Helen Bennett 5 L-B31947F Lesley Brown 6 F-C16315M Francisco Chang 7 PTC11962M Philip T Cramer 8 A-C71970F Aria Cruz 9 AMD15433F Ann M Devon job_id job_lvl pub_id 1 13 35 877 2 14 89 1389 3 6 140 877 4 12 35 877 5 7 120 877 6 4 227 9952 7 2 215 9952 8 10 87 1389 9 3 200 9952 FIGURE 7.1 A sample view filtered by column.

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

Chapter 7 view_name is the name of the view and must adhere to the rules for identifiers. column is the name to be used for the columns that are returned from the select statement. This is useful when you want to change the name of the column identifier. WITH ENCRYPTION encrypts the syscomments columns that contain the text of the CREATE VIEW statement. Encryption prevents people from being able to see the view definition, replicate the view, or generate a complete database creation script. If you attempt to view the properties of the view through Enterprise Manage or sp_helptext, you see a comment telling you that the view is encrypted. If you query sys_comments directly, you see an encrypted string or gibberish. WITH SCHEMABINDING specifies that the view be bound to the schema. Visually this means that tables, views, and functions that the view refers to must contain the owner name in the reference (for example, dbo.sales). Schema binding is designed to prevent objects from being dropped or modified while the view still refers to them, because that would cause the view to cease functioning. When creating a view, you may want to name an alias for a certain column to make a better user interface for your users. However, it is not obligatory. If a column alias is not specified, the view refers to the column name in the underlying table or view referenced. You must assign a column alias when joining two tables that have the same column names. Selecting Specific Columns and Rows As you have noted in previous sections, views are effective for securing data and reducing the display of non-relevant data. Views can help in showing your users only what you want them to see. This may be a set of rows or columns or both. This is especially important when sensitive information is involved, such as salary and credit card information. Views allow you to retrieve only the data that is needed or relevant. NOTE WORKING WITH VIEWS 397 Indexed Views and SCHEMABINDING Note that SCHEMABINDING has to be specified when you create indexed views. Also, when SCHEMABINDING is specified, you have to adhere to the owner.object syntax when referencing tables or views in the creation of your view. Indexed views are covered in Chapter 10, Boosting Performance with Indexes.

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

396 Part I EXAM PREPARATION NOTE Indexed Views Indexed views are new to SQL Server 2000 and can significantly speed up the time required to access a view. Indexed views are discussed at a greater depth in Chapter 10, Boosting Performance with Indexes. View names must adhere to uniqueness rules. Two users may create a view named MyView because the object owner makes the fully qualified names unique. Views cannot be created on temporary tables. Temporary views cannot be created. Views can be nested up to 32 levels. By now you should have a general feel for what a view is. You should also be aware of the benefits derived from and the restrictions that apply to views. With that knowledge, you can begin the next section. The next section looks at creating the view and the syntax required. CREATE AND ALTER VIEWS . Create and alter database objects. Objects include views. This section looks at creating views, including how to restrict the data that is returned through the view, and how to enhance these views by using aggregates and joins. Finally, it explains how to modify views and remove views. Because it is best to start at the beginning, the first thing to study is how to create views. Creating Views Views can be created using the Enterprise Manager, the Create View Wizard, or using T-SQL within the SQL Query Analyzer. When you use the Query Analyzer, you use the CREATE VIEW statement to create your virtual table. When creating a view, remember these guidelines. The following shows the syntax for the CREATE VIEW statement. CREATE VIEW [ < database_name > . ] [ < owner > . ] .view_name [ ( column [ ,...n ] ) ] [ WITH < view_attribute > [ ,...n ] ] AS < SELECT_statement > [ WITH CHECK OPTION ] < view_attribute > ::= { ENCRYPTION | SCHEMABINDING | VIEW_METADATA }

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

Chapter 7 WORKING WITH VIEWS 395 The greatest benefits that views provide relate to their role as predefined SELECT statements. This enables them to consolidate relevant information into convenient locations. There are some restrictions on what data can be combined in a view, and the following section covers some of those guidelines. View Creation Guidelines Views offer some great advantages, but have several restrictions that do not exist in normal SELECT statements. Before examining the view creation process, you should review some facts and restrictions that deal with views. SQL Server will not let you forget these facts when you are creating your views; it throws up an error, usually detailing what you are missing or doing wrong. The most important of these facts are the following: A view is nothing more than a SELECT statement that has been given a name and saved in the database. The view does not contain the data, but defines where the data comes from. The view can often be treated like a table. Because a view is only a SELECT statement, you can filter the data that is returned to include some or all of a table s rows or columns. You can stick to the important data. The view s definition can comprise of rows and columns from one or more tables in the current or other databases. A view can contain a maximum of 1024 columns. Defaults, triggers, and rules are not allowed to be associated with a view. The only exception to this rule is the new INSTEAD OF trigger. A view can use data that may be anywhere (such as another database) as long as the view is created in the current database. View names must follow the rules for identifiers. Views cannot be created using the ORDER BY clause unless they use the TOP clause (as in TOP 100 PERCENT). Views cannot be created using the COMPUTE BY or SELECT INTO clauses.

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