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

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

При выполнении инструкции CREATE VIEW параметры ANSI_NULLS и QUOTED_IDENTIFIER должны быть установлены в ON. Для представлений эти сведения доступны через функцию OBJECTPROPERTY со свойствами ExecIsAnsiNullsOn и ExecIsQuotedIdentOn.
При создании инструкцией CREATE TABLE таблиц, на которые ссылается представление, параметр ANSI_NULLS должен быть установлен в ON.
Представление не должно ссылаться ни на какие другие представления, ссылки должны указывать только на базовые таблицы.
Все базовые таблицы, на которые ссылается представление, должны находиться в той же базе данных, что и представление, и иметь того же владельца, что и представление.
Представление должно быть создано с параметром SCHEMABINDING. Это позволяет привязать представление к схеме базовых таблиц.
Пользовательские функции, на которые ссылается представление, должны быть созданы с параметром SCHEMABINDING.
Таблицы и пользовательские функции, на которые ссылается представление, должны указываться по именам из двух элементов. Имена из одного, трех или четырех элементов недопустимы.
Все функции, на которые ссылаются выражения в представлении, должны быть детерминированными.
Неявное преобразование символьных данных не в Юникоде между различными параметрами сортировки также считается недетерминированным, если только не установлен уровень совместимости 80 или менее.
При уровне совместимости 90 создание индексов представлений, содержащих эти выражения, недопустимо. Это, однако, не относится к существующим представлениям, содержащим такие выражения из обновленной базы данных. Если используются индексированные представления, содержащие неявное преобразование строк в дату, необходимо убедиться в том, что значения параметров LANGUAGE и DATEFORMAT в базах данных и приложениях согласованы, чтобы избежать возможности повреждения индексированных представлений.
Если в определении представления используется статистическая функция, список SELECT должен также включать в себя COUNT_BIG (*).
Свойство доступа к данным пользовательской функции должно быть установлено в NO SQL, а свойство внешнего доступа в NO.
Инструкция SELECT в представлении не может содержать следующие синтаксические элементы языка Transact-SQL.

Синтаксис * или table_name.* для задания столбцов. Имена столбцов должны быть указаны явно.
Имя столбца, указываемое в качестве простого выражения, не может быть задано более чем в одном столбце представления. Ссылки на столбец могут выполняться несколько раз, при условии, что все (или все, кроме одной) ссылки на столбец являются частью сложного выражения или параметром функции.

Например, следующий список выборки недопустим:

SELECT ColumnA, ColumnB, ColumnA

Следующий список выборки допустим:

SELECT SUM(ColumnA) AS SumColA, ColumnA % ColumnB AS ModuloColAColB, COUNT_BIG(*) AS cBig FROM dbo.T1 GROUP BY ModuloColAColB

Выражение для столбца, указанного в предложении GROUP BY, или выражение для результата выполнения статистической функции.
Операторы UNION, EXCEPT или INTERSECT.
Вложенные запросы.
Внешние соединения или самосоединение.
Предложение TOP.
Предложение ORDER BY.
Ключевое слово DISTINCT.
COUNT (COUNT_BIG(*) допустимо).
Статистические функции AVG, MAX, MIN, STDEV, STDEVP, VAR или VARP.
Функция SUM, ссылающаяся на выражение, результатом которого может быть значение NULL.

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

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

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

ANSI_NULLS
ANSI_PADDING
ANSI_WARNINGS
CONCAT_NULL_YIELDS_NULL
QUOTED_IDENTIFIER

Параметр NUMERIC_ROUNDABORT должен быть установлен в OFF. Это установка по умолчанию.
Если база данных работает при уровне совместимости 80 или ниже, то параметр ARITHABORT должен быть установлен в значение ON.
При создании кластеризованного или некластеризованного индекса параметр IGNORE_DUP_KEY должен быть установлен в OFF (установка по умолчанию).
Представление не может содержать столбцы типа text, ntext или image, если даже на них нет ссылок в инструкции CREATE INDEX.
Если инструкция SELECT в определении представления содержит предложение GROUP BY, ключ уникального кластеризованного индекса может ссылаться только на столбцы, которые заданы в предложении GROUP BY.
Выражение с потерей точности, формирующее значение ключевого столбца индекса, должно ссылаться на хранимый столбец в базовой таблице данного представления. Этот столбец может быть либо обычным, либо материализованным вычисляемым столбцом. Никакие другие выражения с потерей точности не могут быть частью ключевого столбца индексированного представления.

После создания кластеризованного индекса любое соединение, которое пытается изменить базовые данные представления, должно иметь такие же установки параметров, какие необходимы для создания индекса. Если соединение, выполняющее инструкцию, имеет неверные установки параметров, SQL Server выдает ошибку и выполняет откат инструкций INSERT, UPDATE или DELETE, которые выполняются в отношении результирующего набора представления.

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

Хотя в инструкции CREATE UNIQUE CLUSTERED INDEX задаются только столбцы, образующие кластеризованный индексный ключ, в базе данных хранится весь результирующий набор представления. Как и в кластеризованном индексе базовой таблицы, структура сбалансированного дерева кластеризованного индекса содержит только ключевые столбцы, а в результирующем наборе представления строки данных содержат все столбцы.

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

Удалить и вновь создать представление с указанием WITH SCHEMABINDING.
Создать второе представление с тем же текстом, что и в существующем представлении, но под другим именем. Оптимизатор пользуется индексами нового представления даже в том случае, если в запросе предложение FROM на него непосредственно не ссылается.

Новое представление должно удовлетворять всем требованиям, предъявляемым к индексированным представлениям.

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

--Установка опций необходимых для создания индексированного представления

SET NUMERIC_ROUNDABORT OFF;

SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON;

--Создание представления с опцией SCHEMABINDING

CREATE VIEW Sales.vOrders

WITH SCHEMABINDING

SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Revenue,

OrderDate, ProductID, COUNT_BIG(*) AS COUNT

FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o

WHERE od.SalesOrderID = o.SalesOrderID