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

Статья
01/07/2014

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

В этом разделе

Перед началом работы выполните следующие действия.

Ограничения

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

Замечания

Безопасность
Создание индексируемого представления

Transact-SQL

Перед началом работы

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

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

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

Если при выполнении запроса активны разные параметры SET, выполнение одного и того же выражения может дать разные результаты в Компонент Database Engine. Например, если параметр SET CONCAT_NULL_YIELDS_NULL равен ON, выражение 'abc' + NULL возвращает значение NULL. Но если параметр CONCAT_NULL_YIEDS_NULL равен OFF, то же самое выражение дает результат 'abc'.

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

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

Индексированное представление используется оптимизатором запросов для создания плана запроса.

Параметры SET	Обязательное значение	Значение сервера по умолчанию	По умолчанию Значение OLE DB и ODBC	По умолчанию Значение DB-Library
ANSI_NULLS	ON	ON	ON	OFF
ANSI_PADDING	ON	ON	ON	OFF
ANSI_WARNINGS*	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

*Если уровень совместимости базы данных равен 90 или более, при установке параметра ANSI_WARNINGS в состояние ON параметр ARITHABORT также устанавливается в состояние ON.

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

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

Настоятельно рекомендуется присвоить пользовательскому параметру 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 определяемого пользователем типа данных, используемые в определении представления, должны иметь свойства, установленные так, как показано в следующей таблице.

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

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

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

COUNT	Функции ROWSET (OPENDATASOURCE, OPENQUERY, OPENROWSET и OPENXML)	Инструкции объединения OUTER (LEFT, RIGHT или FULL)
Производная таблица (определяемая путем указания инструкции SELECT в предложении FROM)	Самосоединения	Указание столбцов с использованием SELECT * или SELECT имя_таблицы.*
DISTINCT	STDEV, STDEVP, VAR, VARP или AVG	Обобщенное табличное выражение (CTE)
Столбцы float*, text, ntext, image, XML или filestream	Вложенный запрос	Предложение OVER, включающее статистические функции или агрегатные оконные функции.
Полнотекстовые предикаты (CONTAIN, FREETEXT)	Функция SUM, ссылающаяся на выражение, допускающее значение NULL	ORDER BY
Определяемая пользователем агрегатная функция CLR	TOP	Операторы CUBE, ROLLUP или GROUPING SETS
MIN, MAX	Операторы UNION, EXCEPT или INTERSECT	TABLESAMPLE
Табличные переменные	OUTER APPLY или CROSS APPLY	PIVOT, UNPIVOT
Наборы разреженных столбцов	Встроенные функции или функции с табличным значением с несколькими инструкциями	OFFSET
CHECKSUM_AGG

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

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

Замечания

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

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

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

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

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

Значок стрелки, используемый со ссылкой «В начало» [В начало]

Безопасность

Разрешения

Для выполнения этой инструкции требуется разрешение CREATE VIEW в отношении базы данных и разрешение ALTER в отношении схемы, в которой создается представление.

Значок стрелки, используемый со ссылкой «В начало» [В начало]

Использование Transact-SQL

Создание индексируемого представления

В обозревателе объектов установите соединение с экземпляром компонента Компонент Database Engine.
На стандартной панели выберите пункт Создать запрос.

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

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

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

Значок стрелки, используемый со ссылкой «В начало» [В начало]