Views can be thought of as virtual tables because the result sets returned by views have the same general form as a table, with columns and rows, and views can be referenced much like tables in queries. Several types of views can be created. Most views are used to join data from multiple tables so it can be accessed in a single result set. For example, you could create a CustOrder view that gets the customer’s first name, last name, address, account number, and telephone number from the Customers table and the last order details from the Orders table, which makes the information more manageable for your company’s sales representatives.
Views can be created from other views as well, which allows you to extract subsets of data from views and to create supersets that combine data from multiple views. For example, you could create a subset view of the CustOrder view that shows only the customer’s first name, last name, and telephone number. You could also create a superset view that combines elements of the CustOrder view, the AllCustOrders view, and LastOrder view.
To create a view, you use a SELECT statement to select the data in one or more tables and display it as a view. Like tables, views can be partitioned and indexed. A partitioned view joins horizontally partitioned data from a set of base tables from one or more servers. A partitioned view in which all base tables reside in the same database is referred to as a local partitioned view. A partitioned view in which one or more base tables reside on one or more different, remote servers is referred to as a distributed partitioned view.
Tip
Typically, you will use distributed partitioned views rather than local partitioned views because the preferred method for partitioning data locally is through partitioned tables (and local partitioned views are supported only for backward compatibility). Distributed partitioned views are used to create a federation of database servers. A federation is a group of servers that are managed separately, but which cooperate to share the query processing load of a large application or Web site.
Partitioned views also may be updatable or read-only. Updatable partitioned views are updatable copies of the underlying tables. Read-only partitioned views are read-only copies of the underlying tables. To perform updates on a partitioned view, the partitioning column must be part of the base table’s primary key. If this is not possible (or to make read-only partitioned views updatable), you can use INSTEAD OF triggers. INSTEAD OF triggers execute whenever a user attempts to modify data using INSERT, UPDATE, or DELETE. Views comprising multiple base tables must use an INSTEAD OF trigger to support inserts, updates, and deletes that reference data in more than one table.
You index views to improve query performance. The first index created on a view must be a unique clustered index. After the unique clustered index is created, you can create additional nonclustered indexes on the view. A view for which you want to create a unique clustered index must not reference any other views. It can only reference base tables, and those base tables must be in the same database as the view and have the same owner as the view.
The query processor handles indexed and nonindexed views in different ways. The rows of an indexed view are stored in the database in the same format as a table. If the query optimizer uses an indexed view in a query plan, the indexed view is handled the same way as a base table. With nonindexed views, only the view definition is stored in the database, not the rows of the view. If the query optimizer uses a nonindexed view in an execution plan, only the logic from the view definition is used.
When an SQL statement references a nonindexed view, the parser and query optimizer analyze the source of both the SQL statement and the view and resolve them into a single execution plan. This means that there is not one plan for the SQL statement and a separate plan for the view—there is only one execution plan.
As with tables, views are contained in schemas, and you can assign permissions to views. Typically, you want the base tables and the related views to be in the same schema. It is also important to note that permissions assigned to views are separate from the table permissions.
Views can have up to 1,024 columns. If you understand tables, creating views is a fairly straightforward process. However, there are a few rules to follow. Although the SELECT statement used to create a view can use more than one table and other views, you must have appropriate permissions to select from the referenced objects. The view definition cannot include COMPUTE or COMPUTE BY clauses, an ORDER BY clause (unless there is also a TOP clause), the INTO keyword, the OPTION clause, or a reference to a temporary table or a table variable.
You can create a view in SQL Server Management Studio by completing the following steps:
In SQL Server Management Studio, select a database, and then expand the Views node to list the current views in the database. Two types of views are available: system and user. System views provide a summarized report of database information, such as table constraints and table privileges. User views are defined by you or by other database users.
To create a new view, right-click the Views node. From the shortcut menu, choose New View to display the Add Table dialog box shown in Figure 9-5. The Add Table dialog box has tabs that allow you to work with tables, views, functions, and synonyms. If you select Add Derived Tables from the Query Designer menu and display the dialog box again, you will see a Local Tables tab containing derived tables.
In the Add Table dialog box, select a table or other object that contains data you want to add to the view, and then click Add. This displays a view pane for the selected object, which you can use to add columns, fields, and so on to the view you are creating.
When you are finished working with the Add Table dialog box, click Close. You can display this dialog box again at any time by selecting Add Table on the Query Designer menu.
Use the view panes provided to select the columns and fields to use in the view, as shown in Figure 9-6. Your actions create a SELECT statement that can be used to generate the view.
The View Properties pane is not displayed by default. In the right pane, click the tab with the name of the view, and then press F4 to display the View Properties pane.
Set the view name, description, and schema. Type the name and description in the fields provided. Use the drop-down list to select the schema that will contain this view.
You may want to create a dependency within a schema to ensure that any modifications to the underlying structures that comprise the view are not changed without first changing the view. You do this by binding the view to the schema. If you want to bind the view to the schema, set Bind To Schema to Yes.
Note
When you bind a view to the schema, views or tables used in the view cannot be dropped unless that view is dropped or changed so that it no longer has schema binding. Furthermore, executing ALTER TABLE statements on tables that participate in views that have schema binding will fail when these statements affect the view definition.
If you want to ensure that the view shows only distinct rows and filters out duplicate rows, set Distinct Values to Yes.
If you want the view to return a partial result set containing the top matches, set Top to Yes, and then define the number of top matches to return as either a fixed maximum or a percentage of total results.
To define a fixed maximum, set Expression to the maximum number of results to return and set Percent to No. For example, set Expression to 50 to return the top 50 results.
To define a percentage of the total results to return, set Expression to the percentage of results to return and set Percent to Yes. For example, set Expression to 10 and Percent to Yes to return the top 10 percent of the result set.
If you want to create an updatable view, set Update Using View Rules to Yes. Updatable views cannot be created with distinct values or from top result sets. To make sure data remains visible through the view after a modification is committed, set Check Option to Yes. Remember, however, that updates performed directly to a view’s base tables are not verified against the view even when you select the Check Option.
When you are finished configuring the view, verify the SQL syntax by selecting Verify SQL Syntax from the Query Designer menu. Correct any errors or issues reported during the verification process before continuing.
To create the view, press Ctrl+R or select Execute SQL on the Query Designer menu.
After you run the view to update it for the latest changes, save the view. Press Ctrl+S or click Save on the toolbar.
You can also create views using the CREATE VIEW statement. You can create a simple view by selecting all the values in a table:
CREATE VIEW Sales.CustomView As SELECT * FROM Sales.Customers
Then you can work directly with the view:
SELECT * FROM Sales.CustomView
Example 9-8 shows the full syntax and usage for CREATE VIEW.
Example 9-8. CREATE VIEW Syntax
CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ]
[ WITH <view_attribute> [ ,...n ] ]
AS select_statement [ ; ]
[ WITH CHECK OPTION ]
<view_attribute> ::=
{ [ ENCRYPTION ]
[ SCHEMABINDING ]
[ VIEW_METADATA ] }Usage
CREATE VIEW Sales.CustomView As SELECT cust_id AS Account, cust_lname AS [Last Name], cust_fname AS [First Name], state AS Region FROM Sales.Customers WHERE (state = 'WA') OR (state = 'HI') OR (state = 'CA')
You can modify a view in SQL Server Management Studio by completing the following steps:
In SQL Server Management Studio, select a database, and then expand the Views node to list the current views in the database.
To modify an existing view, right-click the view, and then select Modify.
If you want to add tables, views, and so on, you can display the Add Table dialog box by selecting Add Table from the Query Designer menu.
If you want to set view properties, click the tab with the name of the view and then press F4 to display the View Properties pane.
To change an existing view without having to reset its permissions and other properties, you use ALTER VIEW. The following example changes the definition of the Sales Custom view used in previous examples:
ALTER VIEW Sales.CustomView As
SELECT cust_id AS Account, cust_lname AS [Customer Last Name],
cust_fname AS [Customer First Name], state AS Region
FROM Sales.Customers
WHERE (state = 'WA') OR
(state = 'CA')Example 9-9 shows the full syntax for ALTER VIEW.
SQL Server supports updatable views as well. With an updatable view, you can change the information in the view using INSERT, UPDATE, and DELETE statements. You can create updatable views if the table columns being modified are not affected by GROUP BY, HAVING, or DISTINCT clauses. Furthermore, an updatable view can only be modified when the columns from one base table are being modified and those columns directly reference the underlying data. This means the data cannot be derived from an aggregate function or computed from an expression that uses other columns.
With updatable views, you will usually want to set Check Option to Yes. If you do not set the option, changes to the view may result in rows that are no longer displayed in the view. For example, consider the view created as an example previously. The view included customer information from Washington (WA), Hawaii (HI), and California (CA). If you change a state value to GA, the row would disappear from the view because Georgia-based customers are not displayed in the view.
You can examine view properties, set view permissions, and perform other management tasks, just as you do with tables. To get started managing views, complete the following steps:
In SQL Server Management Studio, select a database, and then expand the Views node to list the current views in the database.
Select a view, and then right-click it to open a shortcut menu that gives you the following choices to manage the view:
To set permissions for a view, right-click the view, and then select Properties. In the View Properties dialog box, select the Permissions page. You can now manage the permissions for the view.