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

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

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

Шаги

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

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

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

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

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

Вычисление одного выражения может привести к разным результатам в ядро СУБД, если при выполнении запроса активны разные SET параметры. Например, после SET задания ONпараметра CONCAT_NULL_YIELDS_NULL выражение '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

^{1 Параметр для неявных наборов ARITHABORT ON.ON ANSI_WARNINGS}

Если вы используете подключение к СЕРВЕРУ 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 параметр индекса (параметр по умолчанию).

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

• Определяемые пользователем функции, на которые ссылается представление, должны быть созданы с параметром 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	Определяется путем задания атрибута и атрибута DataAccess SystemDataAccessKind.None.SystemDataAccess DataAccessKind.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, , OPENROWSETOPENQUERYи 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 ALLEXCEPTINTERSECT	Использование OR, AND NOTи AND в предложении WHERE соответственно
  ORDER BY	ORDER BY
  ORDER BY	OFFSET

  Тип исходного столбца	Возможная альтернатива
  Устаревшие типы столбцов больших значений (текст, ntext и изображение)	Перенос столбцов в varchar(max), nvarchar(max), а также varbinary(max) соответственно.
  Столбцы XML или FILESTREAM
  Float 1 столбцов в ключе индекса
  Наборы разреженных столбцов

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

  Внимание

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

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

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

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

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

При выполнении DML (напримерUPDATEDELETE, или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 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.

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

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