Обновляемое представление Microsoft SQL Server (MSSQL) с несколькими базовыми таблицами и полной производительностью

Question

Я использую MSSQL 2008 R2. Он имеет удобную функцию обновляемых представлений. Например, если у меня есть один идентификатор таблицы, сопоставляющий имя:

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

а затем еще одна таблица с некоторыми идентификаторами и дополнительной информацией:

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

и для удобства, представление, которое позволяет мне видеть строки из вас, дополненные столбцом имени:

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

тогда я могу теперь делать обновления по имени, а не по id:

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

Самый удобный. Но что произойдет, если я захочу удалить строку для 'fred'?

delete v where name = 'fred' -- Fails

Я получаю ошибку

Представление или функция 'v' не могут быть обновлены, поскольку модификация влияет на несколько базовых таблиц.

Как объяснено в Обновляемом представлении SQL с объединенными таблицами (которое относится к Oracle, но ситуация выглядит так же для MSSQL), вы можете иметь обновляемое представление более чем для одной базовой таблицы, при условии, что существует только одна таблица с сохранением ключей; грубо говоря, это где любая строка из таблицы появляется не более одного раза в представлении. В приведенном выше представлении мы можем видеть, что и t, и u являются таблицами с сохранением ключей. Но мы можем обмануть, изменив определение представления:

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

Это дает те же строки, что и раньше, но теперь позволяет обновления:

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

Семантически верно, что любая строка из t появляется в представлении не более одного раза, но поскольку мы не присоединились к t обычным способом, мы не достигли ограничения на обновление. Кроме того, мы также можем удалить из этого представления:

delete from v where name = 'fred'

Это делает правильно, удаляя из базовой таблицы u, но не из t. Ясно, что с более ранним представлением, выраженным как простое соединение, не было бы никакого способа сказать, должна ли операция 'delete' удалять строку из u или из t (или обоих).

Для многих запросов "select" план выполнения с использованием переписанного представления немного отличается, поэтому я могу ожидать, что в некоторых случаях он будет выполняться немного медленнее. Жаль, что оптимизатор не может увидеть, что (в данном конкретном случае, с существующими уникальными индексами) эти два представления имеют одинаковые данные.

Вы также можете сделать обновляемый вид, используя функцию:

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

Это может привести к другим (и часто более сложным) планам запросов, так что это может быть еще медленнее.

Таким образом, у нас есть два возможных способа сделать обновляемое представление, но они не совсем удовлетворительны. Было бы хорошо, если бы работали операции обновления и удаления, но все же были бы уверены, что представление не будет работать хуже на запросах выбора, чем простое объединение двух таблиц; возможно, есть какая-то подсказка, которую вы можете дать движку запросов. Кто-нибудь может что-то предложить?

Answer 1

(select t.name from t t where t.id = u.id) очень уловка Macgyver, вы обходите ограничение только одной таблицы для удаляемых таблиц.

Одно из решений, которое я могу предложить, - это использовать вместо триггеров, что позволит вам персонализировать то, что оператор удаления будет делать с представлением.

Индивидуальные триггеры могут не влиять на автоматическую оптимизацию представлений.

Некоторые сайты, которые идут гораздо глубже об этом с некоторыми примерами: http://blogs.msdn.com/b/anthonybloesch/archive/2009/02/16/insteadoftriggerspart1.aspx и http://www.mssqltips.com/sqlservertip / 1804 /, используя вместо-о-триггеры-в-SQL-Server-для-DML-операций /