Creating and Managing Views in SQL Server 2008 : Managing Views, Data Modifications and Views

Managing Views

After creating your view, you can manage the view via T-SQL or the View Designer. The T-SQL commands for managing views are the ALTER VIEW and DROP VIEW statements. The ALTER VIEW statement is used to modify the properties or definition of the view, and the DROP VIEW statement is used to remove the view from the database.

Altering Views with T-SQL

The ALTER VIEW syntax follows:

ALTER VIEW [ schema_name . ] view_name [ ( column [ ,...n ] ) ]
[ WITH <view_attribute> [ ,...n ] ]
AS select_statement [ ; ]
[ WITH CHECK OPTION ]

<view_attribute> ::=
{
    [ ENCRYPTION ]
    [ SCHEMABINDING ]
    [ VIEW_METADATA ]
}

The ALTER VIEW statement utilizes the same set of options and parameters as the CREATE VIEW statement. You should consider using the ALTER VIEW statement when making changes to your view instead of dropping and re-creating the view. Altered views retain their associated permissions and do not affect dependent stored procedures or triggers.

An example of the ALTER VIEW statement follows:

 ALTER VIEW [dbo].[vw_employee]
 with SCHEMABINDING
 AS
SELECT TITLE, GENDER
   FROM HumanResources.Employee
 WITH CHECK OPTION

The entire definition of the view, including any attributes or options, must be listed in the ALTER VIEW statement. This behavior is similar to that of the ALTER PROCEDURE statement and some of the other ALTER statements. You can generate the ALTER VIEW statement from the Object Explorer by right-clicking the view and selecting Script View As and then choosing Alter To. This allows you to script the ALTER statement to a new query editor window, a file, or the Clipboard.

Dropping Views with T-SQL

You can drop views from a database by using the DROP VIEW statement. The syntax for DROP VIEW follows:

DROP VIEW [ schema_name . ] view_name [ ...,n ] [ ; ]

You can drop more than one view by using one DROP VIEW statement and listing all the targeted views, separated by commas. You should consider running the sp_depends stored procedure against the targeted views before dropping them. This procedure lists the objects dependent on the view you are dropping.

Tip

You can also drop more than one view via SSMS. Simply select the Views node in Object Explorer and then activate the Object Explorer Details window. The Object Explorer Details window displays all the views and allows you to select multiple views that are displayed. After selecting the views you want to delete, you can right-click a selection and choose Delete to remove all the views selected.

Data Modifications and Views

Data modifications are allowed through a view under certain circumstances. Views that meet these criteria are sometimes called updatable views. Updatable views can be referenced in an INSERT, UPDATE, or DELETE statement, and these statements ultimately affect the underlying table(s) in the view.

The following example contains a SQL statement to create an updatable view, followed by an UPDATE statement that performs a data modification using the view:

CREATE VIEW vw_CreditCard
AS
SELECT     CreditCardID, CardType, CardNumber, ExpMonth, ExpYear
FROM         Sales.CreditCard

UPDATE vw_CreditCard
 SET ExpYear = ExpYear + 1
 WHERE ExpYear < 2006

In general, updatable views are similar to the previous example. The following specific conditions allow a view to be updatable:

• Any data modification via a view must reference columns from a single base table. This does not restrict a view to only one table, but the columns referenced in the data modification can be for only one of the tables defined in the view.

• The columns affected by the data modification must directly reference the underlying tables. They cannot be derived through an aggregate function (for example, AVG, COUNT, SUM) and cannot contain computations from an expression that utilizes columns from another table.

• The TOP clause cannot be part of the SELECT statement that defines the view when the WITH CHECK OPTION clause is used.

• The columns affected by the data modification cannot be affected by GROUP BY, HAVING, or DISTINCT clauses in the view definition.

You can overcome these restrictions by using INSTEAD OF triggers to perform the data modifications. You can create INSTEAD OF triggers on a view, and the logic within the triggers performs the actual database updates.

Partitioned views are another means for performing data modifications via a view. Partitioned views can be updatable and are not subject to all the restrictions listed for conventional views. However, some additional restrictions apply to partitioned views. These additional restrictions and other details about partitioned views are discussed in the next section.

Managing Views with SSMS

You can use the Object Explorer in SQL Server Management Studio to alter or drop views as well. To do so, you right-click a view in the Object Explorer and choose Design to launch the View Designer. The View Designer allows you to modify a view in an easy-to-use graphical interface.

To drop a view, you right-click the view in the Object Explorer and choose Delete. You can drop the view by clicking OK on the Delete Object screen, or you can script the drop statement for later execution.