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)
Creates a virtual table whose contents (columns and rows) are defined by a query. Use this statement to create a view of the data in one or more tables in the database. For example, a view can be used for the following purposes:
To focus, simplify, and customize the perception each user has of the database.
As a security mechanism by allowing users to access data through the view, without granting the users permissions to directly access the underlying base tables.
To provide a backward compatible interface to emulate a table whose schema has changed.
Transact-SQL Syntax Conventions
Syntax
To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.
Arguments
OR ALTER
Applies to: Azure SQL Database and SQL Server (starting with SQL Server 2016 (13.x) SP1).
Conditionally alters the view only if it already exists.
schema_name
Is the name of the schema to which the view belongs.
view_name
Is the name of the view. View names must follow the rules for identifiers. Specifying the view owner name is optional.
column
Is the name to be used for a column in a view. A column name is required only when a column is derived from an arithmetic expression, a function, or a constant; when two or more columns may otherwise have the same name, typically because of a join; or when a column in a view is specified a name different from that of the column from which it is derived. Column names can also be assigned in the SELECT statement.
If column is not specified, the view columns acquire the same names as the columns in the SELECT statement.
In the columns for the view, the permissions for a column name apply across a CREATE VIEW or ALTER VIEW statement, regardless of the source of the underlying data. For example, if permissions are granted on the SalesOrderID column in a CREATE VIEW statement, an ALTER VIEW statement can name the SalesOrderID column with a different column name, such as OrderRef, and still have the permissions associated with the view using SalesOrderID.
Specifies the actions the view is to perform.
select_statement
Is the SELECT statement that defines the view. The statement can use more than one table and other views. Appropriate permissions are required to select from the objects referenced in the SELECT clause of the view that is created.
A view does not have to be a simple subset of the rows and columns of one particular table. A view can be created that uses more than one table or other views with a SELECT clause of any complexity.
In an indexed view definition, the SELECT statement must be a single table statement or a multitable JOIN with optional aggregation.
The SELECT clauses in a view definition cannot include the following:
An ORDER BY clause, unless there is also a TOP clause in the select list of the SELECT statement
The ORDER BY clause is used only to determine the rows that are returned by the TOP or OFFSET clause in the view definition. The ORDER BY clause does not guarantee ordered results when the view is queried, unless ORDER BY is also specified in the query itself.
A reference to a temporary table or a table variable.
Because select_statement uses the SELECT statement, it is valid to use and
Functions and multiple SELECT statements separated by UNION or UNION ALL can be used in select_statement.
CHECK OPTION
Forces all data modification statements executed against the view to follow the criteria set within select_statement. When a row is modified through a view, the WITH CHECK OPTION makes sure the data remains visible through the view after the modification is committed.
The CHECK OPTION only applies to updates made through the view. It has no applicability to any updates performed directly to a view’s underlying tables.
ENCRYPTION
Applies to: SQL Server 2008 and later and Azure SQL Database.
Encrypts the entries in sys.syscomments that contain the text of the CREATE VIEW statement. Using WITH ENCRYPTION prevents the view from being published as part of SQL Server replication.
SCHEMABINDING
Binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition. The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified. When you use SCHEMABINDING, the select_statement must include the two-part names (schema.object) of tables, views, or user-defined functions that are referenced. All referenced objects must be in the same database.
Views or tables that participate in a view created with the SCHEMABINDING clause cannot be dropped unless that view is dropped or changed so that it no longer has schema binding. Otherwise, the Database Engine raises an error. Also, executing ALTER TABLE statements on tables that participate in views that have schema binding fail when these statements affect the view definition.
In Azure Synapse Analytics, views currently do not support schema binding. For more information, see T-SQL views with dedicated SQL pool and serverless SQL pool in Azure Synapse Analytics.
VIEW_METADATA
Specifies that the instance of SQL Server will return to the DB-Library, ODBC, and OLE DB APIs the metadata information about the view, instead of the base table or tables, when browse-mode metadata is being requested for a query that references the view. Browse-mode metadata is additional metadata that the instance of SQL Server returns to these client-side APIs. This metadata enables the client-side APIs to implement updatable client-side cursors. Browse-mode metadata includes information about the base table that the columns in the result set belong to.
For views created with VIEW_METADATA, the browse-mode metadata returns the view name and not the base table names when it describes columns from the view in the result set.
When a view is created by using WITH VIEW_METADATA, all its columns, except a timestamp column, are updatable if the view has INSTEAD OF INSERT or INSTEAD OF UPDATE triggers. For more information about updatable views, see Remarks.
Remarks
A view can be created only in the current database. The CREATE VIEW must be the first statement in a query batch. A view can have a maximum of 1,024 columns.
When querying through a view, the Database Engine checks to make sure that all the database objects referenced anywhere in the statement exist and that they are valid in the context of the statement, and that data modification statements do not violate any data integrity rules. A check that fails returns an error message. A successful check translates the action into an action against the underlying table or tables.
If a view depends on a table or view that was dropped, the Database Engine produces an error message when anyone tries to use the view. If a new table or view is created and the table structure does not change from the previous base table to replace the one dropped, the view again becomes usable. If the new table or view structure changes, the view must be dropped and re-created.
If a view is not created with the SCHEMABINDING clause, run sp_refreshview when changes are made to the objects underlying the view that affect the definition of the view. Otherwise, the view might produce unexpected results when it is queried.
When a view is created, information about the view is stored in the following catalog views: sys.views, sys.columns, and sys.sql_expression_dependencies. The text of the CREATE VIEW statement is stored in the sys.sql_modules catalog view.
A query that uses an index on a view defined with numeric or float expressions may have a result that is different from a similar query that does not use the index on the view. This difference may be caused by rounding errors during INSERT, DELETE, or UPDATE actions on underlying tables.
The Database Engine saves the settings of SET QUOTED_IDENTIFIER and SET ANSI_NULLS when a view is created. These original settings are used to parse the view when the view is used. Therefore, any client-session settings for SET QUOTED_IDENTIFIER and SET ANSI_NULLS do not affect the view definition when the view is accessed.
Updatable Views
You can modify the data of an underlying base table through a view, as long as the following conditions are true:
Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.
The columns being modified in the view must directly reference the underlying data in the table columns. The columns cannot be derived in any other way, such as through the following:
An aggregate function: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR, and VARP.
A computation. The column cannot be computed from an expression that uses other columns. Columns that are formed by using the set operators UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT amount to a computation and are also not updatable.
The columns being modified are not affected by GROUP BY, HAVING, or DISTINCT clauses.
TOP is not used anywhere in the select_statement of the view together with the WITH CHECK OPTION clause.
The previous restrictions apply to any subqueries in the FROM clause of the view, just as they apply to the view itself. Generally, the Database Engine must be able to unambiguously trace modifications from the view definition to one base table. For more information, see Modify Data Through a View.
If the previous restrictions prevent you from modifying data directly through a view, consider the following options:
INSTEAD OF Triggers
INSTEAD OF triggers can be created on a view to make a view updatable. The INSTEAD OF trigger is executed instead of the data modification statement on which the trigger is defined. This trigger lets the user specify the set of actions that must happen to process the data modification statement. Therefore, if an INSTEAD OF trigger exists for a view on a specific data modification statement (INSERT, UPDATE, or DELETE), the corresponding view is updatable through that statement. For more information about INSTEAD OF triggers, see DML Triggers.
Partitioned Views
If the view is a partitioned view, the view is updatable, subject to certain restrictions. When it is needed, the Database Engine distinguishes local partitioned views as the views in which all participating tables and the view are on the same instance of SQL Server, and distributed partitioned views as the views in which at least one of the tables in the view resides on a different or remote server.
Partitioned Views
A partitioned view is a view defined by a UNION ALL of member tables structured in the same way, but stored separately as multiple tables in either the same instance of SQL Server or in a group of autonomous instances of SQL Server servers, called federated database servers.
The preferred method for partitioning data local to one server is through partitioned tables. For more information, see Partitioned Tables and Indexes.
A partitioned view on Server1 is defined in the following way:
Generally, a view is said to be a partitioned view if it is of the following form:
Conditions for Creating Partitioned Views
In the column list of the view definition, select all columns in the member tables.
Ensure that the columns in the same ordinal position of each select list are of the same type, including collations. It is not sufficient for the columns to be implicitly convertible types, as is generally the case for UNION.
Constraint C1 defined on table T1 must be of the following form:
The following examples show valid sets of constraints:
The same column cannot be used multiple times in the select list.
The partitioning column is a part of the PRIMARY KEY of the table.
It cannot be a computed, identity, default, or timestamp column.
If there is more than one constraint on the same column in a member table, the Database Engine ignores all the constraints and does not consider them when determining whether the view is a partitioned view. To meet the conditions of the partitioned view, ensure that there is only one partitioning constraint on the partitioning column.
There are no restrictions on the updatability of the partitioning column.
The tables can be either local tables or tables from other computers that are running SQL Server that are referenced either through a four-part name or an OPENDATASOURCE- or OPENROWSET-based name. The OPENDATASOURCE and OPENROWSET syntax can specify a table name, but not a pass-through query. For more information, see OPENDATASOURCE (Transact-SQL) and OPENROWSET (Transact-SQL).
If one or more of the member tables are remote, the view is called distributed partitioned view, and additional conditions apply. They are described later in this section.
The same table cannot appear two times in the set of tables that are being combined with the UNION ALL statement.
The member tables cannot have indexes created on computed columns in the table.
The member tables have all PRIMARY KEY constraints on the same number of columns.
All member tables in the view have the same ANSI padding setting. This can be set by using either the user options option in sp_configure or the SET statement.
Conditions for Modifying Data in Partitioned Views
The following restrictions apply to statements that modify data in partitioned views:
The INSERT statement supplies values for all the columns in the view, even if the underlying member tables have a DEFAULT constraint for those columns or if they allow for null values. For those member table columns that have DEFAULT definitions, the statements cannot explicitly use the keyword DEFAULT.
The value being inserted into the partitioning column satisfies at least one of the underlying constraints; otherwise, the insert action will fail with a constraint violation.
UPDATE statements cannot specify the DEFAULT keyword as a value in the SET clause, even if the column has a DEFAULT value defined in the corresponding member table.
Columns in the view that are an identity column in one or more of the member tables cannot be modified by using an INSERT or UPDATE statement.
If one of the member tables contains a timestamp column, the data cannot be modified by using an INSERT or UPDATE statement.
If one of the member tables contains a trigger or an ON UPDATE CASCADE/SET NULL/SET DEFAULT or ON DELETE CASCADE/SET NULL/SET DEFAULT constraint, the view cannot be modified.
INSERT, UPDATE, and DELETE actions against a partitioned view are not allowed if there is a self-join with the same view or with any of the member tables in the statement.
To update a partitioned view, the user must have INSERT, UPDATE, and DELETE permissions on the member tables.
Additional Conditions for Distributed Partitioned Views
For distributed partitioned views (when one or more member tables are remote), the following additional conditions apply:
A distributed transaction will be started to guarantee atomicity across all nodes affected by the update.
Set the XACT_ABORT SET option to ON for INSERT, UPDATE, or DELETE statements to work.
Any columns in remote tables of type smallmoney that are referenced in a partitioned view are mapped as money. Therefore, the corresponding columns (in the same ordinal position in the select list) in the local tables must also be of type money.
Under database compatibility level 110 and higher, any columns in remote tables of type smalldatetime that are referenced in a partitioned view are mapped as smalldatetime. Corresponding columns (in the same ordinal position in the select list) in the local tables must be smalldatetime. This is a change in behavior from earlier versions of SQL Server in which any columns in remote tables of type smalldatetime that are referenced in a partitioned view are mapped as datetime and corresponding columns in local tables must be of type datetime. For more information, see ALTER DATABASE Compatibility Level (Transact-SQL).
Any linked server in the partitioned view cannot be a loopback linked server. This is a linked server that points to the same instance of SQL Server.
The setting of the SET ROWCOUNT option is ignored for INSERT, UPDATE, and DELETE actions that involve updatable partitioned views and remote tables.
When the member tables and partitioned view definition are in place, the SQL Server query optimizer builds intelligent plans that use queries efficiently to access data from member tables. With the CHECK constraint definitions, the query processor maps the distribution of key values across the member tables. When a user issues a query, the query processor compares the map to the values specified in the WHERE clause, and builds an execution plan with a minimal amount of data transfer between member servers. Therefore, although some member tables may be located in remote servers, the instance of SQL Server resolves distributed queries so that the amount of distributed data that has to be transferred is minimal.
Considerations for Replication
To create partitioned views on member tables that are involved in replication, the following considerations apply:
If the underlying tables are involved in merge replication or transactional replication with updating subscriptions, ensure that the uniqueidentifier column is also included in the select list.
Any INSERT actions into the partitioned view must provide a NEWID() value for the uniqueidentifier column. Any UPDATE actions against the uniqueidentifier column must supply NEWID() as the value because the DEFAULT keyword cannot be used.
The replication of updates made by using the view is the same as when tables are replicated in two different databases: the tables are served by different replication agents and the order of the updates is not guaranteed.
Permissions
Requires CREATE VIEW permission in the database and ALTER permission on the schema in which the view is being created.
Examples
The following examples use the AdventureWorks 2012 or AdventureWorksDW database.
A. Using a simple CREATE VIEW
The following example creates a view by using a simple SELECT statement. A simple view is helpful when a combination of columns is queried frequently. The data from this view comes from the HumanResources.Employee and Person.Person tables of the AdventureWorks2012 database. The data provides name and hire date information for the employees of Adventure Works Cycles. The view could be created for the person in charge of tracking work anniversaries but without giving this person access to all the data in these tables.
B. Using WITH ENCRYPTION
The following example uses the WITH ENCRYPTION option and shows computed columns, renamed columns, and multiple columns.
Applies to: SQL Server 2008 and later and SQL Database.
C. Using WITH CHECK OPTION
The following example shows a view named SeattleOnly that references five tables and allows for data modifications to apply only to employees who live in Seattle.
D. Using built-in functions within a view
The following example shows a view definition that includes a built-in function. When you use functions, you must specify a column name for the derived column.
E. Using partitioned data
Examples: Azure Synapse Analytics and Analytics Platform System (PDW)
F. Creating a simple view
The following example creates a view by selecting only some of the columns from the source table.