Monday, June 21, 2010

Updateable views – how to use them

This post is first part of miniseries that discusses ways of updating data in views. In this post I will discuss updateable views, in the second part there will be short overview of INSTEAD OF triggers and interesting implications they offer.

Note: All examples in this post are made using AdventureWorks sample database, which you can download from CodePlex page.

With simple views selecting just from single table, with no derived columns the query engine knows how to translate insert or update statement on view to appropriate operation on underlying tables, and no trigger is required. The following script shows an example of such behaviour:

   1: select * from Person.vContact1 where ContactID = 1
   2: update Person.vContact1 set MiddleName = 'S.' where ContactID = 1
   3: select * from Person.vContact1 where ContactID = 1


The query engine translates update statement against the view to an update statement against the underlying table, and if you look at the execution plan of the update statement, you’ll see there is no difference to an update statement against the bare table:


viewupdate1


If the view is more complex, the query engine doesn’t know how to execute the operation and the update fails. There may be several different error messages, depending on the type of the error. For example, if you have calculated column in your view, you cant update this column and obviously you can’t insert rows into the view. The following view returns more or less the same information as the Person.vContact1, but it replaces original value of MiddleName column with custom string:


   1: ALTER view [Person].[vContact2]
   2: as
   3: select ContactID, NameStyle, 
   4: case when Title = 'Mr.' then 'Very long title 1'
   5:     when Title = 'Ms.' then 'Very long title 2'
   6:     else 'Other title' end [Full Title], 
   7: FirstName, MiddleName, LastName, Suffix, EmailAddress, EmailPromotion, Phone, PasswordHash, PasswordSalt, rowguid, ModifiedDate
   8: from Person.Contact


If you try to update the Full Title column, the update will fail:

   1: select * from Person.vContact2 where ContactID = 1
   2: update Person.vContact1 set [Full Title] = 'Some other title' where ContactID = 1
   3: select * from Person.vContact2 where ContactID = 1

Msg 207, Level 16, State 1, Line 2
Invalid column name 'Full Title'.



This is obviously reasonable: Full Title column is calculated at runtime, when the view is executed and cannot be altered in any way because there is no storage related with this column – this column doesn’t exist in the table. You can however update other columns in this view, because they are bound directly to columns in the table.

   1: select * from Person.vContact2 where ContactID = 1
   2: update Person.vContact1 set MiddleName = 'D.' where ContactID = 1
   3: select * from Person.vContact2 where ContactID = 1

In some cases views return data from more than one table. Let’s examine such case. The following script updates City name in one of the tables that comprise the Purchasing.vVendor view:

   1: select * from Purchasing.vVendor where VendorId = 1
   2: select * from Person.Address where AddressLine1 = '683 Larch Ct.'
   3: update Purchasing.vVendor set City = 'Buenos Aires' where VendorId = 1
   4: select * from Person.Address where AddressLine1 = '683 Larch Ct.'
   5: select * from Purchasing.vVendor where VendorId = 1

When you run the above script you’ll notice that query engine was smart enough to update only appropriate row in the Person.Address table. Note also that the view,although joins eight tables, uses inner joins only and the query engine is able to determine exact range of rows to update in each participating table.

There are some other conditions that have to be met to make a view updateable, for example, when you run an update query, you have to modify only columns from one base table at a time. The following query fails:


   1: update Purchasing.vVendor 
   2:     set City = 'Buenos Aires',
   3:     MiddleName = 'M.' 
   4:     where VendorId = 1

Msg 4405, Level 16, State 1, Line 1
View or function 'Purchasing.vVendor' is not updatable because the modification affects multiple base tables.


Other conditions include (after MSDN):
  • The columns being modified in the view must directly reference the underlying data in the table columns. The columns cannot be derived in any other way, such as through the following:

    • An aggregate function: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR, and VARP.
    • A computation. The column cannot be computed from an expression that uses other columns. Columns that are formed by using the set operators UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT amount to a computation and are also not updatable.
  • The columns being modified are not affected by GROUP BY, HAVING, or DISTINCT clauses.
  • TOP is not used anywhere in the select_statement of the view together with the WITH CHECK OPTION clause.
This will conclude first part of the series. In the next post I will write about triggers on views and their possible usage.