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

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

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

Шаги

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

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

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

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

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

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

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

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

Параметры SET	Обязательное значение	Значение сервера по умолчанию	По умолчанию. Значение OLE DB и ODBC	По умолчанию. Значение DB-Library
ANSI_NULLS	ON	ON	ON	OFF
ANSI_PADDING	ON	ON	ON	OFF
ANSI_WARNINGS 1	ON	ON	ON	OFF
ARITHABORT	ON	ON	OFF	OFF
CONCAT_NULL_YIELDS_NULL	ON	ON	ON	OFF
NUMERIC_ROUNDABORT	OFF	OFF	OFF	OFF
QUOTED_IDENTIFIER	ON	ON	ON	OFF

¹ Установка 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, если TRUE, 0, если FALSE, и NULL для недопустимых входных данных. Это означает, что столбец не является детерминированным или не точным.

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

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

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

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

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

• Таблицы должны упоминаться по двухсоставным именам, <schema>.<tablename>, в определении представления.

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

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

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

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

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

  Свойство	Примечание.
  ДЕТЕРМИНИРОВАННЫЙ = ИСТИНА	Должно быть объявлено явно в качестве атрибута метода Microsoft .NET Framework.
  PRECISE = ИСТИНА	Должно быть объявлено явно в качестве атрибута метода .NET Framework.
  Доступ к данным = NO SQL	Определяется путем задания атрибута DataAccess значением DataAccessKind.None и атрибута SystemDataAccess значением SystemDataAccessKind.None.
  ВНЕШНИЙ ДОСТУП = НЕТ	Для процедур 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,MAX,CHECKSUM_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 столбец в ключе индекса
  Наборы разреженных столбцов

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

  Внимание

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

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

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

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

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

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

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

Дополнительные рекомендации

• Параметр 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
INNER 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
INNER 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. В выпуске SQL Server Standard необходимо использовать NOEXPAND подсказку запроса для обращения к индексированному представлению напрямую. База данных 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.

Связанный контент

• CREATE INDEX (Transact-SQL)
• SET ANSI_NULLS (Transact-SQL)
• SET ANSI_PADDING (Transact-SQL)
• SET ANSI_WARNINGS (Transact-SQL)
• SET ARITHABORT (Transact-SQL)
• SET CONCAT_NULL_YIELDS_NULL (Transact-SQL)
• SET NUMERIC_ROUNDABORT (Transact-SQL)
• SET QUOTED_IDENTIFIER (Transact-SQL)