• Статья

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

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

  • При выполнении инструкции CREATE VIEW параметры ANSI_NULLS и QUOTED_IDENTIFIER должны быть установлены в ON. Для представлений эти сведения доступны через функцию OBJECTPROPERTY со свойствами ExecIsAnsiNullsOn и ExecIsQuotedIdentOn.

  • При создании инструкцией CREATE TABLE таблиц, на которые ссылается представление, параметр ANSI_NULLS должен быть установлен в ON.

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

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

  • Представление должно быть создано с параметром SCHEMABINDING. Это позволяет привязать представление к схеме базовых таблиц.

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

  • Таблицы и пользовательские функции, на которые ссылается представление, должны указываться по именам из двух элементов. Имена из одного, трех или четырех элементов недопустимы.

  • Все функции, на которые ссылаются выражения в представлении, должны быть детерминированными. Определить, является ли пользовательская функция детерминированной, можно по свойству IsDeterministic через функцию OBJECTPROPERTY. Дополнительные сведения см. в разделе Детерминированные и недетерминированные функции.

    ПримечаниеПримечание

    При ссылке на строковые литералы datetime и smalldatetime из индексированных представлений в SQL Server 2008 рекомендуется явно преобразовывать литерал к типу данных, необходимому при помощи детерминированного стиля формата даты. Список детерминированных стилей формата даты см. в разделе Функции CAST и CONVERT (Transact-SQL). Выражения, включающие неявные преобразования символьных строк к типам данных datetime или smalldatetime, считаются недетерминированными, если только не установлен уровень совместимости 80 или менее. Это связано с тем, что результаты зависят от значений параметров LANGUAGE и DATEFORMAT сеанса сервера. Например, результат выражения CONVERT (datetime, '30 listopad 1996', 113) зависит от значения параметра LANGUAGE, поскольку слово listopad в разных языках обозначает разные месяцы. Аналогичным образом, вычисляя выражение DATEADD(mm,3,'2000-12-01'), SQL Server интерпретирует строку '2000-12-01' в соответствии со значением параметра DATEFORMAT.

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

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

  • Если в определении представления используется статистическая функция, список SELECT должен также включать в себя COUNT_BIG (*).

  • Свойство доступа к данным пользовательской функции должно быть установлено в NO SQL, а свойство внешнего доступа — в NO.

  • Функции среды CLR могут быть указаны в списке выбора представления, но не могут быть частью определения ключа кластеризованного индекса. Функции CLR нельзя указывать в представлении в предложении WHERE и предложении ON операции JOIN.

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

    Свойство

    Примечание

    DETERMINISTIC = TRUE

    Должно быть объявлено явно в качестве атрибута метода Microsoft .NET Framework.

    PRECISE = TRUE

    Должно быть объявлено явно в качестве атрибута метода .NET Framework.

    DATA ACCESS = NO SQL

    Определяется установкой атрибута DataAccess в DataAccessKind.None и атрибута SystemDataAccess в SystemDataAccessKind.None.

    EXTERNAL ACCESS = NO

    Для процедур CLR значением свойства по умолчанию является NO.

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

    ПредупреждениеВнимание!

    Не рекомендуется устанавливать свойства методов CLR, входящие в противоречие с их функциональностью, это может привести к повреждению данных.

  • Инструкция 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. Если функция AVG(expression) указывается в запросах, ссылающихся на индексированное представление, оптимизатор зачастую может получить необходимый результат, если список выборки представления содержит функции SUM(expression) и COUNT_BIG(expression). Например, список выборки SELECT индексированного представления не может содержать выражение AVG(column1). Если список выборки SELECT представления содержит выражения SUM(column1) и COUNT_BIG(column1), SQL Server может вычислить среднее значение для запроса, который ссылается на представление и задает AVG(column1).

    • Функция SUM, ссылающаяся на выражение, результатом которого может быть значение NULL.

    • Предложение OVER, включающее статистические функции или функции ранжирования окна.

    • Пользовательская статистическая функция CLR.

    • Полнотекстовые предикаты CONTAINS или FREETEXT.

    • Предложения COMPUTE или COMPUTE BY.

    • Операторы CROSS APPLY или OUTER APPLY.

    • Операторы PIVOT и UNPIVOT.

    • Табличные подсказки (применимо только к уровню совместимости 90 и выше).

    • Подсказки соединения.

    • Прямые ссылки на выражения Xquery. Косвенные ссылки, такие как выражения Xquery внутри привязанной к схеме пользовательской функции, являются допустимыми.

  • Если имеется предложение GROUP BY, список выбора представления должен содержать выражение COUNT_BIG(*), а в определении представления не могут быть указаны предложения HAVING или GROUPING SETS или операторы ROLLUP или CUBE.

Требования к инструкции CREATE INDEX

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

Инструкция 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.

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

Замечания

Установка параметра столбца large_value_types_out_of_row в индексированном представлении наследуется от установки соответствующего столбца базовой таблицы. Это значение задается при помощи хранимой процедуры sp_tableoption. Для столбцов, созданных из выражений, установкой по умолчанию является 0. Это означает, что типы больших значений хранятся в строке. Дополнительные сведения см. в разделе Использование типов данных больших значений.

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

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

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

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

  • Удалить и вновь создать представление с указанием WITH SCHEMABINDING.

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

    ПримечаниеПримечание

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

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

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

Пример

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

USE AdventureWorks2008R2;
GO
--Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
    QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
--Create view with schemabinding.
IF OBJECT_ID ('Sales.vOrders', 'view') IS NOT NULL
DROP VIEW Sales.vOrders ;
GO
CREATE VIEW Sales.vOrders
WITH SCHEMABINDING
AS
    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
    GROUP BY OrderDate, ProductID;
GO
--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX IDX_V1 
    ON Sales.vOrders (OrderDate, ProductID);
GO
--This query can use the indexed view even though the view is 
--not specified in the FROM clause.
SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev, 
    OrderDate, ProductID
FROM Sales.SalesOrderDetail AS od
    JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID=o.SalesOrderID
        AND ProductID BETWEEN 700 and 800
        AND OrderDate >= CONVERT(datetime,'05/01/2002',101)
GROUP BY OrderDate, ProductID
ORDER BY Rev DESC;
GO
--This query can use the above indexed view.
SELECT  OrderDate, SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev
FROM Sales.SalesOrderDetail AS od
    JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID=o.SalesOrderID
        AND DATEPART(mm,OrderDate)= 3
        AND DATEPART(yy,OrderDate) = 2002
GROUP BY OrderDate
ORDER BY OrderDate ASC;
GO