Creating Views

Views can be created using Transact-SQL statements or through Enterprise Manager. Enterprise Manager also provides a Create View Wizard that will step you through the creation of a view. The Create View Wizard can be accessed via the Wizards option in the Tools menu.

This section will first present some guidelines and restrictions for creating views which are applicable no matter which tool you choose.

Guidelines for Creating Views

When creating a view, consider using an intuitive name that identifies it as a view, such as dbo.emp_list_view . The owner name is optional, but it should be specified as dbo to avoid broken ownership chains ( assuming the referenced objects are also owned by dbo ). When the view is created, the existence of objects referenced in the SELECT statement is verified , and you must have SELECT permission on the objects if the owner differs from that of the view. If the columns referenced in the view are derived (for example, the column contains an arithmetic expression or aggregate function), or if columns from multiple tables have the same name, column names must be specified for the view columns . Column names also can be specified to hide the column names of the base tables, or perhaps to simply make them more user friendly.

Before you create the view, test the SELECT statement to make sure it returns the desired results. This also might be a good time to consider whether additional indexes might be required on columns referenced by the view, especially any columns specified in search arguments or join columns.

Restrictions on Creating a View

Be aware of the following restrictions when creating a view:

• A CREATE VIEW statement cannot contain the INTO keyword or the COMPUTE or COMPUTE BY clauses.

• The ORDER BY clause can only be specified in a view in conjunction with the TOP keyword.

• The CREATE VIEW command cannot be combined with other T-SQL statements in a batch.

• A view cannot reference more than 1,024 columns, nor can it reference temporary tables or table variables .

• Views must be created in the current database. The CREATE VIEW statement does not allow you to specify a database name. However, the view can reference tables or views that exist in other databases, or even other servers when using distributed queries.

Creating Views Using T-SQL

Views are created, surprisingly enough, with the CREATE VIEW statement. The syntax for creating views is as follows :

 CREATE VIEW owner.  view_name  [  (   column  [  ,...   n  ]  )  ]  [WITH {ENCRYPTIONSCHEMABINDINGVIEW_METADATA}[  ,...   n  ]] AS  select_statement  [WITH CHECK OPTION] 

The CREATE VIEW options are outlined in the next few sections.

WITH ENCRYPTION

When a view is created, its definition is stored in the text column of the syscomments table. If WITH ENCRYPTION is specified when the view is created or altered , the definition is stored encrypted. If you are encrypting a view, make sure you save the original script in case it is required to re-create or alter the view in the future. Encrypting the view also prevents it from being published with replication.

NOTE

If you must hide the definition of a view, use the WITH ENCRYPTION option. Do not attempt to remove the view definition by deleting it from the syscomments table. This will prevent you from using the view, and also prevent SQL Server from being able to re-create the view during the next upgrade.

WITH SCHEMABINDING

The WITH SCHEMABINDING option binds the view to the underlying object schemas. Use of the WITH SCHEMABINDING option requires that two-part (owner.object) names be specified for all referenced objects. This option prevents referenced objects from being dropped or modified in a way that would affect the view, without the view first being dropped or modified to remove the schema binding.

WITH VIEW_METADATA

When this option is specified, SQL Server returns information about the view, as opposed to the base tables, when browse-mode metadata is requested for a query that references the view via a database API. Browse-mode metadata is additional information returned by SQL Server to client-side DBLIB, ODBC, and OLE DB APIs, which allows them to implement client-side updateable cursors .

WITH CHECK OPTION

By default, when a view contains a WHERE clause that restricts which rows can be seen through the view, it does not prevent the user from modifying or inserting a row into the view that cannot subsequently be seen through the view. For example, the definition of USA_contactview shown in Listing 27.3 would not prevent the following insert and update statements from being executed, even though the user would not be able to see the rows generated by these statements:

 update USA_contactview set country = 'Canada'     where CustomerID = 'WHITC' insert USA_contactview (CustomerID, CompanyName, ContactName,                         ContactTitle, Phone, Country)    values ('GOTHX', 'Gotham Consulting Services', 'Ray Rankins',             'President', '[phone]', 'UK') 

From a user perspective, the effect of modifying a row through a view such that it could no longer be queried through the view would be that of having deleted the row. To prevent this situation, you need to create the view with the WITH CHECK OPTION property specified. The following version of the USA_contactview would prevent the previous insert and update statements from occurring:

 /* create view that allows access to only USA Customers */  CREATE VIEW dbo.USA_contactview AS SELECT CustomerID, CompanyName, ContactName, ContactTitle, Phone, Country    FROM  dbo.Customers    WHERE Country = 'USA' WITH CHECK OPTION Go 

Attempting to perform either of those statements would result in the following error message:

 Server: Msg 550, Level 16, State 1, Line 1  The attempted insert or update failed because the target view either specifies  WITH CHECK OPTION or spans a view 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. 

CREATE VIEW Examples

The following examples illustrate the use of the CREATE VIEW statement.

Listing 27.5 creates a view on the employee table using the WITH CHECK OPTION .

Listing 27.5 Creating a View Using WITH CHECK OPTION

 CREATE VIEW Uk_emp_view AS SELECT * FROM employees WHERE country = 'UK' WITH CHECK OPTION GO 

A view is often created to retrieve data from multiple tables. Listing 27.6 illustrates this. This view also uses WITH ENCRYPTION to hide the view definition and SCHEMABINDING to prevent changes to underlying tables unless the view is dropped or altered first.

Listing 27.6 Returning Data from Multiple Tables in a View

 CREATE VIEW High_Sales_View WITH ENCRYPTION, SCHEMABINDING AS SELECT o.orderid, o.customerid, od.quantity, od.productid    FROM dbo.orders o    JOIN dbo.[order details] od      ON o.orderid = od.orderid    WHERE od.quantity > 20 GO 

Creating Views Using the View Designer

If you prefer to create views through the Enterprise Manager, you can access the View Designer by right-clicking the Views icon in the appropriate database and selecting New View. The View Designer is actually based on the Query Designer; however, when you select Save, you are prompted for a name for the view and the query you have built is stored as the view definition. Figure 27.1 shows the View Designer.

Figure 27.1. The View Designer window.

After you are in View Designer, you can click on the Add Table icon (it's the one to the far right) in the toolbar, or right-click in the Design pane and click Add Table in the pop-up menu to add tables, views, and table-valued user-defined functions to your query. Selecting the check boxes for the appropriate columns adds them to the SELECT statement that will be saved as your view definition. Figure 27.2 illustrates a join on the Orders and Order Details tables being saved as a view.

Figure 27.2. Saving a view definition.

If you want to add view options such as the WITH CHECK OPTION property, select the Properties icon or right-click in the window and choose Properties to bring up the view's Properties page. Figure 27.3 shows the Properties page.

Figure 27.3. Defining options for a view.

TIP

When using tools, such as the View Designer, hover over the taskbar icons and a pop-up will describe their function. Context-sensitive help also is available for most functions. To access context-sensitive help, click the Help button on the appropriate page or window, or simply press F1.