Microsoft SQL Server (MSSQL) updatable view with multiple base tables and full performance

Question

I am using MSSQL 2008 R2. It has the handy feature of updatable views. For example if I have one table t mapping id to name:

create table t (id int not null primary key, name varchar(100) not null unique)

and then another table giving some ids and some more information:

create table u (id int not null primary key references t, info varchar(100) not null)

and for convenience, a view that lets me see rows from u augmented with the name column:

create view v as select u.*, t.name from u u join t t on u.id = t.id

then I can now do updates by name, rather than id:

update v set info = 'foo' where name = 'fred'

Most handy. But what happens if I wanted to delete the row for 'fred'?

delete v where name = 'fred' -- Fails

I get the error

View or function 'v' is not updatable because the modification affects multiple base tables.

As explained in SQL Updatable View with joined tables (which refers to Oracle, but the situation appears the same for MSSQL) you can have an updatable view over more than one base table, as long as there is only one key-preserved table; roughly speaking this is where any row from the table appears at most once in the view. In the above view we can see that both t and u are key-preserved tables. But we can cheat by tweaking the view definition:

create view v as
select u.*, (select t.name from t t where t.id = u.id) as name
from u u

This gives the same rows as before, but now allows updates:

update v set info = 'foo' where name = 'fred'

Semantically, it remains true that any row from t appears at most once in the view, but because we have not joined to t in the normal way we don't hit the update restriction. Moreover we can also delete from this view:

delete from v where name = 'fred'

This does the right thing, deleting from the underlying table u but not from t. Clearly, with the earlier view expressed as a simple join, there would be no way to tell whether the 'delete' operation should remove the row from u or from t (or both).

For many 'select' queries, the execution plan using the rewritten view is a bit different, so I might expect it to perform a bit slower in some cases. It is a shame the optimizer isn't able to see that (in this particular case, with the unique indexes that exist) the two views have the same data.

You can also make an updatable view using a function:

create function dbo.get_name(@id int) returns varchar(100) as begin
  declare @r varchar(100)
  select @r = name from t where id = @id
  return @r
end

create view v as select *, dbo.get_name(id) as name from u

This can give still different (and often more complex) query plans, so it might be slower still.

So we have two possible ways to make an updatable view, but they're not entirely satisfactory. It would be nice to have the update and delete operations working but yet be certain that the view won't perform any worse on select queries than the simple join of two tables; perhaps there is some hint you can give to the query engine. Could anyone suggest something?

Answer 1

The (select t.name from t t where t.id = u.id) is a very Macgyver trick, you go around the restriction of only one table for deletable tables.

One solution I can suggest is to use instead of triggers, which will allow you to personalize what the delete statement will do against the view.

Customized triggers might not affect the auto optimization of the views.

Some sites that go a lot deeper about it with some examples: http://blogs.msdn.com/b/anthonybloesch/archive/2009/02/16/insteadoftriggerspart1.aspx and http://www.mssqltips.com/sqlservertip/1804/using-instead-of-triggers-in-sql-server-for-dml-operations/