创建索引视图

  • 项目

适用于：SQL ServerAzure SQL 数据库Azure SQL 托管实例

本文介绍了如何对视图创建索引。 对视图创建的第一个索引必须是唯一聚集索引。 创建唯一聚集索引后，可以创建更多非聚集索引。 为视图创建唯一聚集索引可以提高查询性能，因为视图在数据库中的存储方式与具有聚集索引的表的存储方式相同。 查询优化器可使用索引视图加快执行查询的速度。 要使优化器考虑将该视图作为替换，并不需要在查询中引用该视图。

步骤

创建索引视图需要执行下列步骤并且这些步骤对于成功实现索引视图而言非常重要：

  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 会返回值 NULLNULL。 但将 CONCAT_NULL_YIELDS_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 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 将 ANSI_WARNINGS 设置为 ON 会隐式地将 ARITHABORT 设置为 ON。

如果使用的是 OLE DB 或 ODBC 服务器连接，则唯一必须要修改的值是 ARITHABORT 设置。 必须使用 sp_configure 在服务器级别或使用 SET 命令从应用程序中正确设置所有 DB-Library 值。

重要

强烈建议在服务器的任一数据库中创建计算列的第一个索引视图或索引后，尽早在服务器范围内将 ARITHABORT 用户选项设置为 ON。

确定性视图要求

索引视图的定义必须是确定性的。 如果选择列表中的所有表达式、WHERE 和 GROUP BY 子句都具有确定性，则视图也具有确定性。 在使用特定的输入值集对确定性表达式求值时，它们始终返回相同的结果。 只有确定性函数可以加入确定性表达式。 例如，DATEADD 函数是确定性函数，因为对于其三个参数的任何给定参数值集它总是返回相同的结果。 GETDATE 不是确定性函数，因为总是使用相同的参数调用它，而它在每次执行时返回结果都不同。

要确定视图列是否为确定性列，请使用 COLUMNPROPERTY 函数的 IsDeterministic 属性。 使用 COLUMNPROPERTY 函数的 IsPrecise 属性确定具有架构绑定的视图中的确定性列是否为精确列。 如果 TRUE，则 COLUMNPROPERTY 将返回 1；如果 FALSE，则将返回 0；如果输入无效，则将返回 NULL。 这意味着该列不是确定性列，也不是精确列。

即使是确定性表达式，如果其中包含浮点表达式，则准确结果也会取决于处理器体系结构或微代码的版本。 为了确保数据完整性，此类表达式只能作为索引视图的非键列加入。 不包含浮点表达式的确定性表达式称为精确表达式。 只有精确的确定性表达式才能加入键列，并包含在索引视图的 WHERE 或 GROUP BY 子句中。

其他需求

除对 SET 选项和确定性函数的要求外，还必须满足下列要求

  • 执行 CREATE INDEX 的用户必须是视图所有者。

  • 创建索引时，IGNORE_DUP_KEY 索引选项必须设置为 OFF（默认设置）。

  • 在视图定义中，表必须由两部分组成的名称（即 schema.tablename）引用。

  • 视图中引用的用户定义函数必须使用 WITH SCHEMABINDING 选项创建。

  • 视图中引用的任何用户定义的函数都必须由两部分组成的名称（即 <schema>.<function>）引用。

  • 用户定义函数的数据访问属性必须是 NO SQL，外部访问属性必须是 NO。

  • 公共语言运行时 (CLR) 函数可以出现在视图的选择列表中，但不能作为聚集索引键定义的一部分。 CLR 函数不能出现在视图的 WHERE 子句中或视图中的 JOIN 运算的 ON 子句中。

  • 在视图定义中使用的 CLR 函数和 CLR 用户定义类型方法必须具有下表所示的属性设置。

    properties 注意
    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 选项创建视图。

  • 视图必须仅引用与视图位于同一数据库中的基表。 视图无法引用其他视图。

  • 如果存在 GROUP BY，则 VIEW 定义必须包含 COUNT_BIG(*)，并且不得包含 HAVING。 这些 GROUP BY 限制仅适用于索引视图定义。 即使某个索引视图不满足这些 GROUP BY 限制，查询也可以在其执行计划中使用该视图。

  • 如果视图定义包含 GROUP BY 子句，则唯一聚集索引的键只能引用 GROUP BY 子句中指定的列。

  • 视图定义中的 SELECT 语句不能包含下列 Transact-SQL 元素：

    Transact-SQL 函数 可能的替代方法
    COUNT 使用 COUNT_BIG
    行集函数（OPENDATASOURCE、OPENQUERY、OPENROWSET 和 OPENXML）
    算术平均值 AVG 使用 COUNT_BIG 和 SUM 用作单独的列
    统计聚合函数（STDEV、STDEVP、VAR 和 VARP）
    引用可为空的表达式的 SUM 函数 在 SUM() 内部使用 ISNULL 使表达式不可为 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 派生表表达式（即在 FROM 子句中使用 SELECT）
    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
    集合运算符 UNION、UNION ALL、EXCEPT、INTERSECT 在 WHERE 子句中分别使用 OR、AND NOT 和 AND
    ORDER BY ORDER BY
    ORDER BY OFFSET
    源化列值 可能的替代方法
    弃用的大值列类型 text、ntext 和 image 分别将列迁移到 varchar(max)、nvarchar(max) 和 varbinary(max)。
    xml 或 FILESTREAM 列
    索引键中的 float1 列
    稀疏列集

    1 索引视图可以包含 float 列；但聚集索引键中不能包含此类列。

    重要

    临时查询顶部不支持索引视图（使用 FOR SYSTEM_TIME 子句的查询）。

日期/时间 和 smalldatetime 建议

引用索引视图中的 datetime 和 smalldatetime 字符串文字时，建议使用确定性日期格式样式将文字显式转换为所需日期类型。 有关确定性日期格式样式的列表，请参阅 CAST 与 CONVERT (Transact-SQL)。 有关确定性和非确定性表达式的详细信息，请参阅本页中的注意事项部分。

将字符串隐式转换为 datetime 或 smalldatetime 所涉及的表达式被视为具有不确定性。 有关详细信息，请参阅文字日期字符串转换为日期值的不确定性转换。

索引视图的性能注意事项

如果表被大量索引视图引用（或引用它的索引视图数量较少，但较为复杂），那么在该表上执行 DML（如 UPDATE、DELETE或 INSERT）时，必须在执行 DML 期间更新这些索引视图。 因此，DML 查询性能可能会显着降低，或者在某些情况下，甚至无法生成查询计划。 在这种情况下，请在生成使用之前测试 DML 查询、分析查询计划并调整/简化 DML 语句。

要防止数据库引擎使用索引视图，请在查询中包含 OPTION (EXPAND VIEWS) 提示。 此外，任何所列选项设置不正确均会阻止优化器使用视图上的索引。 有关 OPTION (EXPAND VIEWS) 提示的更多信息，请参阅 SELECT (Transact-SQL)。

各种其他注意事项

  • 索引视图中列的 large_value_types_out_of_row 选项的设置继承的是基表中相应列的设置。 此值是使用 sp_tableoption设置的。 从表达式组成的列的默认设置为 0。 这意味着大值类型存储在行内。

  • 可以对已分区表创建索引视图，并可以由其自行分区。

  • 若删除视图，该视图的所有索引也将被删除。 若删除聚集索引，视图的所有非聚集索引和自动创建的统计信息也将被删除。 视图中用户创建的统计信息受到维护。 非聚集索引可以分别删除。 删除视图的聚集索引将删除存储的结果集，并且优化器将重新像处理标准视图那样处理视图。

  • 可以禁用表和视图的索引。 禁用表的聚集索引时，与该表关联的视图的索引也将被禁用。

权限

若要创建视图，用户需要在数据库中具有 CREATE VIEW 权限，并具有在其中创建视图的架构的 ALTER 权限。 如果基表位于不同的架构中，则至少需要针对表的 REFERENCES 权限。 如果创建索引的用户与创建视图的用户不同，则仅在创建索引时需要对视图的 ALTER 权限（包含在对架构的 ALTER 权限中）。

只能在与被引用的一个或多个表的所有者相同的视图上创建索引。 这也称为视图和表之间的原样所有权链。 通常，当表和视图位于同一架构中时，其架构所有者适用于架构中的所有对象。 因此可以存在创建视图却不是视图所有者的情况。 反过来也是可能的，即架构中的各个对象具有不同的显式所有者。 如果所有者不同于架构所有者，则 sys.tables 中的列 principal_id 会包含一个值。

创建索引视图：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
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
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) Service Pack 1 之前，仅在特定版本的 SQL Server 中支持查询优化器自动使用索引视图。 在 SQL Server Standard 版本上，必须使用 NOEXPAND 查询提示直接查询该索引视图。 从 SQL Server 2016 (13.x) Service Pack 1 起，所有版本都支持自动使用索引视图。 Azure SQL 数据库和 Azure SQL 托管实例还支持在不指定 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 (Transact-SQL)。

后续步骤