Создание индексированных представлений
Область применения: SQL Server
База данных SQL Azure Управляемый экземпляр SQL Azure
В этой статье описывается, как создавать индексы в представлении. Первым индексом, создаваемым для представления, должен быть уникальный кластеризованный индекс. После создания уникального кластеризованного индекса могут быть созданы некластеризованные индексы. Создание уникального кластеризованного индекса в представлении повышает производительность запросов, так как представление хранится в базе данных таким же образом, как таблица с кластеризованным индексом сохраняется. Оптимизатор запросов может использовать индексированные представления для ускорения выполнения запроса. Представление не должно ссылаться в запросе оптимизатора, чтобы рассмотреть это представление для подстановки.
Шаги
Чтобы создать индексированное представление, нужно выполнить следующие шаги. Точность при их выполнении критически важна для успешной реализации индексированного представления.
- Проверьте правильность
SET
параметров для всех существующих таблиц, на которые будут ссылаться в представлении. - Убедитесь, что
SET
параметры сеанса заданы правильно перед созданием таблиц и представлений. - Проверьте, что определение представления детерминировано.
- Убедитесь, что у базовой таблицы тот же владелец, что и у представления.
- Создайте представление с помощью параметра
WITH SCHEMABINDING
. - Создайте уникальный кластеризованный индекс для представления.
При выполнении UPDATE
DELETE
или 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
если FALSE
TRUE
и 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
, ,OPENROWSET
OPENQUERY
и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
, ,FREETEXT
CONTAINSTABLE
,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 Float 1 столбцов в ключе индекса Наборы разреженных столбцов 1 Индексированное представление может содержать столбцы с плавающей запятой . Однако такие столбцы нельзя включить в кластеризованный ключ индекса.
Внимание
Индексированные представления не поддерживаются поверх темпоральных запросов (запросов, использующих
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 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.