with schemabinding sql что это
With schemabinding sql что это
Давайте рассмотрим базовый синтаксис создания представления:
Есть две важные опции, которые могут быть использованы при создании представления. Это SCHEMABINDING и ENCRYPTION. Мы рассмотрим каждую из них подробно, но сначала разберем пример создания обычного представления без опций.
Этот скрипт создает представление vwSample. Теперь вместо использования полной команды SELECT можно воспользоваться следующей командой:
SELECT * from vwSample
Пожалуйста, удалите это представление, если вы выполняете мои примеры на своем компьютере, потому что я буду использовать одно и то же имя во всей главе.
DROP VIEW vwSample
Создание представления с опцией SCHEMABINDING блокирует таблицы, на которые ссылается представление и запрещает любые изменения схемы этих таблиц.
Обратите внимание на два важных условия при создании представления с опцией SCHEMABINDING:
Ниже приведен пример представления с опцией SCHEMABINDING:
Эта команда создает представление vwSample. После создания представления попробуйте изменить таблицу CUSTOMERS, это не удастся. Это результат опции SCHEMABINDING. Т.к. vwSample ссылается на таблицу CUSTOMERS, то вы не можете выполнять команду ALTER на таблице CUSTOMERS.
Не забудьте удалить представление.
DROP VIEW vwSample
Эта опция зашифровывает определение представления. Пользователи не смогут просмотреть определение представления после его создания.
Определение представления будет сохранено в зашифрованном формате в системной таблице syscomments.
Внимание: после шифрования определения расшифровать его уже нельзя. Поэтому будьте очень осторожны при использовании опции ENCRYPTION.
Не забудьте удалить представление.
DROP VIEW vwSample
Давайте рассмотрим пример индексированного представления:
Эта команда создает уникальный кластерный индекс для представления.
Не забудьте удалить представление.
DROP VIEW vwSample
Представления могут быть использованы для вставки/обновления и удаления данных из таблицы. Давайте подробно разберем, как это делать. Сначала рассмотрим, как вставить данные в таблицу, используя представление.
Следующий скрипт создаст таблицу TEST и представление vwSample.
Теперь вставим данные в таблицу Test, используя представление. Выполните следующую команду. Она вставит данные в таблицу Test.
Теперь обновим данные, используя то же представление.
Таким же образом мы можем удалить данные из таблицы, используя то же самое представление.
Не забудьте удалить представление.
SQL SERVER 2000 позволяет создавать триггеры на представлениях. Предыдущие версии SQL SERVER не позволяли делать это. Это является новой особенностью SQL SERVER 2000. Но помните, что вы можете создать только триггеры INSTEAD OF на представлениях.
Существуют некоторые ограничения при использовании представлений. Вот они:
Представления и пользовательские функции используются почти для одной и той же цели. Главным отличием является то, что пользовательские функции могут получать параметры, а представления нет. Также результат пользовательской функции может быть напрямую использован в команде SELECT, что невозможно сделать с представлением.
Представления
Создание представления
Представление создается посредством инструкции CREATE VIEW, синтаксис которой выглядит следующим образом:
Инструкция CREATE VIEW должна быть единственной инструкцией пакета. (Это означает, что эту инструкцию следует отделять от других инструкций группы посредством инструкции GO.)
Параметр view_name задает имя определяемого представления, а в параметре column_list указывается список имен, которые будут использоваться в качестве имен столбцов представления. Если этот необязательный параметр опущен, то используются имена столбцов таблиц, по которым создается представление. Параметр select_statement задает инструкция SELECT, которая извлекает строки и столбцы из таблиц (или других представлений). Параметр WITH ENCRYPTION задает шифрование инструкции SELECT, повышая таким образом уровень безопасности системы баз данных.
Любая попытка модифицировать структуру представлений или таблиц, на которые ссылается созданное таким образом представление, будет неудачной. Чтобы такие таблицы или представления можно было модифицировать (инструкцией ALTER) или удалять (инструкцией DROP), нужно удалить это представление или убрать из него предложение SCHEMABINDING.
Когда при создании представления указывается параметр VIEW_METADATA, все его столбцы можно обновлять (за исключением столбцов с типом данных timestamp), если представление имеет триггеры INSERT или UPDATE INSTEAD OF.
Инструкция SELECT в представлении не может содержать предложение ORDER BY или параметр INTO. Кроме этого, по временным таблицам нельзя выполнять запросы.
Представления можно использовать для разных целей:
Для ограничения использования определенных столбцов и/или строк таблиц. Таким образом, представления можно использовать для управления доступом к определенной части одной или нескольких таблиц.
Для скрытия подробностей сложных запросов. Если для приложения базы данных требуются запросы со сложными операциями соединения, создание соответствующих представлений может упростить такие запросы.
Для ограничения вставляемых или обновляемых значений некоторым диапазоном.
В примере ниже показано создание представления:
Запрос в этом примере выбирает из таблицы Works_on строки, удовлетворяющие условию Job=’Консультант’. Представление view_Consultant определяется строками и столбцами, возвращаемыми этим запросом. На рисунке ниже отображена таблица Works_on, в которой строки, выбранные в представлении view_Consultant, выделены красным цветом:
Запрос в этом примере задает выборку строк, т.е. он создает горизонтальное подмножество базовой таблицы Works_on. Возможно также создание представления с ограничениями на включаемые в него столбцы и строки. Создание такого представления показано в примере ниже:
Запрос в этом примере выбирает для включения в представление view_WithoutBudget все столбцы таблицы Project, за исключением столбца Budget.
Как уже упоминалось ранее, в общем формате инструкции CREATE VIEW не обязательно указывать имена столбцов представления. Однако, с другой стороны, в приведенных далее двух случаях обязательно требуется явно указывать имена столбцов:
если столбец представления создается из выражения или агрегатной функции;
если два или больше столбцов представления имеют одинаковое имя в базовой таблице.
В примере ниже показано создание представления, для которого явно указываются имена столбцов:
Здесь имена столбцов представления view_Count должны быть указаны явно по той причине, что инструкция SELECT содержит агрегатную функцию count(*), которая требует, чтобы все столбцы представления были именованы.
Не требуется явно указывать список столбцов в инструкции CREATE VIEW, если применить заголовки столбцов, как это показано в примере ниже:
Представление можно создать из другого представления, как показано в примере:
Представление view_project_p2 в примере ниже создается из представления view_Consultant. Все запросы, использующие представление view_project_p2, преобразовываются в эквивалентные запросы к базовой таблице Works_on.
Представления можно также создавать посредством среды Management Studio. Для этого выберите в обозревателе объектов базу данных, в которой требуется создать представление, щелкните в ней правой кнопкой мыши узел Views и в открывшемся контекстном меню выберите пункт New View. Откроется редактор представлений, в котором можно выполнять следующие действия:
выбрать базовые таблицы и строки в этих таблицах для создания представления;
присвоить представлению имя и определить условия в предложении WHERE соответствующего запроса.
Изменение и удаление представлений
Для изменения определения представления в языке Transact-SQL применяется инструкция ALTER VIEW. Синтаксис этой инструкции аналогичен синтаксису инструкции CREATE VIEW, применяющейся для создания представления.
Использование инструкции ALTER VIEW позволяет избежать переназначения существующих разрешений для представления. Кроме этого, изменение представления посредством этой инструкции не влияет на объекты базы данных, зависящие от этого представления. Если же модифицировать представление, сначала удалив его (инструкция DROP VIEW), а затем создав новое представление с требуемыми свойствами (инструкция CREATE VIEW), то все объекты базы данных, которые ссылаются на это представление, не будут работать должным образом, по крайней мере, в период времени между удалением представления и его воссоздания.
Использование инструкции ALTER VIEW показано в примере ниже:
В этом примере инструкция ALTER VIEW расширяет инструкцию SELECT в представлении view_WithoutBudget новым условием в предложении WHERE.
Инструкция DROP VIEW удаляет из системных таблиц определение указанного в ней представления. Применение этой инструкции показано в примере ниже:
При удалении представления инструкцией DROP VIEW все другие представления, основанные на удаленном, также удаляются, как показано в примере ниже:
Здесь инструкция DROP VIEW явно удаляет представление view_Consultant, при этом неявно удаляя представление view_project_p2, основанное на представлении view_Consultant. Теперь попытка выполнить запрос по представлению view_project_p2 возвратит сообщение об ошибке.
При удалении базовой таблицы представления, основанные на ней другие представления, не удаляются автоматически. Это означает, что все представления для удаленной таблицы нужно удалять явно, используя инструкцию DROP VIEW. С другой стороны, представления удаленной таблицы можно снова использовать на новой таблице, имеющей такую же логическую структуру, как и удаленная.
Создание индексированных представлений
В этой статье описывается, как создавать индексы в представлении. Первым индексом, создаваемым для представления, должен быть уникальный кластеризованный индекс. После создания уникального кластеризованного индекса могут быть созданы некластеризованные индексы. Создание уникального кластеризованного индекса для представления повышает производительность запросов, т. к. представление хранится в базе данных так же, как и таблица с кластеризованным индексом. Оптимизатор запросов может использовать индексированные представления для ускорения выполнения запросов. Чтобы оптимизатор рассматривал представление для подстановки, это представление не обязательно должно быть указано в запросе.
Этапы
Чтобы создать индексированное представление, нужно выполнить следующие шаги. Точность при их выполнении критически важна для успешной реализации индексированного представления.
При выполнении DML 1 для таблицы, на которую ссылается большое количество индексированных представлений либо меньшее количество очень сложных индексированных представлений, эти упоминаемые индексированные представления также потребуется обновить. В результате может значительно снизиться производительность запросов DML, а в некоторых случаях может быть невозможно даже создать план запроса. В таких ситуациях протестируйте запросы DML перед использованием в рабочей среде, проанализируйте план запроса и настройте или упростите инструкцию DML.
1 Например, операции UPDATE, DELETE или INSERT.
Обязательные параметры SET для индексированных представлений
Для правильной поддержки представлений и получения согласованных результатов некоторые параметры SET индексированных представлений должны иметь определенные значения. В приведенных ниже случаях параметрам SET из следующей таблицы нужно присвоить значения, указанные в столбце Обязательное значение :
Значение OLE DB и ODBC
Значение DB-Library
1 Если параметру ANSI_WARNINGS присвоить значение ON, то для параметра ARITHABORT будет неявно задано значение ON.
Настоятельно рекомендуется присвоить пользовательскому параметру ARITHABORT значение ON на всем сервере, как только в какой-либо базе данных сервера будет создано первое индексированное представление или индекс на базе вычисляемого столбца.
Требование детерминированного представления
Даже если выражение детерминировано, если оно содержит выражения с плавающей запятой, результат может зависеть от архитектуры процессора или версии микропрограммы. Для сохранения целостности данных такие выражения могут быть только неключевыми столбцами индексированных представлений. Детерминированные выражения, не содержащие выражений с плавающей запятой, называются точными выражениями. Только точные детерминированные выражения могут содержаться в ключевых столбцах и предложениях WHERE или GROUP BY индексированных представлений.
Дополнительные требования
Кроме требований, касающихся параметров SET и детерминированных функций, должны выполняться следующие требования.
При создании индекса параметр индекса IGNORE_DUP_KEY должен быть установлен в значение OFF (значение по умолчанию).
Функции среды CLR могут быть указаны в списке выбора представления, но не могут быть частью определения ключа кластеризованного индекса. Функции CLR нельзя указывать в представлении в предложении WHERE и предложении ON операции JOIN.
Функции и методы CLR определяемого пользователем типа данных, используемые в определении представления, должны иметь свойства, установленные так, как показано в следующей таблице.
В представлении допустимы ссылки только на базовые таблицы той же самой базы данных. Ссылки на другие представления недопустимы.
Инструкция SELECT в определении представления не должна содержать следующие элементы языка Transact-SQL:
1 Индексированное представление может содержать столбцы типа float, но они не могут включаться в ключ кластеризованного индекса.
Добавление индексированных представлений в темпоральные запросы (запросы, использующие предложение FOR SYSTEM_TIME ) не поддерживается.
Рекомендации по datetime и smalldatetime
При ссылке на строковые литералы datetime и smalldatetime из индексированных представлений рекомендуется явно преобразовывать литерал к нужному типу даты при помощи детерминированного стиля формата даты. Список детерминированных стилей форматирования даты см. в разделе Функции CAST и CONVERT (Transact-SQL). Дополнительные сведения о детерминированных и недетерминированных выражениях см. в разделе Замечания.
Выражения, включающие неявные преобразования символьных строк в типы datetime или smalldatetime, считаются недетерминированными. Дополнительные сведения см. в статье Недетерминированное преобразование строк дат литералов в значения DATE.
Вопросы производительности с индексированными представлениями
Дополнительные замечания
Значение параметра large_value_types_out_of_row столбца в индексированном представлении наследуется от значения соответствующего столбца базовой таблицы. Это значение задается с помощью хранимой процедуры sp_tableoption. Для столбцов, созданных из выражений, установкой по умолчанию является 0. Это означает, что типы больших значений хранятся в строке.
Индексированные представления могут создаваться на секционированной таблице и сами могут быть секционированными.
При удалении представления удаляются также и все его индексы. При удалении кластеризованного индекса удаляются все некластеризованные индексы и автоматически созданные для представления статистики. Статистики, созданные пользователем, сохраняются. Некластеризованные индексы могут удаляться по отдельности. При удалении кластеризованного индекса представления удаляется сохраненный результирующий набор, и оптимизатор снова начинает работать с ним, как с обычным представлением.
Индексы таблиц и представлений могут быть отключены. При отключении кластеризованного индекса таблицы индексы представлений, связанных с ней, также отключаются.
Permissions
Чтобы создать представление, пользователю требуется разрешение CREATE VIEW в отношении базы данных и разрешение ALTER в отношении схемы, в которой создается представление. Если базовая таблица находится в другой схеме, для нее требуется как минимум разрешение REFERENCES. Если пользователь, создающий индекс, отличается от пользователей, создавших представление, для создания индекса требуется только разрешение ALTER для представления (охватываемое ALTER в схеме).
Индексы могут создаваться только для представлений, имеющих того же владельца, что и таблицы, на которые выполняется ссылка. Это также называется неизменной цепочкой владения между представлением и таблицами. Как правило, если таблица и представление находятся в одной и той же схеме, ко всем объектам в схеме применяется один и тот же владелец. Поэтому можно создать представление и не быть владельцем представления. С другой стороны, также возможно, что отдельные объекты в схеме имеют разных явных владельцев. Если владелец отличается от владельца схемы, в столбце principal_id в sys.tables будет содержаться соответствующее значение.
Создание индексированного представления: пример для T-SQL
В следующем примере создаются представление и индекс этого представления. Включено два запроса, использующих созданное индексированное представление в базе данных AdventureWorks.
Дополнительные сведения см. в статье CREATE VIEW (Transact-SQL).
CREATE VIEW (Transact-SQL)
Создает виртуальную таблицу, содержимое которой (столбцы и строки) определяется запросом. Используйте эту инструкцию для создания представления данных, содержащихся в одной или более таблицах базы данных. Например, представление можно использовать в следующих целях.
Для направления, упрощения и настройки восприятия информации в базе данных каждым пользователем.
В качестве механизма безопасности, позволяющего пользователям обращаться к данным через представления, но не предоставляя им разрешений на непосредственный доступ к базовым таблицам.
Для предоставления интерфейса обратной совместимости, моделирующего таблицу, схема которой изменилась.
Синтаксические обозначения в Transact-SQL
Синтаксис
Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.
Аргументы
OR ALTER
Применимо к: База данных SQL Azure и SQL Server (начиная с SQL Server 2016 (13.x); с пакетом обновления 1 (SP1)).
Условно изменяет представление только в том случае, если оно уже существует.
schema_name
Имя схемы, которой принадлежит представление.
view_name
Имя представления. Имена представлений должны соответствовать требованиям, предъявляемым к идентификаторам. Указывать имя владельца представления не обязательно.
column
Имя, которое будет иметь столбец в представлении. Имя столбца требуется только в тех случаях, когда столбец формируется на основе арифметического выражения, функции или константы, если два или более столбцов могут по иной причине получить одинаковые имена (как правило, в результате соединения) или если столбцу представления назначается имя, отличное от имени столбца, от которого он произведен. Назначать столбцам имена можно также в инструкции SELECT.
Если аргумент column не указан, столбцам представления назначаются такие же имена, которые имеют столбцы в инструкции SELECT.
В столбцах представления разрешения для имени столбца применяются с инструкцией CREATE VIEW или ALTER VIEW вне зависимости от источника базовых данных. Например, если в инструкции CREATE VIEW были заданы разрешения для столбца SalesOrderID, инструкция ALTER VIEW может переименовать столбец SalesOrderID, например в OrderRef, и все же иметь разрешения, связанные с представлением, в котором используется столбец SalesOrderID.
Определяет действия, которые должны быть выполнены в представлении.
select_statement
Инструкция SELECT, которая определяет представление. В этой инструкции можно указывать более одной таблицы и другие представления. Для выбора объектов, указанных в предложении SELECT создаваемого представления, необходимы соответствующие разрешения.
Представление не обязательно является простым подмножеством строк и столбцов одной конкретной таблицы. С помощью предложения SELECT можно создавать представление, использующее более одной таблицы, или другие представления любой степени сложности.
При использовании в определении индексированного представления инструкция SELECT должна содержать указание одной таблицы или соединять инструкцией JOIN несколько таблиц с необязательной статистической обработкой.
Предложения SELECT, используемые в определении представления, не могут включать следующие элементы:
предложение ORDER BY, если только в списке выбора инструкции SELECT нет также предложения TOP;
Предложение ORDER BY используется исключительно для определения строк, возвращаемых предложениями TOP или OFFSET в определении представления. Предложение ORDER BY не гарантирует упорядочивания результатов при запросе к представлению, если оно не указано в самом запросе.
ключевое слово INTO;
ссылку на временную таблицу или табличную переменную.
Так как аргумент select_statement использует инструкцию SELECT, допустимо включать в состав предложения FROM указания и
В аргументе select_statement можно использовать функции и множественные инструкции SELECT, разделенные оператором UNION или UNION ALL.
CHECK OPTION
Обеспечивает соответствие всех выполняемых для представления инструкций, изменяющих данные, критериям, заданным в выражении select_statement. Если строка изменяется посредством представления, предложение WITH CHECK OPTION гарантирует, что после фиксации изменений доступ к данным из представления сохранится.
CHECK OPTION применяется только к обновлениям, выполненным через представление. Он неприменим к обновлениям, выполненным непосредственно в базовых таблицах представления.
ENCRYPTION
Применимо к: SQL Server 2008 и выше, а также База данных SQL Azure.
Выполняет шифрование элементов представления sys.syscomments, содержащих текст инструкции CREATE VIEW. Использование предложения WITH ENCRYPTION предотвращает публикацию представления в рамках репликации SQL Server.
SCHEMABINDING
Привязывает представление к схеме базовой таблицы или таблиц. Если аргумент SCHEMABINDING указан, нельзя изменить базовую таблицу или таблицы таким способом, который может повлиять на определение представления. Сначала нужно изменить или удалить само представление для сброса зависимостей от таблицы, которую требуется изменить. При использовании аргумента SCHEMABINDING инструкция select_statement должна включать двухкомпонентные (schema . object) имена таблиц, представлений или пользовательских функций, упоминаемых в предложении. Все указанные в инструкции объекты должны находиться в одной базе данных.
Представления или таблицы, входящие в представление, созданное при помощи предложения SCHEMABINDING, не могут быть сброшены, пока это представление не будет удалено или изменено таким образом, чтобы оно более не было привязано к схеме. В противном случае компонент Компонент Database Engine выдаст ошибку. Кроме того, выполнение инструкций ALTER TABLE для таблиц, которые входят в представления, привязанные к схемам, завершается ошибкой, если эти инструкции влияют на определение представления.
В настоящее время представления в Azure Synapse Analytics не поддерживают привязку схем. Дополнительные сведения см. в статье Представления T-SQL с выделенным пулом SQL и бессерверным пулом SQL в Azure Synapse Analytics.
VIEW_METADATA
Указывает, что экземпляр SQL Server возвратит в API-интерфейсы DB-Library, ODBC и OLE DB сведения метаданных о представлении вместо базовой таблицы или таблиц, когда метаданные режима обзора затребованы для запроса, который ссылается на представление. Метаданные режима обзора — это дополнительные метаданные, которые экземпляр SQL Server возвращает вышеназванным клиентским API-интерфейсам. Эти метаданные позволяют клиентским API-интерфейсам реализовывать обновляемые клиентские курсоры. Метаданные режима обзора содержат сведения о базовой таблице, которой принадлежат столбцы в результирующем наборе.
Для представлений, созданных с применением предложения VIEW_METADATA, метаданные режима обзора возвращают имя представления, а не имена базовых таблиц при описании столбцов из представления в результирующем наборе.
В представлении, созданном с предложением WITH VIEW_METADATA, все столбцы, за исключением столбца timestamp, поддерживают обновление, если представление включает триггеры INSTEAD OF INSERT или INSTEAD OF UPDATE. Дополнительные сведения об обновляемых представлениях см. в разделе «Примечания».
Remarks
Представление может быть создано только в текущей базе данных. Инструкция CREATE VIEW должна быть первой в пакетном запросе. Представление может включать не более 1 024 столбцов.
При выполнении запросов через представление компонент Компонент Database Engine проверяет, существуют ли все указанные в инструкции объекты базы данных, верны ли они в контексте инструкции и соответствуют ли инструкции модификации данных правилам обеспечения целостности данных. Если проверка завершается ошибкой, возвращается сообщение об ошибке. При успешной проверке операция преобразуется в операцию над базовой таблицей или таблицами.
Если представление зависит от удаленной таблицы или представления, компонент Компонент Database Engine в ответ на попытку использования представления возвращает сообщение об ошибке. Если создана новая таблица или представление, а структура таблицы не изменилась по сравнению с предыдущей базовой таблицей для замены удаленной, то представление можно использовать снова. Если структура новой таблицы или представления отличается от предыдущей, представление нужно удалить и создать заново.
Если представление создано без применения предложения SCHEMABINDING, то при изменении базовых объектов, влияющих на определение представления, выполните хранимую процедуру sp_refreshview. В противном случае результат запроса представления может быть непредвиденным.
При создании представления сведения о нем сохраняются в следующих представлениях каталога: sys.views, sys.columns и sys.sql_expression_dependencies. Текст инструкции CREATE VIEW сохраняется в представлении каталога sys.sql_modules.
Запрос, в котором используется индекс представления, определенного с выражением типа numeric или float, может привести к результатам, отличным от результатов подобного запроса, в котором не используется индекс представления. Это отличие может быть обусловлено ошибками округления при выполнении запросов INSERT, DELETE или UPDATE для базовых таблиц.
При создании представления компонент Компонент Database Engine сохраняет значения SET QUOTED_IDENTIFIER и SET ANSI_NULLS. Эти исходные значения используются для синтаксического анализа данных представления при обращениях к нему. Таким образом, при доступе к представлению какие-либо заданные во время клиентского сеанса значения SET QUOTED_IDENTIFIER и SET ANSI_NULLS не влияют на определение представления.
Обновляемые представления
Можно изменять данные базовой таблицы через представление до тех пор, пока выполняются следующие условия:
Любые изменения, в том числе инструкции UPDATE, INSERT и DELETE, должны ссылаться на столбцы только одной базовой таблицы.
Изменяемые в представлении столбцы должны непосредственно ссылаться на данные столбцов базовой таблицы. Столбцы нельзя сформировать каким-либо другим образом, в том числе:
с помощью агрегатной функции: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR и VARP;
на основе вычисления. Столбец нельзя вычислить по выражению, включающему другие столбцы. Столбцы, сформированные при помощи операторов UNION, UNION ALL, CROSSJOIN, EXCEPT и INTERSECT, считаются вычисляемыми и также не являются обновляемыми.
Предложения GROUP BY, HAVING и DISTINCT не влияют на изменяемые столбцы.
Предложение TOP не используется нигде в инструкции select_statement представления вместе с предложением WITH CHECK OPTION.
Вышеназванные ограничения относятся ко всем подзапросам представления в предложении FROM, равно как и к самому представлению. Как правило, компонент Компонент Database Engine должен иметь возможность однозначно трассировать изменения от определения представления до одной базовой таблицы. Дополнительные сведения см. в разделе Изменение данных через представление.
Если вышеуказанные ограничения не позволяют изменить данные через представление напрямую, рассмотрите следующие варианты.
Триггеры INSTEAD OF
Чтобы сделать представление обновляемым, для него можно создать триггеры INSTEAD OF. Триггер INSTEAD OF выполняется вместо инструкции модификации данных, для которой он определен. Этот триггер позволяет пользователю указать набор действий, которые должны быть выполнены для обработки инструкции модификации данных. Таким образом, если для представления создан триггер INSTEAD OF, связанный с конкретной инструкцией модификации данных (INSERT, UPDATE или DELETE), соответствующее представление можно обновлять при помощи этой инструкции. Дополнительные сведения о триггерах INSTEAD OF см. в разделе Триггеры DML.
Секционированные представления
Секционированное представление является в то же время и обновляемым, но при этом действуют некоторые ограничения. При необходимости компонент Компонент Database Engine проводит различие между локальными и распределенными секционированными представлениями. Первый тип включает представления, которые вместе со всеми соответствующими таблицами относятся к одному экземпляру SQL Server, а второй — представления, в которых хотя бы одна из таблиц относится к другому или удаленному серверу.
Секционированные представления
Секционированное представление — это представление, определенное посредством объединения всех (UNION ALL) таблиц-элементов, структурированных одинаковым образом, но хранимых отдельно в форме разных таблиц либо в одном экземпляре SQL Server, либо в группе автономных экземпляров SQL Server, которые называются федеративными серверами баз данных.
Предпочтительным способом локального секционирования данных на один сервер является применение секционированных таблиц. Дополнительные сведения см. в разделе Partitioned Tables and Indexes.
Секционированное представление на сервере Server1 определяется следующим образом.
Как правило, представление считают секционированным, если оно соответствует следующему формату:
Требования к созданию секционированных представлений
В списке столбцов определения представления выберите все столбцы таблиц-элементов.
В следующем фрагменте продемонстрированы правильные наборы ограничений:
Один столбец не может быть указан в списке выбора несколько раз.
Столбец секционирования является частью первичного ключа (PRIMARY KEY) таблицы.
Он не может быть вычисляемым столбцом, столбцом идентификаторов, столбцом по умолчанию или столбцом типа timestamp.
Если для одного столбца таблицы-элемента определено более одного ограничения, ядро СУБД пропускает все ограничения и не учитывает их при определении того, является ли представление секционированным. Чтобы выполнялись требования к секционированному представлению, со столбцом секционирования должно быть связано только одно ограничение секционирования.
На возможность обновления столбца секционирования никакие ограничения не распространяются.
Эти таблицы могут быть или локальными таблицами, или таблицами с других компьютеров, на которых выполняется SQL Server. Во втором случае для ссылки на таблицу должно быть использовано или четырехкомпонентное имя, или имя в формате функции OPENDATASOURCE или OPENROWSET. Синтаксис функций OPENDATASOURCE и OPENROWSET позволяет указать имя таблицы, но не передаваемого запроса. Дополнительные сведения см. в разделе OPENDATASOURCE (Transact-SQL) и OPENROWSET (Transact-SQL).
Если хотя бы одна таблица-элемент является удаленной, представление называется распределенным секционированным представлением, и тогда вступают в силу дополнительные требования. Они описаны ниже в данном разделе.
Одна таблица не может быть указана два раза в наборе таблиц, объединяемых при помощи инструкции UNION ALL.
Таблицы-элементы не могут иметь индексы, созданные для вычисляемых столбцов в таблице.
Все ограничения первичного ключа (PRIMARY KEY), действующие в таблицах-элементах, должны быть связаны с одинаковым количеством столбцов.
Всем таблицам-элементам в представлении должно быть назначено одинаковое значение заполнения ANSI. Его можно задать либо при помощи аргумента user options процедуры sp_configure, либо при помощи инструкции SET.
Условия изменения данных в секционированных представлениях
На инструкции, изменяющие секционированные представления, распространяются следующие ограничения:
В инструкции INSERT должны быть указаны значения для всех столбцов представления, даже если в базовых таблицах-элементах действует ограничение DEFAULT для этих столбцов или они допускают значения NULL. Для тех столбцов таблиц-элементов, которые имеют определения DEFAULT, в инструкциях нельзя явно использовать ключевое слово DEFAULT.
Значение, вставляемое в столбец секционирования, должно отвечать по крайней мере одному из базовых ограничений. В противном случае операция вставки завершится ошибкой из-за нарушения ограничений.
В предложении SET инструкции UPDATE в качестве значения не может быть указано ключевое слово DEFAULT, даже если столбец имеет значение DEFAULT, определенное в соответствующей таблице-элементе.
Столбец представления, который является столбцом идентификаторов в одной или нескольких таблицах-элементах, не может быть изменен при помощи инструкции INSERT или UPDATE.
Если одна из таблиц-элементов содержит столбец timestamp, представление не может быть изменено при помощи инструкции INSERT или UPDATE.
Если одна из таблиц-элементов содержит триггер, ограничение ON UPDATE CASCADE/SET NULL/SET DEFAULT или ограничение ON DELETE CASCADE/SET NULL/SET DEFAULT, то представление не может быть изменено.
Выполнение операций INSERT, UPDATE и DELETE для секционированного представления не допускается, если осуществляется самосоединение с тем же представлением или с какой-либо из таблиц-элементов, указанных в инструкции.
Массовый импорт данных в секционированное представление не поддерживается bcp и инструкциями BULK INSERT и INSERT. SELECT * FROM OPENROWSET(BULK. ). Однако можно вставить в секционированное представление несколько строк с помощью инструкции INSERT.
Для обновления секционированного представления пользователь должен иметь связанные с таблицами-элементами разрешения INSERT, UPDATE и DELETE.
Дополнительные требования, предъявляемые к распределенным секционированным представлениям
При работе с распределенными секционированными представлениями (если одна или несколько таблиц-элементов являются удаленными) действуют следующие дополнительные требования.
Для обеспечения атомарности операций на всех узлах, затрагиваемых операцией обновления, запускается распределенная транзакция.
Чтобы инструкция INSERT, UPDATE или DELETE была выполнена успешно, установите параметр XACT_ABORT SET в значение ON.
Любые столбцы удаленных таблиц типа smallmoney, фигурирующих в секционированном представлении, сопоставляются как тип money. Таким образом, соответствующие им столбцы локальных таблиц (столбцы, занимающие те же порядковые позиции в списке выбора) должны также иметь тип money.
При уровне совместимости базы данных 110 и выше любые столбцы типа smalldatetime в удаленных таблицах, фигурирующих в секционированном представлении, сопоставляются как тип smalldatetime. Соответствующие им столбцы локальных таблиц (столбцы, занимающие те же порядковые позиции в списке выбора) должны иметь тип smalldatetime. В этом отличие от более ранних версий SQL Server, где столбцы в удаленных таблицах типа smalldatetime, фигурирующих в секционированном представлении, сопоставляются как тип datetime, а соответствующие столбцы в локальных таблицах должны иметь тип datetime. Дополнительные сведения см. в разделе Уровень совместимости инструкции ALTER DATABASE (Transact-SQL).
Никакой связанный сервер в секционированном представлении не может быть замкнут на себя. Это связанный сервер, указывающий на тот же экземпляр SQL Server.
При выполнении операций INSERT, UPDATE и DELETE, в которых задействованы обновляемые секционированные представления и удаленные таблицы, параметр SET ROWCOUNT не учитывается.
При наличии таблиц-элементов и определения секционированного представления оптимизатор запросов SQL Server составляет планы эффективного выполнения запросов для доступа к данным из таблиц-элементов. При наличии определений ограничения CHECK обработчик запросов составляет карту распределения значений ключей по таблицам-элементам. Когда пользователь выполняет запрос, обработчик запросов сравнивает карту со значениями, указанными в предложении WHERE, и создает план выполнения, позволяющий свести к минимуму объем передачи данных между серверами-элементами. Следовательно, несмотря на то, что некоторые таблицы-элементы могут храниться на удаленных серверах, экземпляр SQL Server разрешает распределенные запросы таким образом, чтобы объем передаваемых распределенных данных оказался минимальным.
Аспекты, связанные с репликацией
При создании секционированных представлений для таблиц-элементов, задействованных в репликации, следует учитывать следующие факторы.
Если базовые таблицы задействованы в репликации слиянием или репликации транзакций с обновляемыми подписками, в список выбора должен быть включен столбец uniqueidentifier.
При выполнении любых операций INSERT в секционированном представлении необходимо предоставлять значение NEWID() для столбца uniqueidentifier. При выполнении любых операций UPDATE для столбца uniqueidentifier необходимо предоставлять значение NEWID(), так как ключевое слово DEFAULT использовать при этом нельзя.
Репликация обновлений, производимых при помощи представления, выполняется так же, как и при репликации таблиц в разных базах данных: таблицы обслуживаются различными агентами репликации, и определенный порядок выполнения обновлений не гарантируется.
Разрешения
Для выполнения этой инструкции требуется разрешение CREATE VIEW в отношении базы данных и разрешение ALTER в отношении схемы, в которой создается представление.
Примеры
В следующих примерах используются базы данных AdventureWorks 2012 и AdventureWorksDW.
A. Использование простого разрешения CREATE VIEW
Б. Использование WITH ENCRYPTION
Следующий пример поясняет применение параметра WITH ENCRYPTION и обращение к вычисляемым, переименованным и множественным столбцам.
Применимо к: SQL Server 2008 и выше, а также База данных SQL.
В. Использование WITH CHECK OPTION
Г. Использование встроенных функций в представлении
В следующем фрагменте показано определение представления, включающее встроенную функцию. Применяя функцию, следует указывать имя производного столбца.
Д. Использование секционированных данных
Примеры: Azure Synapse Analytics и Система платформы аналитики (PDW)
Е. Создание простого представления
В следующем примере создается представление путем выбора некоторых столбцов из исходной таблицы.