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

ОБЛАСТЬ ПРИМЕНЕНИЯ:даSQL Server (начиная с 2008)даБаза данных SQL AzureдаХранилище данных SQL AzureнетParallel Data WarehouseAPPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

В этом разделе описывается, как создавать в представлении индексы.This topic describes how to create indexes on a view. Первым индексом, создаваемым для представления, должен быть уникальный кластеризованный индекс.The first index created on a view must be a unique clustered index. После создания уникального кластеризованного индекса могут быть созданы некластеризованные индексы.After the unique clustered index has been created, you can create more nonclustered indexes. Создание уникального кластеризованного индекса для представления повышает производительность запросов, т. к. представление хранится в базе данных так же, как и таблица с кластеризованным индексом.Creating a unique clustered index on a view improves query performance because the view is stored in the database in the same way a table with a clustered index is stored. Оптимизатор запросов может использовать индексированные представления для ускорения выполнения запросов.The query optimizer may use indexed views to speed up the query execution. Чтобы оптимизатор рассматривал представление для подстановки, это представление не обязательно должно быть указано в запросе.The view does not have to be referenced in the query for the optimizer to consider that view for a substitution.

Перед началомBefore You Begin

Чтобы создать индексированное представление, нужно выполнить следующие шаги. Точность при их выполнении критически важна для успешной реализации индексированного представления.The following steps are required to create an indexed view and are critical to the successful implementation of the indexed view:

  1. Убедитесь в правильности параметров SET для всех существующих таблиц, на которые ссылается представление.Verify the SET options are correct for all existing tables that will be referenced in the view.
  2. Прежде чем создавать новые таблицы и представление, проверьте параметры SET для сеанса.Verify that the SET options for the session are set correctly before you create any tables and the view.
  3. Проверьте, что определение представления детерминировано.Verify that the view definition is deterministic.
  4. Создайте представление с помощью параметра WITH SCHEMABINDING.Create the view by using the WITH SCHEMABINDING option.
  5. Создайте уникальный кластеризованный индекс для представления.Create the unique clustered index on the view.

Важно!

При выполнении DML1 для таблицы, на которую ссылается большое количество индексированных представлений либо меньшее количество очень сложных индексированных представлений, эти упоминаемые индексированные представления также потребуется обновить.When executing DML1 on a table referenced by a large number of indexed views, or fewer but very complex indexed views, those referenced indexed views will have to be updated as well. В результате может значительно снизиться производительность запросов DML, а в некоторых случаях может быть невозможно даже создать план запроса.As a result, DML query performance can degrade significantly, or in some cases, a query plan cannot even be produced.
В таких ситуациях протестируйте запросы DML перед использованием в рабочей среде, проанализируйте план запроса и настройте или упростите инструкцию DML.In such scenarios, test your DML queries before production use, analyze the query plan and tune/simplify the DML statement.

1 Например, операции UPDATE, DELETE или INSERT.1 Such as UPDATE, DELETE or INSERT operations.

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

Если при выполнении запроса активны разные параметры SET, выполнение одного и того же выражения может дать разные результаты в Компонент Database EngineDatabase Engine .Evaluating the same expression can produce different results in the Компонент Database EngineDatabase Engine when different SET options are active when the query is executed. Например, если параметр SET CONCAT_NULL_YIELDS_NULL равен ON, выражение 'abc' + NULL возвращает значение NULL.For example, after the SET option CONCAT_NULL_YIELDS_NULL is set to ON, the expression 'abc' + NULL returns the value NULL. Однако если параметр CONCAT_NULL_YIEDS_NULL равен OFF, то же самое выражение дает результат 'abc'.However, after CONCAT_NULL_YIEDS_NULL is set to OFF, the same expression produces 'abc'.

Для правильной поддержки представлений и получения согласованных результатов некоторые параметры SET индексированных представлений должны иметь определенные значения.To make sure that the views can be maintained correctly and return consistent results, indexed views require fixed values for several SET options. В приведенных ниже случаях параметрам SET из следующей таблицы нужно присвоить значения, указанные в столбце Обязательное значение :The SET options in the following table must be set to the values shown in the Required Value column whenever the following conditions occur:

  • Будет создано представление с соответствующими индексами в нем.The view and subsequent indexes on the view are created.

  • Базовые таблицы, на которые ссылается представление в момент создания таблицы.The base tables referenced in the view at the time the table is created.

  • С любой из таблиц, используемых в индексированном представлении, выполняется операция вставки, обновления или удаления.There is any insert, update, or delete operation performed on any table that participates in the indexed view. Это требование охватывает такие операции, как массовое копирование, репликация и распределенные запросы.This requirement includes operations such as bulk copy, replication, and distributed queries.

  • Индексированное представление используется оптимизатором запросов для создания плана запроса.The indexed view is used by the query optimizer to produce the query plan.

Параметры SETSET options Обязательное значениеRequired value Значение сервера по умолчаниюDefault server value По умолчаниюDefault

Значение OLE DB и ODBCOLE DB and ODBC value
По умолчаниюDefault

Значение DB-LibraryDB-Library value
ANSI_NULLSANSI_NULLS ONON ONON ONON OFFOFF
ANSI_PADDINGANSI_PADDING ONON ONON ONON OFFOFF
ANSI_WARNINGS1ANSI_WARNINGS1 ONON ONON ONON OFFOFF
ARITHABORTARITHABORT ONON ONON OFFOFF OFFOFF
CONCAT_NULL_YIELDS_NULLCONCAT_NULL_YIELDS_NULL ONON ONON ONON OFFOFF
NUMERIC_ROUNDABORTNUMERIC_ROUNDABORT OFFOFF OFFOFF OFFOFF OFFOFF
QUOTED_IDENTIFIERQUOTED_IDENTIFIER ONON ONON ONON OFFOFF
         

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

Если используется соединение с сервером через интерфейсы OLE DB или ODBC, достаточно изменить параметр ARITHABORT.If you are using an OLE DB or ODBC server connection, the only value that must be modified is the ARITHABORT setting. Все значения DB-Library должны быть правильно установлены на уровне сервера с помощью хранимой процедуры sp_configure или из приложения с помощью команды SET.All DB-Library values must be set correctly either at the server level by using sp_configure or from the application by using the SET command.

Важно!

Настоятельно рекомендуется присвоить пользовательскому параметру ARITHABORT значение ON на всем сервере, как только в какой-либо базе данных сервера будет создано первое индексированное представление или индекс на базе вычисляемого столбца.We strongly recommend that you set the ARITHABORT user option to ON server-wide as soon as the first indexed view or index on a computed column is created in any database on the server.

Детерминированные представленияDeterministic Views

Определение индексированного представления должно быть детерминированным.The definition of an indexed view must be deterministic. Представление детерминировано, если детерминированы все выражения в списке выбора, а также предложения WHERE и GROUP BY.A view is deterministic if all expressions in the select list, as well as the WHERE and GROUP BY clauses, are deterministic. Детерминированные выражения возвращают одни и те же результаты при каждом вычислении с одним и тем же набором входных значений.Deterministic expressions always return the same result any time they are evaluated with a specific set of input values. Только детерминированные функции могут использоваться в детерминированных выражениях.Only deterministic functions can participate in deterministic expressions. Например, функция DATEADD детерминирована, так как всегда возвращает один и тот же результат для любого заданного набора значений аргументов трех ее параметров.For example, the DATEADD function is deterministic because it always returns the same result for any given set of argument values for its three parameters. Функция GETDATE не детерминирована, так как всегда вызывается с одним и тем же аргументом, но каждый раз возвращает разные значения.GETDATE is not deterministic because it is always invoked with the same argument, but the value it returns changes each time it is executed.

Чтобы определить, является ли столбец представления детерминированным, используйте свойство IsDeterministic функции COLUMNPROPERTY .To determine whether a view column is deterministic, use the IsDeterministic property of the COLUMNPROPERTY function. Чтобы определить, является ли точным детерминированный столбец в представлении с привязкой к схеме, используйте свойство IsPrecise функции COLUMNPROPERTY.To determine if a deterministic column in a view with schema binding is precise, use the IsPrecise property of the COLUMNPROPERTY function. Функция COLUMNPROPERTY возвращает значение 1, если TRUE, 0, если FALSE, и NULL в случае недопустимого входного значения.COLUMNPROPERTY returns 1 if TRUE, 0 if FALSE, and NULL for input that is not valid. Это означает, что столбец не является детерминированным или точным.This means the column is not deterministic or not precise.

Даже если выражение детерминировано, если оно содержит выражения с плавающей запятой, результат может зависеть от архитектуры процессора или версии микропрограммы.Even if an expression is deterministic, if it contains float expressions, the exact result may depend on the processor architecture or version of microcode. Для сохранения целостности данных такие выражения могут быть только неключевыми столбцами индексированных представлений.To ensure data integrity, such expressions can participate only as non-key columns of indexed views. Детерминированные выражения, не содержащие выражений с плавающей запятой, называются точными выражениями.Deterministic expressions that do not contain float expressions are called precise. Только точные детерминированные выражения могут содержаться в ключевых столбцах и предложениях WHERE или GROUP BY индексированных представлений.Only precise deterministic expressions can participate in key columns and in WHERE or GROUP BY clauses of indexed views.

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

Кроме требований, касающихся параметров SET и детерминированных функций, должны выполняться следующие требования.In addition to the SET options and deterministic function requirements, the following requirements must be met:

  • Пользователь, выполняющий инструкцию CREATE INDEX, должен быть владельцем представления.The user that executes CREATE INDEX must be the owner of the view.

  • При создании индекса параметр IGNORE_DUP_KEY должен быть установлен в OFF (значение по умолчанию).When you create the index, the IGNORE_DUP_KEY option must be set to OFF (the default setting).

  • Имя таблицы в определении представления должно быть двухкомпонентным: схема.имя_таблицы .Tables must be referenced by two-part names, schema.tablename in the view definition.

  • Определяемые пользователем функции, на которые ссылается представление, должны быть созданы с параметром WITH SCHEMABINDING.User-defined functions referenced in the view must be created by using the WITH SCHEMABINDING option.

  • Все определяемые пользователем функции, на которые ссылается представление, должны иметь двухкомпонентные имена: <schema>.<function>.Any user-defined functions referenced in the view must be referenced by two-part names, <schema>.<function>.

  • Свойство доступа к данным пользовательской функции должно быть установлено в значение NO SQL, а свойство внешнего доступа — в NO.The data access property of a user-defined function must be NO SQL, and external access property must be NO.

  • Функции среды CLR могут быть указаны в списке выбора представления, но не могут быть частью определения ключа кластеризованного индекса.Common language runtime (CLR) functions can appear in the select list of the view, but cannot be part of the definition of the clustered index key. Функции CLR нельзя указывать в представлении в предложении WHERE и предложении ON операции JOIN.CLR functions cannot appear in the WHERE clause of the view or the ON clause of a JOIN operation in the view.

  • Функции и методы CLR определяемого пользователем типа данных, используемые в определении представления, должны иметь свойства, установленные так, как показано в следующей таблице.CLR functions and methods of CLR user-defined types used in the view definition must have the properties set as shown in the following table.

    СвойствоProperty ПримечаниеNote
    DETERMINISTIC = TRUEDETERMINISTIC = TRUE Должно быть объявлено явно в качестве атрибута метода Microsoft .NET Framework.Must be declared explicitly as an attribute of the Microsoft .NET Framework method.
    PRECISE = TRUEPRECISE = TRUE Должно быть объявлено явно в качестве атрибута метода .NET Framework.Must be declared explicitly as an attribute of the .NET Framework method.
    DATA ACCESS = NO SQLDATA ACCESS = NO SQL Определяется установкой атрибута DataAccess в DataAccessKind.None и атрибута SystemDataAccess в SystemDataAccessKind.None.Determined by setting DataAccess attribute to DataAccessKind.None and SystemDataAccess attribute to SystemDataAccessKind.None.
    EXTERNAL ACCESS = NOEXTERNAL ACCESS = NO Для процедур CLR значением свойства по умолчанию является NO.This property defaults to NO for CLR routines.
       
  • Представления должны быть созданы с параметром WITH SCHEMABINDING.The view must be created by using the WITH SCHEMABINDING option.

  • В представлении допустимы ссылки только на базовые таблицы той же самой базы данных.The view must reference only base tables that are in the same database as the view. Ссылки на другие представления недопустимы.The view cannot reference other views.

  • Инструкция SELECT в определении представления не должна содержать следующие элементы языка Transact-SQL:The SELECT statement in the view definition must not contain the following Transact-SQL elements:

    COUNT Функции ROWSET (OPENDATASOURCE, OPENQUERY, OPENROWSET И OPENXML)ROWSET functions (OPENDATASOURCE, OPENQUERY, OPENROWSET, AND OPENXML) Объединения OUTER (LEFT, RIGHT или FULL)OUTER joins (LEFT, RIGHT, or FULL)
    Производная таблица (определяемая путем указания инструкции SELECT в предложении FROM)Derived table (defined by specifying a SELECT statement in the FROM clause) СамосоединенияSelf-joins Указание столбцов с использованием SELECT * или SELECT <table_name>.*Specifying columns by using SELECT * or SELECT <table_name>.*
    DISTINCT STDEV, STDEVP, VAR, VARP или AVGSTDEV, STDEVP, VAR, VARP, or AVG Обобщенное табличное выражение (CTE)Common table expression (CTE)
    Столбцы float1, text, ntext, image, XML или filestreamfloat1, text, ntext, image, XML, or filestream columns Вложенный запросSubquery Предложение OVER, включающее статистические функции или агрегатные оконные функцииOVER clause, which includes ranking or aggregate window functions
    Полнотекстовые предикаты (CONTAINS, FREETEXT)Full-text predicates (CONTAINS, FREETEXT) Функция SUM, ссылающаяся на выражение, допускающее значение NULLSUM function that references a nullable expression ORDER BY
    Определяемая пользователем агрегатная функция CLRCLR user-defined aggregate function TOP Операторы CUBE, ROLLUP или GROUPING SETSCUBE, ROLLUP, or GROUPING SETS operators
    MIN, MAXMIN, MAX Операторы UNION, EXCEPT или INTERSECTUNION, EXCEPT, or INTERSECT operators TABLESAMPLE
    Табличные переменныеTable variables OUTER APPLY или CROSS APPLYOUTER APPLY or CROSS APPLY PIVOT, UNPIVOTPIVOT, UNPIVOT
    Наборы разреженных столбцовSparse column sets Встроенные функции (TVF) или функции с табличным значением с несколькими инструкциями (MSTVF)Inline (TVF) or multi-statement table-valued functions (MSTVF) OFFSET
    CHECKSUM_AGG
         

    1 Индексированное представление может содержать столбцы типа float, но они не могут включаться в ключ кластеризованного индекса.1 The indexed view can contain float columns; however, such columns cannot be included in the clustered index key.

  • Если присутствует предложение GROUP BY, определение VIEW должно содержать COUNT_BIG(*) и не должно содержать HAVING.If GROUP BY is present, the VIEW definition must contain COUNT_BIG(*) and must not contain HAVING. Эти ограничения для предложения GROUP BY относятся только к определению индексированного представления.These GROUP BY restrictions are applicable only to the indexed view definition. Запрос может использовать индексированное представление в своем плане выполнения, даже если он не соответствует этим ограничениям для предложения GROUP BY.A query can use an indexed view in its execution plan even if it does not satisfy these GROUP BY restrictions.

  • Если определение представления содержит предложение GROUP BY, ключ уникального кластеризованного индекса может включать только столбцы, указанные в предложении GROUP BY.If the view definition contains a GROUP BY clause, the key of the unique clustered index can reference only the columns specified in the GROUP BY clause.

Важно!

Добавление индексированных представлений в темпоральные запросы (запросы, использующие предложение FOR SYSTEM_TIME) не поддерживается.Indexed views are not supported on top of temporal queries (queries that use FOR SYSTEM_TIME clause).

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

При ссылке на строковые литералы datetime и smalldatetime из индексированных представлений рекомендуется явно преобразовывать литерал к нужному типу даты при помощи детерминированного стиля формата даты.When you refer to datetime and smalldatetime string literals in indexed views, we recommend that you explicitly convert the literal to the date type you want by using a deterministic date format style. Список детерминированных стилей форматирования даты см. в разделе Функции CAST и CONVERT (Transact-SQL).For a list of the date format styles that are deterministic, see CAST and CONVERT (Transact-SQL). Дополнительные сведения о детерминированных и недетерминированных выражениях см. в разделе Замечания.For more information about deterministic and nondeterministic expressions, see the Considerations section in this page.

При выполнении DML (например, UPDATE, DELETE или INSERT) для таблицы, на которую ссылается большое количество индексированных представлений либо меньшее количество очень сложных индексированных представлений, эти индексированные представления также потребуется обновить.When you execute DML (such as UPDATE, DELETE or INSERT) on a table referenced by a large number of indexed views, or fewer but very complex indexed views, those indexed views will have to be updated as well during DML execution. В результате может значительно снизиться производительность запросов DML, а в некоторых случаях может быть невозможно даже создать план запроса.As a result, DML query performance may degrade significantly, or in some cases, a query plan cannot even be produced. В таких ситуациях протестируйте запросы DML перед использованием в рабочей среде, проанализируйте план запроса и настройте или упростите инструкцию DML.In such scenarios, test your DML queries before production use, analyze the query plan and tune/simplify the DML statement.

ЗамечанияConsiderations

Значение параметра large_value_types_out_of_row столбца в индексированном представлении наследуется от значения соответствующего столбца базовой таблицы.The setting of the large_value_types_out_of_row option of columns in an indexed view is inherited from the setting of the corresponding column in the base table. Это значение задается с помощью хранимой процедуры sp_tableoption.This value is set by using sp_tableoption. Для столбцов, созданных из выражений, установкой по умолчанию является 0.The default setting for columns formed from expressions is 0. Это означает, что типы больших значений хранятся в строке.This means that large value types are stored in-row.

Индексированные представления могут создаваться на секционированной таблице и сами могут быть секционированными.Indexed views can be created on a partitioned table, and can themselves be partitioned.

Чтобы компонент Компонент Database EngineDatabase Engine не использовал индексированные представления, включите в запрос указание OPTION (EXPAND VIEWS).To prevent the Компонент Database EngineDatabase Engine from using indexed views, include the OPTION (EXPAND VIEWS) hint on the query. Кроме того, если любой из вышеуказанных параметров установлен неправильно, оптимизатор не сможет использовать индексы представлений.Also, if any of the listed options are incorrectly set, this will prevent the optimizer from using the indexes on the views. Дополнительные сведения об указании OPTION (EXPAND VIEWS) см. в разделе SELECT (Transact-SQL).For more information about the OPTION (EXPAND VIEWS) hint, see SELECT (Transact-SQL).

При удалении представления удаляются также и все его индексы.All indexes on a view are dropped when the view is dropped. При удалении кластеризованного индекса удаляются все некластеризованные индексы и автоматически созданные для представления статистики.All nonclustered indexes and auto-created statistics on the view are dropped when the clustered index is dropped. Статистики, созданные пользователем, сохраняются.User-created statistics on the view are maintained. Некластеризованные индексы могут удаляться по отдельности.Nonclustered indexes can be individually dropped. При удалении кластеризованного индекса представления удаляется сохраненный результирующий набор, и оптимизатор снова начинает работать с ним, как с обычным представлением.Dropping the clustered index on the view removes the stored result set, and the optimizer returns to processing the view like a standard view.

Индексы таблиц и представлений могут быть отключены.Indexes on tables and views can be disabled. При отключении кластеризованного индекса таблицы индексы представлений, связанных с ней, также отключаются.When a clustered index on a table is disabled, indexes on views associated with the table are also disabled.

Выражения, включающие неявные преобразования символьных строк в типы datetime или smalldatetime, считаются недетерминированными.Expressions that involve implicit conversion of character strings to datetime or smalldatetime are considered nondeterministic. Дополнительные сведения см. в статье Недетерминированное преобразование строк дат литералов в значения DATE.For more information, see Nondeterministic conversion of literal date strings into DATE values.

безопасностьSecurity

PermissionsPermissions

Для выполнения этой инструкции требуется разрешение CREATE VIEW в отношении базы данных и разрешение ALTER в отношении схемы, в которой создается представление.Requires CREATE VIEW permission in the database and ALTER permission on the schema in which the view is being created.

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

Создание индексируемого представленияTo create an indexed view

  1. В обозревателе объектовподключитесь к экземпляру компонента Компонент Database EngineDatabase Engine.In Object Explorer, connect to an instance of Компонент Database EngineDatabase Engine.

  2. На стандартной панели выберите пункт Создать запрос.On the Standard bar, click New Query.

  3. Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить.Copy and paste the following example into the query window and click Execute. В следующем примере создается представление и индекс для этого представления.The example creates a view and an index on that view. Включено два запроса, использующих созданное индексированное представление.Two queries are included that use the indexed view.

    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).For more information, see CREATE VIEW (Transact-SQL).

См. также:See Also

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