创建索引视图 - SQL Server | Microsoft Learn

索引视图所需的 SET 选项

如果执行查询时启用不同的 SET 选项，则在数据库引擎中对同一表达式求值会产生不同结果。例如，将 SET 选项 CONCAT_NULL_YIELDS_NULL 设置为 ON 后，表达式 'abc' + NULL 会返回值 NULL 。但将 CONCAT_NULL_YIELDS_NULL 设置为 OFF 后，同一表达式会生成 abc 。

为了确保能够正确维护视图并返回一致结果，索引视图需要多个 SET 选项具有固定值。如果下列条件成立，则下表中的 SET 选项必须设置为 Required value 列中显示的值：

创建视图和视图上的后续索引。

创建视图时在视图中引用的基表。

对构成该索引视图的任何表执行任何插入、更新或删除操作时。此要求包括大容量复制、复制和分布式查询等操作。

查询优化器使用该索引视图生成查询计划。

¹ 将 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 DATA ACCESS = NO SQL 通过将


    DataAccess

属性设置为


    DataAccessKind.None

以及将


    SystemDataAccess

属性设置为


    SystemDataAccessKind.None

来确定。 EXTERNAL ACCESS = NO 对于 CLR 例程，该属性的默认设置为 NO。

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

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

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

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

Transact-SQL 函数可能的替代方法

日期/时间和 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;
CREATE VIEW Sales.vOrders
    WITH SCHEMABINDING
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;
--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX IDX_V1 ON Sales.vOrders (
    OrderDate,
    ProductID
接下来的两个查询演示了如何使用索引视图，即使该视图未在子句中 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;
--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)
			即将发布：在整个 2024 年，我们将逐步淘汰作为内容反馈机制的“GitHub 问题”，并将其取代为新的反馈系统。 有关详细信息，请参阅：https://aka.ms/ContentUserFeedback。
        
提交和查看相关反馈