Представления SQL Server могут стать действительно странными при обновлении базовой таблицы. Я покажу несколько примеров, а также то, что нужно сделать для представлений, чтобы избежать проблем.
Проблема с разбитыми представлениями — это то, что я обнаружил, работая над производственной системой. Когда я впервые увидел это, я был в шоке. Когда я углубился в это, я был еще более потрясен чистым злом, которое могло вызвать это поведение. Но давайте начнем с простой таблицы цветов и вида сверху.
CREATE TABLE [dbo].[Colours] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[Colour] NVARCHAR (10) NOT NULL,
CONSTRAINT [PK_Colours] PRIMARY KEY CLUSTERED ([Id] ASC)
);
GO
CREATE VIEW [dbo].[BadView]
AS SELECT * FROM Colours
Заполните таблицу некоторыми данными и протестируйте представление.
SELECT * FROM BadView Id Colour ----------- ---------- 1 Red 2 Green 3 Blue 4 White 5 Black
Теперь давайте разберемся с этим видом. Я воссоздаю таблицу с другим столбцом, RGBкоторый вставляется сразу после Idстолбца. ColourСтолбец переименовывается ColourName. Перед запуском следующего теста я вставил соответствующие значения RGB в новый столбец.
SELECT * FROM BadView Id Colour ----------- ------ 1 FF0000 2 00FF00 3 0000FF 4 FFFFFF 5 000000
Это то, что я считаю странным поведением. Вывод представления по-прежнему содержит два столбца с именами Idи Colour. Это было бы хорошо — если бы он содержал правильные данные. Это не так. Данные взяты из второго столбца базовой таблицы. Если вы недостаточно напуганы этим, у меня есть кое-что еще, что еще хуже показать…
Когда обнаружены неправильно выровненные имена столбцов и данные, они, конечно, должны быть обработаны. Все проблемы могут быть решены по-разному. Одним из способов, которые я нашел в производственной системе, было предложение select, подобное этому.
SELECT Id, Colour AS RGB FROM BadView
Оно работает. Он производит вывод с правильным именем столбца для данных. Мои чувства и личные мысли о таком подходе не подходят для распечатки.
Id RGB ----------- ------ 1 FF0000 2 00FF00 3 0000FF 4 FFFFFF 5 000000
В этот момент было совершенно очевидно, что система хрупкая, и что мне придется быть предельно осторожным во время необходимой очистки. Выписывание существующего определения представления и повторное его выполнение должно быть безопасным. Сценарии объекта базы данных — это способ получить текущее определение его, чтобы иметь возможность восстановить его. Это неоперация, верно. ВЕРНО? Так вот что я сделал (на резервной копии БД).
ALTER VIEW [dbo].[BadView] AS SELECT * FROM Colours
Затем я еще раз проверил вид.
SELECT * FROM BadView Id RGB ColourName ----------- ------ ---------- 1 FF0000 Red 2 00FF00 Green 3 0000FF Blue 4 FFFFFF White 5 000000 Black
Я теперь был не только напуган. Я был в ужасе. База данных не просто показывает действительно странное поведение. Он хранит информацию о представлении, которая не включена в сценарий определения объекта. Воссоздание его с точно таким же сценарием не даст тот же результат.
Для меня это ужасная история. Идя по дороге устаревшего кода на пути к адскому обслуживанию.
Время взглянуть на лекарство от этой странности.
Схема лечения
Когда я создал, BadViewя также создал, GoodViewчто применяется WITH SCHEMABINDING. Когда представление привязано к схеме, все объекты (таблицы, функции), от которых зависит представление, блокируются для модификации.
Сначала я попытался создать так GoodViewже, как BadView.
CREATE VIEW [dbo].[GoodView] WITH SCHEMABINDING AS SELECT * FROM Colours
Это не удалось.
Msg 1054, Level 15, State 6, Procedure GoodView, Line 3 Syntax '*' is not allowed in schema-bound objects.
При связывании схемы SQL-серверу требуется более конкретный и безопасный оператор выбора.
CREATE VIEW [dbo].[GoodView] WITH SCHEMABINDING AS SELECT Id, Colour FROM dbo.Colours
При выполнении переименования Colourстолбца ColourNameоперация переименования завершается неудачно.
Msg 5074, Level 16, State 1, Line 3 The object 'GoodView' is dependent on column 'ColourName'. Msg 4922, Level 16, State 9, Line 3 ALTER TABLE DROP COLUMN ColourName failed because one or more objects access this column.
Сообщение от самого движка SQL-сервера. Представление схемы защищено от несанкционированного изменения базовой таблицы независимо от того, какой инструмент клиента используется.
Требуется поддержка инструментов
Я нашел статью: SCHEMABINDING — использовать или не использовать, которая обсуждает последствия привязки схемы. Я думаю, что пункты верны, но при этом не упоминается, что хорошая инструментальная поддержка может справиться с проблемами, автоматически отбрасывая и воссоздавая любые представления, связанные со схемой с изменяемой таблицей. Поддержка развертывания базы данных в Visual Studio 2010 и 2012 (значительно улучшенная в 2012 году) является примером инструмента, который справляется с этим.
Как насчет хранимых процедур?
Пока этот пост был о взглядах. Вероятно, более распространенными, чем представления являются хранимые процедуры. По крайней мере, они не используют устаревшие определения столбцов.
CREATE PROCEDURE [dbo].[MyProcedure] AS SELECT * FROM Colours
Когда Coloursтаблица обновляется, выходные данные хранимой процедуры корректируются для включения нового столбца и нового имени переименованного столбца.
рекомендации
Моя рекомендация заключается в том, чтобы схема связывала все представления по умолчанию. Если становится трудно изменить базовые таблицы при наличии нескольких связанных схем схем, необходим более качественный инструмент. Удаление привязки схемы не является хорошим решением. Я предпочитаю представления над хранимыми процедурами для простых запросов, так как они могут быть повторно использованы в других запросах. Раньше было повышение производительности для хранимых процедур, но в современных версиях SQL-сервера все запросы компилируются и план запроса кэшируется.