创建索引视图

适用于： SQL Server Azure 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 会返回值 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

¹ 将 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
  ROWSET 函数（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	%>
  FROM	TABLESAMPLE
  FROM	FOR SYSTEM_TIME	直接查询临时历史记录表
  WHERE	全文谓词（CONTAINS、FREETEXT、CONTAINSTABLE、FREETEXTTABLE）
  GROUP BY	CUBE、ROLLUP 或 GROUPING SETS 运算符	为每个 GROUP BY 列组合定义单独的索引视图
  GROUP BY	HAVING
  集合运算符		在 WHERE 子句中分别使用 OR、AND NOT 和 AND
  ORDER BY	ORDER BY
  ORDER BY	OFFSET

  源化列值	可能的替代方法
  弃用的大值列类型（text、ntext 和 image）	分别将列迁移到 varchar(max)、nvarchar(max) 和 varbinary(max)。
  xml 或 FILESTREAM 列
  索引键中的 float 1 列
  稀疏列集

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

  重要

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

日期/时间和 smalldatetime 建议

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

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

索引视图的性能注意事项

如果表被大量索引视图引用（或引用它的索引视图数量较少，但较为复杂），那么在该表上执行 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 权限中）。

只能在与被引用的一个或多个表的所有者相同的视图上创建索引。 此概念也称为视图和表之间的原样所有权链。 通常，当表和视图位于同一架构中时，其架构所有者适用于架构中的所有对象。 因此可以存在创建视图却不是视图所有者的情况。 另一方面，架构中的各个对象也可能具有不同的显式所有者。 如果所有者不同于架构所有者，则 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
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) 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。

相关内容

• 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)