With check option sql что это

Представления

Что такое представления

Во многом представления работают также, как и обычные таблицы. В них можно(правда с определенными ограничениями) вставлять, изменять и удалять данные.

Создание представлений

Общий синтаксис создания представления следующий:

Т.е. для создания представления достаточно написать запрос, который возвращать нужные данные.

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

Следует обратить внимание на то, что представлениям и колонкам в них можно задавать комментарии как и обычным таблицам.

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

With check option sql что это. Смотреть фото With check option sql что это. Смотреть картинку With check option sql что это. Картинка про With check option sql что это. Фото With check option sql что это

При создании представлений можно использовать уже существующие представления:

Следует с осторожностью использовать уже созданные представления при создании других представлений. Может случиться так, что написать новый запрос будет куда лучше, чем использовать существующие, но не полностью подходящие.

Символ * при создании представлений

Когда при создании представления используется символ «*», то Oracle заменяет звездочку на список столбцов. Это означает, что если в таблицу будет добавлена новая колонка, то она не будет автоматически добавлена в представление.

Это очень просто проверить:

Посмотрим, какие данные содержатся в представлении:

With check option sql что это. Смотреть фото With check option sql что это. Смотреть картинку With check option sql что это. Картинка про With check option sql что это. Фото With check option sql что это

Теперь добавим в таблицу tst еще одну колонку( изменение таблиц будет рассматриваться позже, сейчас достаточно понимать, что данный запрос добавляет новую колонку в таблицу):

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

With check option sql что это. Смотреть фото With check option sql что это. Смотреть картинку With check option sql что это. Картинка про With check option sql что это. Фото With check option sql что это

Чтобы добавить колонку «n3» в представление, можно изменить его, добавив в список колонок нужную, либо заново создать(с использованием create or replace ):

Изменение данных представления

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

Представления, которые созданы на основании одной базовой таблицы, можно изменять также, как и обычную таблицу.

Например, создадим представление vdepartments и добавим в него несколько записей.

Конечно, фактически данные добавляются не в представление, а в базовую таблицу(в данном случае departments ):

With check option sql что это. Смотреть фото With check option sql что это. Смотреть картинку With check option sql что это. Картинка про With check option sql что это. Фото With check option sql что это

Строки можно и удалять, а также и изменять:

Посмотрим на результаты:

With check option sql что это. Смотреть фото With check option sql что это. Смотреть картинку With check option sql что это. Картинка про With check option sql что это. Фото With check option sql что это

Представления с проверкой (WITH CHECK OPTION)

Можно создавать представления, которые будут ограничивать изменение данных в базовых таблицах. Для этого используется опция WITH CHECK OPTION при создании представления.

Создадим представление, которое содержит в себе только менеджеров:

Данное представление содержит только менеджеров, но это не означает, что в него нельзя добавить сотрудников других профессий:

Данные в представлении остались те же, что и были:

With check option sql что это. Смотреть фото With check option sql что это. Смотреть картинку With check option sql что это. Картинка про With check option sql что это. Фото With check option sql что это

А вот в таблицу employees был добавлен новый сотрудник Иван Иванов:

With check option sql что это. Смотреть фото With check option sql что это. Смотреть картинку With check option sql что это. Картинка про With check option sql что это. Фото With check option sql что это

Но зато добавить сотрудника с position_id = 1 можно без проблем:

Изменение представлений из нескольких таблиц

В Oracle можно изменять данные через представления, которые получают данные из нескольких таблиц.

Но есть определенные ограничения:

Второй пункт возможно самый важный для понимания того, можно ли изменять данные в представлении из нескольких таблиц или нет.

Так вот, таблица называется key preserved, если каждой ее строке соответствует *максимум одна строка* в представлении.

Следует помнить, что свойство сохранения ключа в представлениях не зависит от данных, а скорее от структуры таблиц и их отношений между собой. Фактически в представлении данные могут выглядеть так, что для одной строки базовой таблицы есть лишь одна строка представления, но это не означает, что этот вид не изменится при изменении данных в таблицах представления.

Посмотрим, какие данные там находятся:

With check option sql что это. Смотреть фото With check option sql что это. Смотреть картинку With check option sql что это. Картинка про With check option sql что это. Фото With check option sql что это

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

Зная, что проблему нужно искать не в самих данных, а в схеме БД, посмотрим, как мы создавали наши таблицы и как выглядит наш запрос в представлении.

Теперь снова попробуем добавить нового сотрудника:

Добавить данные в таблицу departments через это представление не получится:

Ограничения в изменяемых представлениях

Изменения в представлениях возможны не всегда. Есть определенные условия, при которых они запрещены:

Запрет изменения представления

Пересоздадим представление vdepartments и попробуем добавить туда данные:

Источник

With check option sql что это

CREATE VIEW — создать представление

Синтаксис

Описание

CREATE VIEW создаёт представление запроса. Создаваемое представление лишено физической материализации, поэтому указанный запрос будет выполняться при каждом обращении к представлению.

Команда CREATE OR REPLACE VIEW действует подобным образом, но если представление с этим именем уже существует, оно заменяется. Новый запрос должен выдавать те же столбцы, что выдавал запрос, ранее определённый для этого представления (то есть, столбцы с такими же именами должны иметь те же типы данных и следовать в том же порядке), но может добавить несколько новых столбцов в конце списка. Вычисления, в результате которых формируются столбцы представления, могут быть совершенно другими.

Параметры

С таким указанием представление создаётся как временное. Временные представления автоматически удаляются в конце сеанса. Существующее постоянное представление с тем же именем не будет видно в текущем сеансе, пока существует временное, однако к нему можно обратиться, дополнив имя указанием схемы.

Если в определении представления задействованы временные таблицы, представление так же создаётся как временное (вне зависимости от присутствия явного указания TEMPORARY ). RECURSIVE

Создаёт рекурсивное представление. Синтаксис

Для рекурсивного представления обязательно должен задаваться список с именами столбцов. имя

Имя создаваемого представления (возможно, дополненное схемой). имя_столбца

В этом предложении могут задаваться следующие необязательные параметры представления:

Этот параметр может принимать значение local (локально) или cascaded (каскадно) и равнозначен указанию WITH [ CASCADED | LOCAL ] CHECK OPTION (см. ниже). Изменить этот параметр у существующего представления с помощью ALTER VIEW нельзя. security_barrier ( boolean )

Этот параметр следует использовать, если представление должно обеспечивать защиту на уровне строк. За дополнительными подробностями обратитесь к Разделу 39.5.

Это указание управляет поведением автоматически изменяемых представлений. Если оно присутствует, при выполнении операций INSERT и UPDATE с этим представлением будет проверяться, удовлетворяют ли новые строки условию, определяющему представление (то есть, проверяется, будут ли новые строки видны через это представление). Если они не удовлетворяют условию, операция не будет выполнена. Если указание CHECK OPTION отсутствует, команды INSERT и UPDATE смогут создавать в этом представлении строки, которые не будут видны в нём. Поддерживаются следующие варианты проверки:

Новые строки проверяются только по условиям, определённым непосредственно в самом представлении. Любые условия, определённые в нижележащих базовых представлениях, не проверяются (если только в них нет указания CHECK OPTION ). CASCADED

Указание CHECK OPTION нельзя использовать с рекурсивными представлениями.

Замечания

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

Доступ к таблицам, задействованным в представлении, определяется правами владельца представления. В некоторых случаях это позволяет организовать безопасный, но ограниченный доступ к нижележащим таблицам. Однако учтите, что не все представления могут быть защищёнными; за подробностями обратитесь к Разделу 39.5. Функции, вызываемые в представлении, выполняются так, как будто они вызываются непосредственно из запроса, обращающегося к представлению. Поэтому пользователь представления должен иметь все права, необходимые для вызова всех функций, задействованных в представлении.

При выполнении CREATE OR REPLACE VIEW для существующего представления меняется только правило SELECT, определяющее представление. Другие свойства представления, включая владельца, права и правила, кроме SELECT, остаются неизменными. Чтобы изменить определение представления, необходимо быть его владельцем (или членом роли-владельца).

Изменяемые представления

Список FROM в запросе, определяющем представлении, должен содержать ровно один элемент, и это должна быть таблица или другое изменяемое представление.

Список выборки в запросе не должен содержать агрегатные и оконные функции, а также функции, возвращающие множества.

Автоматически обновляемое представление может содержать как изменяемые, так и не изменяемые столбцы. Столбец будет изменяемым, если это простая ссылка на изменяемый столбец нижележащего базового отношения; в противном случае этот столбец будет доступен только для чтения, и если команда INSERT или UPDATE попытается записать значение в него, возникнет ошибка.

Учтите, что пользователь, выполняющий операции добавления, изменения или удаления данных в представлении, должен иметь соответствующие права для этого представления. Кроме того, владелец представления должен иметь сопутствующие права в нижележащих базовых отношениях, хотя пользователь, собственно выполняющий эти операции, может этих прав не иметь (см. Раздел 39.5).

Примеры

Создание представления, содержащего все комедийные фильмы:

Создание представления с указанием LOCAL CHECK OPTION :

Создание представления с указанием CASCADED CHECK OPTION :

Создание представления с изменяемыми и неизменяемыми столбцами:

Создание рекурсивного представления, содержащего числа от 1 до 100:

Совместимость

Источник

SQL — Использование представлений

Дата публикации: 2017-12-11

With check option sql что это. Смотреть фото With check option sql что это. Смотреть картинку With check option sql что это. Картинка про With check option sql что это. Фото With check option sql что это

От автора: представление — это не что иное, как оператор SQL, который хранится в базе данных с соответствующим именем. На самом деле представление является определенным структурированным набором данных таблицы в виде предопределенного SQL-запроса.

Представления SQL могут содержать все строки таблицы или определенные выбранные строки. Представление может быть создано из одной или нескольких таблиц, это зависит от составленного для представления SQL-запроса.

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

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

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

With check option sql что это. Смотреть фото With check option sql что это. Смотреть картинку With check option sql что это. Картинка про With check option sql что это. Фото With check option sql что это

Бесплатный курс по PHP программированию

Освойте курс и узнайте, как создать динамичный сайт на PHP и MySQL с полного нуля, используя модель MVC

В курсе 39 уроков | 15 часов видео | исходники для каждого урока

Обобщать данные из разных таблиц, чтобы использовать их для различных отчетов.

Создание представлений

Представления базы данных создаются с помощью инструкции CREATE VIEW. Представления можно создать из одной таблицы, нескольких таблиц или другого представления.

Пользователь должен иметь соответствующие системные привилегии в зависимости от конкретной реализации.
Основной синтаксис CREATE VIEW следующий:

Источник

Create View

Представление View — это виртуальная таблица, оформленная в виде запроса типа SELECT, который будет подставлен как подзапрос при использовании View.

В отличие от обычных таблиц реляционных СУБД, виртуальная таблица View не является самостоятельной частью набора данных, хранящегося в базе. Содержимое представления формируется динамически на основании данных, находящихся в реальных таблицах. Изменение данных в реальных таблицах отражается в содержимом всех представлений, использующих эти таблицы в своих запросах.

Описание View для СУБД, поддерживающих язык запросов SQL, представляет собой запрос. Поэтому, содержимое представления — это результат выполнения данного запроса. Так, для типичных СУБД типа Oracle, MSSQL, PostgreSQL, Interbase, Firebird, MySQL, Derby представление может содержать :

Создание представления, CREATE VIEW

Для создания представления необходимо использовать команду CREATE VIEW, включающую необязательное наименования схемы (schema_name), наименование представления, необязательные наименования колонок (column_name) ключевое слова AS и далее текст SQL-запроса :

Схема, в которой создается представление VIEW, может отличаться от схемы с таблицей/ами, определенными в запросе. В этом случае необходимо помнить, что пользователи данного представления должны иметь доступ ко всем «представленным» во View объектам. SQL-запрос представления может включать не только таблицы и условия «where», но также и другие представления.

Выражение WITH CHECK OPTION предназначено для предварительного выполнения проверки, если SQL-запрос представления включает определенные условия и используется для внесения изменений в основную таблицу.

Представление можно использовать в тексте SQL-запроса как обычную таблицу.

SQL представление с подзапросами

Представление VIEW может включать подзапросы. В качестве примера рассмотрим задачу определения в компании менеджеров, заказы которых превысили определенную сумму (50000) :

Теперь, если потребуется определить менеджера с самым высоким заказом в определенный период времени, то можно использовать простой запрос с включением в него VIEW :

SQL представление с группировкой данных

Записи в запросе представления VIEW можно сгруппировать, получив, таким образом, групповое представление. В качестве примера можно рассмотреть представление, формирующего статистическую информацию :

Использование данного VIEW позволяет получить всю статистическую информацию по заказам за день :

Представления предоставляют чрезвычайно гибкий и мощный инструмент для определения порядка использования данных. Кроме этого, VIEW позволяют облегчить работу более простым, переформатированием данных.

Удаление представления, DROP View

Синтаксис удаления представления из базы данных включает ключевые слова DROP VIEW. При необходимости нужно указать наименование схемы :

Следует помнить, что для удаления представления нужно иметь соответствующие права.

Изменение значений с помощью VIEW

Команды изменения значений в таблицах базы данных DML (Insert, Update, Delete) можно использовать и с представлениями. Но не все представления позволяют сделать это. Основные критерии, по которым можно сказать, что с помощью данного представления можно модифицировать данные, следующие:

Примеры CREATE VIEW

Проверка значения при модификации

В следующем примере представление делает выборку записей заказов, в которых сумма превышает заданное значение (5000) :

Данное представление ограничивает доступ к определенным заказам в таблице и позволяет модифицировать данные. Для вставки записи необходимо использовать следующий SQL-скрипт :

Данный скрипт будет выполнен и запись будет добавлена в таблицу. Однако с помощью данного представления «order_5000» мы это не увидим, т.к. данная запись не попадет в результирующий набор в виду ограничения на значение поля «total». Чтобы гарантировать выполнение проверки значений перед записью следует в скрипте создания представления использовать ключевое выражение WITH CHECK OPTION, т.е. с опцией проверки.

Вышеупомянутая вставка будет отклонена следующим VIEW :

Необходимо отметить, что выражение WITH CHECK OPTION не делает каскадированного изменения. Оно применяется только в представлениях в которых оно определено, но не в представлениях основанных на этом представлении.

Исключенные поля представления

Необходимо быть аккуратным при использовании представления с условиями для вставки записей. Рассмотрим такое представление, в котором поле страны «country» отсутствует.

Внешне все нормально и представление можно использовать для внесения изменений в записи таблицы. Но вот как быть с «country», которое отсутствует в полях набора? Что будет при вставке записи через данное представление, если «country» не допускает ввода нулевых (NULL) значений? А как вообще управлять этим полем «country» в основной таблице, не создавать же под каждую страну свое представление. Ответа на данные вопросы не последует, они очевидные. Полагаю, что Вы не будете создавать подобные представления, которые будут Вас ограничивать при выполнении определенных транзакций.

Источник

CREATE VIEW (Transact-SQL)

Создает виртуальную таблицу, содержимое которой (столбцы и строки) определяется запросом. Используйте эту инструкцию для создания представления данных, содержащихся в одной или более таблицах базы данных. Например, представление можно использовать в следующих целях.

Для направления, упрощения и настройки восприятия информации в базе данных каждым пользователем.

В качестве механизма безопасности, позволяющего пользователям обращаться к данным через представления, но не предоставляя им разрешений на непосредственный доступ к базовым таблицам.

Для предоставления интерфейса обратной совместимости, моделирующего таблицу, схема которой изменилась.

With check option sql что это. Смотреть фото With check option sql что это. Смотреть картинку With check option sql что это. Картинка про With check option sql что это. Фото With check option 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 указания и . Дополнительные сведения см. в разделе FROM (Transact-SQL) и SELECT (Transact-SQL).

В аргументе 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)

Е. Создание простого представления

В следующем примере создается представление путем выбора некоторых столбцов из исходной таблицы.

Ж. Создание представления путем соединения двух таблиц

Источник

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *