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

  • Статья

Применяется к:SQL ServerAzure SQL DatabaseAzure, управляемому экземпляру SQL Azure

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

Шаги

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

  1. Проверьте правильность SET параметров для всех существующих таблиц, на которые будут ссылаться в представлении.
  2. Прежде чем создавать новые таблицы и представление, проверьте параметры SET для сеанса.
  3. Проверьте, что определение представления детерминировано.
  4. Убедитесь, что у базовой таблицы тот же владелец, что и у представления.
  5. Создайте представление с помощью параметра WITH SCHEMABINDING.
  6. Создайте уникальный кластеризованный индекс для представления.

Внимание

При выполнении UPDATEDELETE операций INSERT или операций (язык обработки данных или DML) в таблице, на которую ссылается большое количество индексированных представлений, или меньше, но очень сложные индексированные представления, эти индексированные представления также должны быть обновлены. В результате может значительно снизиться производительность запросов DML, а в некоторых случаях может быть невозможно даже создать план запроса. В таких ситуациях протестируйте запросы DML перед использованием в рабочей среде, проанализируйте план запроса и настройте или упростите инструкцию DML.

Обязательные параметры SET для индексированных представлений

Вычисление того же выражения может привести к различным результатам ядра СУБД, если при выполнении запроса активны разные параметры SET. Например, если параметр SET CONCAT_NULL_YIELDS_NULL равен ON, выражение 'abc' + NULL возвращает значение NULL. Однако если параметр CONCAT_NULL_YIELDS_NULL равен OFF, то же самое выражение дает результат 'abc'.

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

  • Будет создано представление с соответствующими индексами в нем.
  • Базовые таблицы, на которые ссылается представление в момент создания представления.
  • С любой из таблиц, используемых в индексированном представлении, выполняется операция вставки, обновления или удаления. Это требование охватывает такие операции, как массовое копирование, репликация и распределенные запросы.
  • Индексированное представление используется оптимизатором запросов для создания плана запроса.
Параметры SET Обязательное значение Значение сервера по умолчанию По умолчанию.

Значение OLE DB и ODBC		 По умолчанию.

Значение DB-Library
ANSI_NULLS DNS DNS DNS ВЫКЛ.
ANSI_PADDING DNS DNS DNS ВЫКЛ.
ANSI_WARNINGS 1 DNS DNS DNS ВЫКЛ.
ARITHABORT DNS DNS ВЫКЛ. ВЫКЛ.
CONCAT_NULL_YIELDS_NULL DNS DNS DNS ВЫКЛ.
NUMERIC_ROUNDABORT ВЫКЛ. ВЫКЛ. ВЫКЛ. ВЫКЛ.
QUOTED_IDENTIFIER DNS DNS DNS ВЫКЛ.

1 Если параметру ANSI_WARNINGS присвоить значение ON, то для параметра ARITHABORT будет неявно задано значение ON.

Если используется соединение с сервером через интерфейсы OLE DB или ODBC, достаточно изменить параметр ARITHABORT. Все значения библиотеки DB должны быть правильно заданы на уровне сервера с помощью sp_configure или из приложения с помощью SET команды.

Внимание

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

Требование детерминированного представления

Определение индексированного представления должно быть детерминированным. Представление детерминировано, если детерминированы все выражения в списке выбора, а также предложения WHERE и GROUP BY. Детерминированные выражения всегда возвращают тот же результат в любое время, когда они оцениваются с определенным набором входных значений. Только детерминированные функции могут использоваться в детерминированных выражениях. Например, функция DATEADD детерминирована, так как всегда возвращает один и тот же результат для любого заданного набора значений аргументов трех ее параметров. GETDATE не детерминирован, так как он всегда вызывается с одним и тем же аргументом, но значение, которое он возвращает изменения при каждом выполнении.

Чтобы определить, является ли столбец представления детерминированным, используйте IsDeterministic свойство функции COLUMNPROPERTY . Чтобы определить, является ли детерминированный столбец в представлении с привязкой схемы точным, используйте IsPrecise свойство COLUMNPROPERTY функции. COLUMNPROPERTYвозвращает значение 1 , 0 если FALSETRUEи NULL для входных данных, которые недопустимы. Это означает, что столбец не является детерминированным или не точным.

Даже если выражение детерминировано, если оно содержит выражения с плавающей запятой, результат может зависеть от архитектуры процессора или версии микропрограммы. Для сохранения целостности данных такие выражения могут быть только неключевыми столбцами индексированных представлений. Детерминированные выражения, не содержащие выражения с плавающей запятой, называются точными. Только точные детерминированные выражения могут содержаться в ключевых столбцах и предложениях WHERE или GROUP BY индексированных представлений.

Дополнительные требования

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

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

  • При создании индекса IGNORE_DUP_KEY необходимо задать OFF параметр индекса (параметр по умолчанию).

  • Имя таблицы в определении представления должно быть двухкомпонентным: схема.имя_таблицы .

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

  • Все определяемые пользователем функции, на которые ссылается представление, должны иметь двухкомпонентные имена: <schema>.<function>.

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

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

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

    Свойство Заметка
    DETERMINISTIC = TRUE Должно быть объявлено явно в качестве атрибута метода Microsoft .NET Framework.
    PRECISE = TRUE Должно быть объявлено явно в качестве атрибута метода .NET Framework.
    DATA ACCESS = NO SQL Определяется путем задания атрибута и атрибута DataAccessSystemDataAccessKind.None.SystemDataAccessDataAccessKind.None
    EXTERNAL ACCESS = NO Для процедур CLR значением свойства по умолчанию является NO.

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

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

  • Если присутствует предложение GROUP BY, определение VIEW должно содержать COUNT_BIG(*) и не должно содержать HAVING. Эти ограничения для предложения GROUP BY относятся только к определению индексированного представления. Запрос может использовать индексированное представление в плане выполнения, даже если оно не удовлетворяет этим GROUP BY ограничениям.

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

  • Инструкция SELECT в определении представления не должна содержать следующий синтаксис Transact-SQL:

    Функция Transact-SQL Возможные альтернативные варианты
    COUNT Использование COUNT_BIG
    Функции ROWSET (OPENDATASOURCE, OPENQUERY, OPENROWSETи OPENXML)
    Арифметическое среднее AVG Использование COUNT_BIG и SUM в качестве отдельных столбцов
    Статистические агрегатные функции (STDEV, STDEVP, VARи VARP)
    Функция SUM, ссылающаяся на выражение, допускающее значение NULL Используйте ISNULL внутри, SUM() чтобы сделать выражение не допускаемым значением NULL
    Другие агрегатные функции (MIN, , MAXCHECKSUM_AGGиSTRING_AGG)
    Определяемые пользователем агрегатные функции (SQL CLR)
    Предложение SELECT Элемент Transact-SQL Возможная альтернатива
    WITH cte AS Распространенные табличные выражения (CTE) WITH
    SELECT Подзапросы
    SELECT SELECT [ <table>. ] * Столбцы явного имени
    SELECT SELECT DISTINCT Использование GROUP BY
    SELECT SELECT TOP
    SELECT Предложение OVER, включающее статистические функции или агрегатные оконные функции
    FROM LEFT OUTER JOIN
    FROM RIGHT OUTER JOIN
    FROM FULL OUTER JOIN
    FROM OUTER APPLY
    FROM CROSS APPLY
    FROM Производные табличные выражения (т. е. использование SELECT в предложении FROM )
    FROM Самосоединения
    FROM Табличные переменные
    FROM Встроенная табличная функция
    FROM Функция с табличным значением с несколькими операторами
    FROM PIVOT, UNPIVOT
    FROM TABLESAMPLE
    FROM FOR SYSTEM_TIME Запрос таблицы темпорального журнала напрямую
    WHERE Полнотекстовые предикаты (CONTAINS, , FREETEXTCONTAINSTABLE, FREETEXTTABLE)
    GROUP BY Операторы CUBE, ROLLUP или GROUPING SETS Определение отдельных индексированных представлений для каждого сочетания столбцов GROUP BY
    GROUP BY HAVING
    Операторы Set UNION, UNION ALL, EXCEPT, INTERSECT Использование OR, AND NOTи AND в предложении WHERE соответственно
    ORDER BY ORDER BY
    ORDER BY OFFSET
    Тип исходного столбца Возможная альтернатива
    Устаревшие типы столбцов больших значений, текст, ntext и изображение Перенос столбцов в varchar(max), nvarchar(max), а также varbinary(max) соответственно.
    Столбцы XML или FILESTREAM
    Float1 столбцов в ключе индекса
    Наборы разреженных столбцов

    1 Индексированное представление может содержать столбцы с плавающей запятой . Однако такие столбцы нельзя включить в кластеризованный ключ индекса.

    Внимание

    Добавление индексированных представлений в темпоральные запросы (запросы, использующие предложение FOR SYSTEM_TIME) не поддерживается.

Рекомендации datetime и smalldatetime

При ссылке на строковые литералы datetime и smalldatetime из индексированных представлений рекомендуется явно преобразовывать литерал к нужному типу даты при помощи детерминированного стиля формата даты. Список стилей формата даты, детерминированных, см. в разделе CAST и CONVERT (Transact-SQL). Дополнительные сведения о детерминированных и недетерминированных выражениях см. в разделе Замечания.

Выражения, включающие неявные преобразования символьных строк в типы datetime или smalldatetime, считаются недетерминированными. Дополнительные сведения см. в статье Недетерминированное преобразование строк дат литералов в значения DATE.

Вопросы производительности с индексированными представлениями

При выполнении DML (напримерUPDATEDELETE, илиINSERT) на таблицу, на которую ссылается большое количество индексированных представлений, или меньше, но сложных индексированных представлений, эти индексированные представления необходимо обновить, а также во время выполнения DML. В результате производительность запросов DML может значительно снизиться или в некоторых случаях план запроса даже не может быть создан. В таких ситуациях протестируйте запросы DML перед использованием в рабочей среде, проанализируйте план запроса и настройте или упростите инструкцию DML.

Чтобы предотвратить использование индексированных представлений ядра СУБД, добавьте OPTION (EXPAND VIEWS) указание на запрос. Кроме того, если любой из вышеуказанных параметров установлен неправильно, оптимизатор не сможет использовать индексы представлений. Дополнительные сведения о подсказке OPTION (EXPAND VIEWS) см. в разделе SELECT (Transact-SQL).

Дополнительные замечания

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

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

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

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

Разрешения

Чтобы создать представление, пользователю требуется разрешение CREATE VIEW в отношении базы данных и разрешение ALTER в отношении схемы, в которой создается представление. Если базовая таблица находится в другой схеме, для нее требуется как минимум разрешение REFERENCES. Если пользователь, создающий индекс, отличается от пользователей, создавших представление, для создания индекса требуется только разрешение ALTER для представления (охватывается ALTER в схеме).

Индексы можно создавать только в представлениях с тем же владельцем, что и в указанной таблице или таблицах. Это также называется нетронутой цепочкой владения между представлением и таблицами. Как правило, если таблица и представление находятся в одной схеме, то один и тот же владелец схемы применяется ко всем объектам в схеме. Поэтому можно создать представление и не быть владельцем представления. С другой стороны, также возможно, что отдельные объекты в схеме имеют разных явных владельцев. Столбец principal_id содержит sys.tables значение, если владелец отличается от владельца схемы.

Создание индексированного представления: пример для T-SQL

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

--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;

--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

В следующих двух запросах показано, как можно использовать индексированное представление, даже если представление не указано в предложении FROM .

--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 o.OrderDate >= CONVERT(datetime, '05/01/2012', 101)
WHERE od.ProductID BETWEEN 700 and 800
   GROUP BY OrderDate, ProductID
   ORDER BY Rev DESC;
GO

--This query will also 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 o.OrderDate >= CONVERT(datetime,'03/01/2012', 101)
      AND o.OrderDate < CONVERT(datetime,'04/01/2012', 101)
    GROUP BY OrderDate
    ORDER BY OrderDate ASC;

Наконец, в этом примере показано выполнение запросов непосредственно из индексированного представления. До SQL Server 2016 (13.x) с пакетом обновления 1 (SP1) автоматическое использование индексированного представления оптимизатором запросов поддерживается только в определенных выпусках SQL Server. В выпуске SQL Server Standard необходимо использовать NOEXPAND указание запроса для запроса индексированного представления напрямую. Так как SQL Server 2016 (13.x) с пакетом обновления 1 (SP1) все выпуски поддерживают автоматическое использование индексированного представления. База данных SQL Azure и Управляемый экземпляр SQL Azure также поддерживают автоматическое использование индексированных представлений без указания NOEXPAND указания. Дополнительные сведения см. в статье Указания по таблицам (Transact-SQL).

--This query uses the indexed view directly, on Enterprise edition.
SELECT OrderDate, Revenue
FROM Sales.vOrders
WHERE OrderDate >= CONVERT(datetime,'03/01/2012', 101)
    AND OrderDate < CONVERT(datetime,'04/01/2012', 101)
ORDER BY OrderDate ASC;

--This query uses the indexed view directly, with the NOEXPAND hint.
SELECT OrderDate, Revenue
FROM Sales.vOrders WITH (NOEXPAND)
WHERE OrderDate >= CONVERT(datetime,'03/01/2012', 101)
    AND OrderDate < CONVERT(datetime,'04/01/2012', 101)
ORDER BY OrderDate ASC;

Дополнительные сведения см. в статье CREATE VIEW (Transact-SQL).

Далее