Creating Views

3 4

Views, like indexes, can be created in a number of ways. You can create a view by using the CREATE VIEW T-SQL statement. This method is preferable if there is any chance that you will be creating more views in the future, because you can put the T-SQL statements in a script file and then edit and use the file over and over again. SQL Server Enterprise Manager provides a graphical environment in which you can create a view. Finally you can use the Create View Wizard when you need to be walked through the view creation process, which can be useful for the novice and expert alike.

Using T-SQL to Create a View

Creating views by using T-SQL is a straightforward process: you run CREATE VIEW to create a view, using ISQL, OSQL, or SQL Server Query Analyzer. As mentioned, using T-SQL commands in a script is preferable because the commands can be modified and reused. (You should also store your database definitions in scripts in case you need to re-create your database.)

The CREATE VIEW command has the following syntax:

 CREATE VIEW view_name [(column, column, ...)] [WITH ENCRYPTION] AS your SELECT statement [WITH CHECK OPTION] 

When you create a view, you can activate two options that change the view's behavior. You activate these options by including the optional keywords WITH ENCRYPTION or WITH CHECK OPTION or both in the T-SQL statement. Let's look at these options more closely.

The WITH ENCRYPTION keyword specifies that the view definition (the SELECT statement that defines the view) be encrypted. SQL Server uses the same encryption method to encrypt SQL statements that is used for passwords. This security technique can be useful if you don't want certain classes of users to know which tables are being accessed.

The WITH CHECK OPTION keyword specifies that data modification operations performed on a view must adhere to the criteria contained within the SELECT statement that defined the view. For example, a data modification operation performed on a view to create a table row that is not visible within the view would not be allowed. Suppose a view is defined to select information about all finance department employees. If WITH CHECK OPTION is not included, you can modify the department column value from finance to a value indicating another department. If this keyword is specified, this modification would not be allowed because changing a row's department value would make this row no longer accessible from the view. The WITH CHECK OPTION keyword specifies that you cannot make a row inaccessible from the view by making a change within the view.

The SELECT statement can be modified to create whatever view you want. It can be used to select a subset of columns or a subset of rows, or it can be used to perform a join operation. In the following sections, you'll learn how to use T-SQL to create the various types of views.

Subset of Columns

A view consisting of a subset of columns can be useful when you need to provide security for a table that should be only partially accessible to users. Let's look at an example. Suppose a corporate employee database consists of a table named Employee that contains the data columns shown in Figure 18-1.

Figure 18-1. The Employee table.

Most of this data is sensitive and should be viewed by only certain employees. It would be useful, however, to allow all employees to view some of the data. Creating a view that gives all employees access to only certain data will address this issue. The view could also be used to avoid duplicating employee data in other database tables.

To create a view on the Employee table that can access only the name, phone, and office columns, use the following T-SQL statement:

 CREATE VIEW emp_vw AS SELECT name, phone, office FROM Employee 

The resulting view will contain the columns shown in Figure 18-2. Although these columns also exist in the underlying table, users who access the data through this view can see the columns only in the view. Because the view can have a different security level than that of the underlying table, the view can allow access to anyone while the underlying table remains secure. In other words, you can restrict access to the Employee table to only the human resources department, for example, while allowing all employees to use the view.

Figure 18-2. The emp_vw view.

Subset of Rows

A view that consists of a subset of rows can be used to restrict access by restricting the rows that are available to users. Suppose our Employee table was populated with data, as shown in Figure 18-3. In this example, instead of restricting the columns, we will restrict the rows by specifying them in a WHERE clause, as shown here:

 CREATE VIEW emp_vw2 AS SELECT * FROM Employee WHERE Dept = 1 

Figure 18-3. The Employee table with data.

The resulting view will contain only the rows that list an employee who works in the human resources department, or department 1, as shown in Figure 18-4. This view would be useful when employees in the human resources department should have access to the records of employees in their area. A subset-of-rows view, like a subset-of-columns view, can be assigned a different security level than that of the underlying table or tables.

Figure 18-4. The emp_vw2 view.

Joins

By defining joins in a view, you can simplify the T-SQL statements used to access data when the statements would contain a JOIN statement. Let's work through an example. Suppose we have two tables, Manager and Employee2, as shown in Figure 18-5.

Figure 18-5. The Manager and Employee2 tables.

The following statement joins the Employee2 and Manager tables into a single virtual table:

 CREATE VIEW org_chart AS SELECT Employee2.ename, Manager.mname FROM Employee2, Manager WHERE Employee2.manager_id = Manager.id GROUP BY Manager.mname, Employee2.ename 

In this example, the two tables are joined on the manager_id value. The resulting data, contained in the view org_chart, is grouped by manager name, as shown in Figure 18-6. Notice that if a manager is listed in the Manager table but no employees listed in the Employee2 table work for that manager, no entry for that manager appears in the view. There is also no entry in the view for an employee who is listed in the Employee2 table but has no corresponding manager listed in the Manager table. To users, all that is visible is a virtual table listing employees and managers.

Figure 18-6. The org_chart view.

Aggregation

Aggregation views can be useful in many ways, such as for retrieving departmental averages and sums. For example, for budgeting purposes, you could use an aggregate view to view the amount of money each department in your corporation pays in salaries. You can also accomplish this task by using a T-SQL query. The advantage of using a view is that users can run it without knowing how to use aggregate functions and T-SQL queries.

NOTE

---

The SQL Server aggregate functions perform calculations on a set of values and return a single value. The aggregate functions include AVG, COUNT, MAX, MIN, and SUM.

The following statement sets up a view that uses an aggregate function (SUM) on the Employee table:

 CREATE VIEW sal_vw AS SELECT dept, SUM(salary) FROM Employee GROUP BY dept 

In this example, the view sets up a virtual table that shows the total each department pays in salaries. The resulting data is grouped by department, as shown in Figure 18-7. This aggregate view is quite simple. Your views can be as complex as necessary to perform the desired function.

Figure 18-7. The sal_vw view.

Partitioning

Views are commonly used to merge partitioned data into a single virtual table. Partitioning is used to reduce the size of tables and indexes. To partition data, you create multiple tables to replace one table and assign each new table a range of values from the original table. For example, instead of having one large database table that stores data about your company's sales transactions, you can create many small tables that each hold one week's worth of data and then use a view to combine them to see the transaction history. The multiple small tables and their indexes are more manageable than one large database table and its index would be. Furthermore, you can easily drop older data by deleting an underlying table of obsolete data. Let's look at this concept in more detail.

The view shown in Figure 18-8 looks like one large table to users, but underneath are many tables, each with its own index. (In fact, a clustered date index would be appropriate here.)

Figure 18-8. Using views to merge partitioned data.

As mentioned, partitioning creates a much more manageable system for the DBA, and merging partitioned data simplifies the data for users.

To create a view that consolidates partitioned data, or a partitioned view, you must first create the partitioned tables. These tables will most likely contain sales data. Each table will store data for a particular period, usually a week or a month. Once you create these tables, you can use a UNION ALL statement to create a view that contains all of the data. For example, suppose you have four tables, named table_1, table_2, table_3, and table_4. The following statement creates one large virtual table that includes all the data from these tables:

 CREATE VIEW partview AS SELECT * FROM table_1 UNION ALL SELECT * FROM table_2 UNION ALL SELECT * FROM table_3 UNION ALL SELECT * FROM table_4 

Now all of the data is available in one table, but it is still manageable. As new partitions are created and older ones are dropped, the view will need to be re-created.

Using Enterprise Manager to Create a View

In this section, we will use Enterprise Manager to create a view in the Northwind database. The following steps will guide you through this process:

1. In the Enterprise Manager window, expand the Databases folder for the server on which the sample database Northwind resides and click Northwind, as shown in Figure 18-9.

   

   Figure 18-9. Information about the Northwind database.

2. Right-click Northwind in the left-hand pane. Point to New in the shortcut menu that appears, and then choose View. The New View window appears, as shown in Figure 18-10. You use this window to define the view name, the table columns used in the view, and the underlying table structure.

   The New View window consists of the following four panes:

   • Diagram pane Shows the table data that is used to create the view. Columns can be selected from within this pane.
   • Grid pane Shows the columns that have been selected from the table or tables that make up the view. Columns can be selected from within this pane.

   

   Figure 18-10. The New View window.
   
   
   
   • SQL pane Shows the SQL statement that is used to define the view. SQL Server generates this SQL statement for you when you drag items in the Diagram pane and select columns in the Grid pane.
   • Results pane Shows the rows that have been retrieved from the view. This information gives you an idea of what the data looks like.

   You can specify which panes are visible by clicking the corresponding buttons on the New View window's toolbar. The other buttons on this toolbar provide some important options. The following list describes all the toolbar buttons, starting from the left side of the toolbar:

   • Save Saves the view.
   • Properties Enables you to change the view's properties. Clicking this button displays the Properties window, which includes the Distinct Values and Encrypt View options.
   • Show/Hide Panes (four buttons) Let you show or hide the four panes of the New View window.
   • Run Runs the query and displays the results in the Results pane. This test can be used to verify that the query is performing correctly.
   • Cancel Execution And Clear Results Clears the Results pane.
   • Verify SQL Tests the query against the underlying table to validate the SQL statement.
   • Remove Filter Removes any filters that have been defined.
   • Use GROUP BY Adds a GROUP BY clause to the statement in the SQL pane.
   • Add Table Lets you add a table to the query.
3. Modify the SELECT statement in the SQL pane to match the SELECT statement shown in Figure 18-11. The view will consist of the columns CompanyName, ContactName, and Phone. Once you have typed the SELECT statement, click the Verify SQL button to verify that the query is valid. If it is, you must click OK in the dialog box that appears to allow Enterprise Manager to fill in the Diagram and Grid panes. Your New View window will look like the window shown in Figure 18-11.

   

   Figure 18-11. The filled-in New View window.

4. Once you have finished verifying that the view behaves as intended (by using the Results pane) and have made any necessary changes, close the New View window. At this point, a dialog box appears asking whether you want to save the view. If you click Yes, you will be prompted to supply a name for the view. Type a descriptive name for your view, and save it by clicking OK.

Your view is now available for use. You can use Enterprise Manager to set the properties of the new view, including permissions. The View Properties window is described in the section "Altering and Dropping Views" later in the chapter.

Using the Create View Wizard to Create a View

To use the Create View Wizard to create a view, follow these steps:

1. In Enterprise Manager, choose Wizards from the Tools menu, expand the Database folder in the dialog box that appears, select Create View Wizard, and click OK. The Create View Wizard welcome screen appears, as shown in Figure 18-12.

   

   Figure 18-12. The Create View Wizard welcome screen.

2. Click Next to display the Select Database screen. This screen lets you specify the database for which you will be creating the view—in this case, the Northwind database.
3. Click Next to display the Select Objects screen, shown in Figure 18-13. Here you can select one or more tables to be referenced in the view. If you are creating a simple view, you can select a single table. To create a view on a join, choose multiple tables.

   

   Figure 18-13. The Select Objects screen.

4. Click Next to display the Select Columns screen, shown in Figure 18-14. Here you can select the columns you want to use in the view—in this example, the CompanyName, ContactName, and Phone columns have been selected.

   

   Figure 18-14. The Select Columns screen.

5. Click Next to display the Define Restriction screen. This screen is used to define an optional WHERE clause to restrict the rows in the database that are selected in the view.
6. Click Next to display the Name The View screen, shown in Figure 18-15. Type a name for the view in the View Name text box.

   

   Figure 18-15. The Name The View screen.

7. Click Next to display the Completing The Create View Wizard screen, shown in Figure 18-16. This screen enables you to save the view, backtrack and make changes, or cancel the view creation. Once you click Finish, the view will be available for use.

   

   Figure 18-16. The Completing The Create View Wizard screen.

Tips for Views

When you are creating a view, remember that a view is made up of an SQL statement that accesses the underlying data, and that you control this SQL statement. Also keep in mind the following guidelines, which can help improve the performance, manageability, and usability of your databases:

• Use views for security. Rather than re-creating a table to provide access to only certain data in the table, create a view that contains the columns and rows you want to make accessible. Using a view is an ideal way to restrict some users to one portion of the data and other users to a different portion of the data. By using a view instead of building a table that must be populated from an existing table, you do not increase the amount of data and you maintain security.
• Take advantage of indexes. Remember that when you use a view, you are still accessing the underlying tables and therefore the indexes on those tables. If a table has a column that is indexed, be sure to include that column in the WHERE clause of the view's SELECT statement. Only if the column is part of the view and is used in the WHERE clause can the index be used for selecting data. For example, if the Employee table has an index on the dept column and this column is included in the view, the index can be used.
• Partition your data. Views are especially useful in enabling you to partition data, which can reduce the amount of time needed to rebuild indexes and manage the virtual table by reducing the size of the individual components. For example, if an index rebuild on a single large table takes two hours, you could partition the data into four smaller tables for which the index rebuild time is much shorter. You could then define a view that transparently combines the individual tables. With large tables that store historical data, this technique can be quite useful.