通过 SQL Server 2005 索引视图提高性能
2007-05-13 09:24:50 来源：WEB开发网本文介绍了 SQL Server 2005 Enterprise Edition 中经过改进的索引视图功能。文中对索引视图进行了说明介绍，并讨论了可通过该功能改善性能的一些具体情况
一、索引视图
多年以来，Microsoft® SQL Server™ 一直支持创建称为视图的虚拟表。通常，这些视图的主要作用是：
| • | 提供一种安全机制，将用户限制到一个或多个基表的某个数据子集中。 |
| • | 提供一种机制，允许开发人员自定义用户通过逻辑方式查看存储在基表中的数据的方式。 |
通过 SQL Server 2000，SQL Server 视图的功能得到了扩展，实现了系统性能方面的收益。可在视图上创建唯一的聚集索引及非聚集索引，来提高最复杂的查询的数据访问性能。在 SQL Server 2000 和 2005 中，具有唯一的聚集索引的视图即为索引视图。本文所讨论的内容适用于 SQL Server 2005，其中有许多内容也适用于 SQL Server 2000。
从数据库管理系统 (DBMS) 的角度看来，视图是对数据（一种元数据类型）的一种描述。当创建了一个典型视图时，通过封装一个 SELECT 语句（定义一个结果集来表示为虚拟表）来定义元数据。当在另一个查询的 FROM 子句中引用视图时，将从系统目录检索该元数据，并替代该视图的引用扩展元数据。视图扩展之后，SQL Server 查询优化器会为执行查询编译一个执行计划。查询优化器会搜索针对某个查询的一组可能的执行计划，并根据对执行每个查询计划所需的实际时间的估计，选择所能找到的成本最低的计划。
对于非索引视图，解析查询所必需的视图部分会在运行时被具体化。任何计算（比如：联接或聚合）都在每个引用视图的查询执行时完成1。在视图上创建了唯一的聚集索引后，该视图的结果集随即被具体化，并保存在数据库的物理存储中，从而在执行时节省了执行这一高成本操作的开销。
在查询执行中，可通过两种方式使用索引视图。查询可直接引用索引视图，或者更重要的是，如果查询优化器确定该视图可替换成本最低的查询计划中的部分或全部查询，那么就可以选定它。在第二种情况中，使用索引视图替代基础表及其一般索引。不必在查询中引用视图以使查询优化器在查询执行时使用该视图。这使得现有的应用程序可以从新创建的索引视图中受益，而不必进行更改。
注意 索引视图是 SQL Server 2000 和 2005 各版本的一个功能。在 SQL Server 2000 和 2005 的 Developer 和 Enterprise 版本中，查询处理器可使用索引视图来解析结构上与该视图相匹配的查询，即便不按名称来引用视图。在其他版本中，必须按名称来引用视图，并对视图引用使用 NOEXPAND 提示来查询索引视图的内容。
通过索引视图改善性能运用索引提高查询性能不算是一个新概念；但是，索引视图提供了一些借助标准索引无法取得的性能收益。索引视图可通过以下方式提高查询性能：
| • | 可预先计算聚合并将其保存在索引中，从而在查询执行时，最小化高成本的计算。 |
| • | 可预先联接各个表并保存最终获得的数据集。 |
| • | 可保存联接或聚合的组合。 |
该图说明了当查询优化器使用索引视图时，通常所能取得的性能改进。所列举的查询在复杂性上有所不同（比如：聚合计算的数量、所用表的数量或谓词的数量）并包含来自真实的生产环境的具有数百万行的表。
其次，视图上的非聚集索引可提供更好的查询性能。与表上的非聚集索引类似，视图上的非聚集索引可提供更多选项，供查询优化器在编译过程中选择。例如，如果查询包含聚集索引所未涉及的列，那么优化器可在计划中选择一个或多个辅助索引，避免对索引视图或基表进行费时的完全扫描。
对架构添加索引会增加数据库的开销，因为索引需要持续的维护。在索引数量和维护开销间寻求适当的平衡点时，应谨慎权衡。
二、应用索引视图的优点
在实施索引视图前，分析数据库工作负荷。运用查询及各种相关工具（比如：SQL Profiler）方面的知识来确定可从索引视图获益的查询。频繁发生聚合和联接的情况最适合使用索引视图。无论是否频繁发生，只要某个查询需要很长的响应时间，同时快速获得响应的开销很高，那么就适合使用索引视图。例如，一些开发人员发现为高级主管们在月末运行的报告，创建预先计算和存储查询的应答的索引视图很有用。
不是所有的查询都能从索引视图中获益。与一般索引类似，如果未使用索引视图，就无法从中受益。在这种情况下，不仅无法实现性能改善，而且会在磁盘空间、维护和优化方面产生额外的成本。然而，当使用索引视图时，可大大改善（在数量级上）数据访问。这是因为查询优化器使用存储在索引视图（大幅降低了查询执行的成本）中预先计算的结果。
查询优化器仅考虑对具有高成本的查询使用索引视图。从而避免出现这样的情况：在查询优化成本高于使用索引视图所节约的成本时尝试匹配各种索引视图。在成本少于 1 的查询中很好使用索引视图。
从实施索引视图中获益的应用程序包括：
| • | 决策支持工作负荷 |
| • | 数据集市 |
| • | 数据仓库 |
| • | 联机分析处理 (OLAP) 存储和源 |
| • | 数据挖掘工作负荷 |
从查询类型和模式方面来看，获益的应用程序一般包含：
| • | 大型表的联接和聚合 |
| • | 查询的重复模式 |
| • | 几组相同或重叠的列上的重复聚合 |
| • | 相同键上相同表的重复联接 |
| • | 以上各项的组合 |
相反，执行许多写入操作的联机事务处理 (OLTP) 系统或者频繁更新的数据库应用程序可能无法运用索引视图，因为同时更新视图和底层基表会带来更高的维护成本。
查询优化器如何使用索引视图SQL Server 查询优化器自动决定何时对给定的查询执行使用索引视图。不必在查询中直接引用视图以供优化器在查询执行计划中使用。所以，现有的应用程序可运用索引视图，而不用更改应用程序本身；只是必须创建索引视图。
优化器考虑事项查询优化器通过考虑几个条件来决定索引视图能否涵盖整个或部分查询。这些条件对应查询中的一个 FROM 子句并由下列这几个部分组成：
| • | 查询 FROM 子句中的表必须是索引视图 FROM 子句中的表的超集。 | ||||||
| • | 查询中的联接条件必须是视图中的联接条件的超集。 | ||||||
| • | 查询中的聚合列必须可从视图中的聚合列的子集派生。 | ||||||
| • | 查询选择列表中的所有表达式必须可从视图选择列表或未包含在视图定义中的表派生。 | ||||||
| • | 如果与其他谓词所匹配的行的超集相匹配，那么该谓词将归入另一个谓词。例如，“T.a=10”归入“T.a=10 and T.b=20”。任何谓词都可归入其自身。视图中限制表值的那部分谓词必须归入查询中限制相同表的那部分谓词。此外，必须以 SQL Server 可验证的方式实现这一点。 | ||||||
| • | 属于视图定义中的表的查询搜索条件谓词的所有列必须出现在下列视图定义的一项或多项中：
情况 (1) 和 (2) 允许 SQL Server 对视图的列应用查询谓词，以便进一步限制视图的列。情况 (3) 比较特殊。在这种情况下，不需要对列进行筛选，因此该列不必出现在视图中。 |
如果查询不止包含一个 FROM 子句（子查询、派生表、UNION），优化器可能选择几个索引视图来处理查询，并将它们应用到不同 FROM 子句。2
本文档的末尾提供了涉及这些情况的具体查询。推荐的最佳实务是让查询优化器决定在查询执行计划中使用哪些索引（如果有的话）。
使用 NOEXPAND 视图提示当 SQL Server 处理按名称引用视图的查询时，视图的定义只有在仅引用基表时才会被正常扩展。这个过程称为视图扩展。其属于一种宏扩展形式。
NOEXPAND 视图提示可强制查询优化器将视图视为带有聚集索引的普通表。其可防止视图扩展。只有在 FROM 子句中直接引用索引视图，才会应用 NOEXPAND 提示。例如，
SELECT Column1, Column2, ...FROM Table1, View1 WITH (NOEXPAND) WHERE ...如要确保让 SQL Server 通过自己读取视图而不是从基表读取数据来处理查询，那么可使用 NOEXPAND。如果出于某种原因，SQL Server 选择了一个查询计划来对基表处理查询，而您想让其使用视图，那么可以考虑使用 NOEXPAND。必须在除 Developer 和 Enterprise 版本外的 SQL Server 的所有版本中使用 NOEXPAND 来让 SQL Server 直接对索引视图处理查询。可以看到 SQL Server 为计划的图形表达式选择了一个使用 SQL Server Management Studio 工具的显示预计的执行计划功能的语句。或者，可以看到使用 SHOWPLAN_ALL、SHOWPLAN_TEXT 或 SHOWPLAN_XML 的不同的非图形表达式。参阅 SQL Sever 联机丛书中有关 SHOWPLAN 的不同版本的相关讨论。
使用 EXPAND VIEWS 查询提示处理按名称引用视图的查询时，除非对视图引用添加 NOEXPAND 提示，否则 SQL Server 总会扩展视图。该提示会尝试匹配索引视图和扩展查询，除非在查询末尾的一个 OPTION 子句中指定 EXPAND VIEWS 查询提示。例如，假设数据库中有一个索引视图 View1。在下方的查询中，根据其逻辑定义（其 CREATE VIEW 语句）对 View1 进行了扩展，然后 EXPAND VIEWS 选项会阻止在计划中使用 View1 的索引视图来解析该查询。
SELECT Column1, Column2, ... FROM Table1, View1 WHERE ...
OPTION (EXPAND VIEWS)如要确保让 SQL Server 通过从查询所引用的基表直接访问数据来处理该查询，而不必访问索引视图，那么可使用 EXPAND VIEWS。在某些情况下，EXPAND 视图有助于消除因使用索引视图而导致的锁争用。在测试应用程序时，NOEXPAND 和 EXPAND VIEWS 都可帮助用户在使用和不使用索引视图的情况下进行性能评估。
三、SQL Server 2005 的索引视图的新增功能
与 SQL Server 2000 相比，SQL Server 2005 包含了许多索引视图的改进功能。可索引的视图组已扩展至包含基于下列各项的视图：
• 标量聚合，包括 SUM 和不带 GROUP BY 的 COUNT_BIG。
• 标量表达式和用户定义的功能 (UDFs)。例如，给定一个表 T(a int, b int, c int) 和一个标量 UDF dbo.MyUDF(@x int)，T 上定义的索引视图可包含一个计算列（比如：a+b 或 dbo.MyUDF(a)）。
• 不精确的永久性列。不精确的列是一种浮型或实型的列，或者是一种派生自浮型或实型列的计算列。在 SQL Server 2000 中，如果不属于索引键的一部分，不精确的列就可用于索引视图的选择列表。不精确的列不能用于视图定义中的其他地方（比如：WHERE 或 FROM 子句）。如果不精确的列永久保存在基表中，那么 SQL Server 2005 允许其加入键或视图定义。永久性列包含常规列和标记为 PERSISTED 的计算列。
• 不精确的非永久性列无法加入索引或索引视图的根本原因是：必须使数据库脱离原计算机，然后再附加到另一台计算机。完成转移之后，保存在索引或索引视图中的所有计算列值在新硬件上的派生方式必须与旧硬件完全相同，精确到每个位。否则，这些索引视图在新硬件上会遭到逻辑破坏。由于这种破坏，在新硬件上，针对索引视图的查询会根据计划是否使用了索引视图或基表来派生视图数据，返回不同的应答。此外，无法在新计算机上正常维护索引视图。可惜，不同计算机上的浮点硬件（即便采用相同制造商的相同处理器体系结构）在处理器的版本上并不总是完全相同。对于某些浮点值 a 和 b，固件升级可能导致新硬件上的 (a*b) 不同于旧硬件上的 (a*b)。例如，结果可能非常相近，但仍存在细微差别。在进行索引之前一直保留不精确的计算值可解决这种分离/附加的不一致性问题，因为在进行索引和索引视图的数据库更新和维护期间，在相同的计算机上评估了所有表达式。
• 通用语言运行时 (CLR) 类型。SQL Server 2005 的一个主要的新功能是支持基于 CLR 的用户定义的类型 (UDT) 和 UDF。假如列或表达式具有确定性或是永久且精确的，或者二者兼具，那么就可在 CLR UDT 列或从这些列派生而来的表达式上定义索引视图。不能在索引视图上使用 CLR 用户定义的聚合。
优化器匹配查询和索引视图（使之可在查询计划中使用）的功能经扩展包含：
• 新的表达式类型，位于查询或视图的 SELECT 列表或条件中，涉及：
• 标量表达式（比如 (a+b)/2）。
• 标量聚合。
• 标量 UDF。
• 间隔归入。优化器可检测索引视图定义中的间隔条件是否覆盖或“归入”查询中的间隔条件。例如，优化器可确定“a>10 and a<20”覆盖“a>12 and a<18”。
• 表达式等价。某些表达式虽然在语法上有所不同，但最终的结果却相同，那么可以将其视为等价。例如，“a=b and c<>10”与“10<>c and b=a”等价。
另外，如果数据库中存在大量索引视图，那么对比在其上定义视图的表的编译性能，SQL Server 2005 通常要比 SQL Server 2000 快很多。.
四、设计注意事项
对数据库系统确定一组适当的索引可能很复杂。如果在设计一般索引时需要考虑众多可能性，那么对架构添加索引视图会大幅提高设计和潜在结果的复杂性。例如，索引视图可用于：
• 查询中引用的表的任何子集。
• 该表子集的查询中的条件的任何子集。
• 组合的列。
• 聚合函数（比如：SUM）。
应同时设计表和索引视图上的索引，以便从每个构造中获得最佳结果。由于索引和索引视图对给定查询可能都很有用，因此分开设计会导致多余的建议，从而产生较高的存储和维护开销。优化数据库的物理设计时，必须权衡一组不同的查询和数据库系统必须支持的更新的性能要求。所以，对索引视图确定一项适当的物理设计是一种富有挑战性的任务，应尽可能使用数据库优化顾问 (Database Tuning Advisor)。
如果为建立一个特殊的查询，查询优化器考虑了许多索引视图，那么查询优化成本就会显著增加。查询优化器可能会考虑在查询中的表的任何子集上定义的所有索引视图。在拒绝视图之前，必须调查每个视图以便进行替换。这可能要花一些时间，尤其当给定查询存在数百个这类视图时。
在其上创建一个唯一的聚集索引之前，视图必须满足几项要求。在设计阶段，考虑这些要求：
• 视图以及视图中引用的所有表必须在相同的数据库中，并具有相同的所有者。
• 索引视图不必包含查询中引用的供优化器使用的所有表。
• 在视图上创建任何其他的索引之前，必须先创建一个唯一的聚集索引。
• 在创建基表、视图和索引时，以及基表和视图中的数据被修改时，必须正确设置某些 SET 选项（在本文档后面所有详述）。此外，除非这些 SET 选项正确无误，否则查询优化器不会考虑索引视图。
• 必须使用架构绑定创建视图，并且还必须通过 SCHEMABINDING 选项创建该视图中引用的任何用户定义的函数。
• 需要额外的磁盘空间来保存索引视图所定义的数据。
设计方针
设计索引视图时考虑这些指导方针：
| • | 设计可供几个查询或多项操作使用的索引视图。 例如，包含列的 SUM 和 COUNT_BIG 的索引视图可供包含函数 SUM、COUNT、COUNT_BIG 或 AVG 的查询使用。查询的速度会更快，因为只需对视图中少量的行进行检索，而不必检索基表中所有的行，而且执行 AVG 函数所需的一部分计算已经完成。 | ||||
| • | 使索引键保持简洁。 通过在索引键中尽可能使用最少的列和字节，可对索引视图的列实现更高效的访问，因为索引视图的列更窄，键比较的速度较更宽的键快一些。另外，在索引视图上定义的每个非聚集索引中，聚集索引键都被用作行定位器。较大的索引键的成本随视图上非聚集索引的数量成比例增长。 | ||||
| • | 考虑最终索引视图的大小。 对于纯聚合，如果索引视图的大小与原始表的大小不相上下，可能就不会实现巨大的性能改善。 | ||||
| • | 设计多个较小的索引视图来局部加速过程。 可能无法总对整个查询设计一个索引视图。如要怎么做，考虑创建若干个索引视图，各执行部分查询。 考虑这几个例子：
|
数据库优化顾问 (DTA3) 是 SQL Server 2005 的一项功能，可帮助管理员优化物理数据库设计。除了建议使用基表上的索引以及表和索引分区策略外，DTA 还推荐使用索引视图。使用 DTA 可加强管理员确定索引、索引视图和分区策略（可优化对数据库执行的查询的典型组合的性能）的组合的能力。DTA 会向用户推荐广泛的索引视图。其中包括运用 SQL Server 2005 的索引视图的新功能（在“SQL Server 2005 的索引视图有哪些新增功能？”一节有所描述）的索引视图。DTA 并没有排除让数据库管理在设计物理存储结构时做出恰当判断的需要。但是，它可以简化物理数据库的设计过程。DTA 通过推荐一组假定的索引，索引视图和分区结果，与基于成本的查询优化器协同工作。DTA 使用优化器来估计当使用和不使用这些结构时的工作负荷成本，并推荐可提供较低的总成本的结构。
因为数据库优化顾问强制执行所有必须的 SET 选项（确保结果集正确无误），所以将成功完成索引视图的创建。然而，如果未能按要求设置选项，用户的应用程序可能无法运用这些视图。如果未按要求指定 SET 选项，对加入索引视图定义的表执行的插入、更新或删除操作就有可能失败。
更新数据时索引视图会有什么变化？与其他任何索引一样，当基表数据变化时，SQL Server 会自动维护索引视图。对于一般索引，每个索引都直接与一个表相关联。随着在基础表上执行每一项 INSERT、UPDATE 或 DELETE 操作，索引将被相应地更新，从而使保存在索引中的值总是与表保持一致。
索引视图也得到相同的维护；但是，如果视图引用了若干个表，那么更新任何一个表都需要更新索引视图。不同于一般索引，在任何参与的表中插入一行都可能导致索引视图中发生多行更改。这是因为所插入的行可能与另一个表的多个行相联接。更新和删除行的情况也一样。因此，索引视图的维护成本可能比维护表上的索引更高。相反，维护具有高选择性条件的索引视图的成本可能要比维护表上的索引低得多，因为多数对视图所引用的基表的插入、删除和更新操作不会影响视图。不用访问其他数据库数据就可为索引视图筛选掉这些操作。
在 SQL Server 中，可更新某些视图。当某个视图可更新时，将使用 INSERT、UPDATE 和 DELETE 语句通过视图直接修改底层基表。在视图上创建索引不会阻止视图的更新。索引视图的更新确实会导致视图下基表的更新。这些更新会作为索引视图维护的一部分自动传播回索引视图。有关可更新的视图的详细信息，参阅面向 SQL Server 2005 的 SQL Server 联机丛书中的“通过视图修改数据”。
维护成本注意事项设计索引视图时应考虑下面这几点：
| • | 索引视图的数据库需要附加存储。索引视图的结果集在物理上通过与典型表存储相似的方式保留在数据库中。 |
| • | SQL Server 会自动维护视图；因此，对定义了视图的基表进行的任何更改都可能引发对索引视图进行一项或多项更改。所以，将产生额外的维护开销。 |
视图所获得的净性能提升为其所实现的总查询执行成本节约与存储和维护成本的差值。
比较容易获得接近于视图所需的存储。通过 SQL Server Management Studio 工具——显示预计的执行计划，评估视图定义所封装的 SELECT 语句。该工具将生成查询所返回的行数和行大小的近似值。通过将这两个值相乘，就可以获得接近于可能的视图大小；但是，只是近似。只有在视图定义中执行查询或在视图上创建索引，才能确定视图上索引的实际大小。
从 SQL Server 所执行的自动维护注意事项的角度来看，显示预计的执行计划功能可能会让用户在一定程度上了解这一开销的影响。如果通过 SQL Server Management Studio 评估修改视图的语句（视图上的 UPDATE、基表中的 INSERT），对该语句显示的执行计划将包括该语句的维护操作。如果就该操作在生产环境中所要执行的次数考虑该成本，那么可能会产生视图维护成本。
通常建议尽可能对视图或其底下的基表成批（而非单独）执行任何修改或更新操作。这样就会降低视图维护开销。
五、创建索引视图
创建索引视图所需的步骤对于视图的成功执行至关重要。
1. | 针对将在视图中引用的所有现有表，确认 ANSI_NULLS 的设置正确无误。 |
2. | 创建任何新表之前，确认对下表所示的当前会话正确设置了 ANSI_NULLS。 |
3. | 创建任何新表之前，确认对下表所示的当前会话正确设置了 ANSI_NULLS 和 QUOTED_IDENTIFIER。 |
4. | 确认视图定义具有确定性。 |
5. | 使用 WITH SCHEMABINDING 选项创建视图。 |
6. | 在视图上创建唯一的聚集索引之前，确认会话的 SET 选项的设置正确无误，如下图所示。 |
7. | 在视图上创建唯一的聚集索引。 |
8. | 可用 OBJECTPROPERTY 函数检查现有表或视图上 ANSI_NULLS 和 QUOTED_IDENTIFIER 的值。 |
如果在执行查询时对当前会话启用了不同的 SET 选项，评估相同的表达式可在 SQL Server 2005 中产生不同的结果。例如，SET 选项 CONCAT_NULL_YIELDS_NULL 被设为 ON 后，表达式 'abc' + NULL 会返回值 NULL。但当 CONCAT_NULL_YIEDS_NULL 被设为 OFF 后，相同的表达式会生成 'abc'。对于当前会话和视图所引用的对象，索引视图需要几个 SET 选项的固定值，以确保正确维护视图并返回一致的结果。
只要存在下列条件，就必须按下表中“必需的值”一列所示的值对当前会话设置 SET 选项：
| • | 创建了索引视图。 | |||||||||||||||||||||||||||||||||||
| • | 在加入索引视图的任何表上执行了任何 INSERT、UPDATE 或 DELETE 操作。 | |||||||||||||||||||||||||||||||||||
| • | 查询优化器用索引视图生成查询计划。
|
ARITHABORT4 选项必需被设为 ON，以便使当前会话创建索引视图，但是在 SQL Server 2005 中，只要 ANSI_WARNINGS 的值为 ON，该选项就会自动被设为 ON，所以不必对其进行设置。如果使用 OLE DB 或 ODBC 服务器连接，只需修改 ARITHABORT 设置的值。必须使用 sp_configure 在服务器级别或使用 SET 命令从应用程序正确设置所有 DB LIB 值。有关 SET 选项的详细信息，参阅 SQL Server 联机丛书中的“使用选项”。
使用具有确定性的功能索引视图的定义必须具有确定性。如果选择列表中的所有表达式以及 WHERE 和 GROUP BY 子句都具有确定性，那么视图就具有确定性。具有确定性的表达式总是在通过一组特定的输入值对其进行评估时，返回相同的结果。只有具有确定性的函数才会加入具有确定性的表达式。例如，DATEADD 函数具有确定性，因为对于任何给定的一组参数值，该函数总对它的三个参数返回相同的结果。GETDATE 不具有确定性，因为它总调用相同的参数，而其返回的值在每次执行时都会发生变化。详细信息，参阅面向 SQL Server 2005 的 SQL Server 联机丛书中的“具有和不具有确定性的函数”。
即使某个表达式具有确定性（如果其包含浮点表达式），确切的结果可能依处理器体系结构或微码的版本而定。为了在计算机间迁移数据库时确保 SQL Server 2005 中数据的完整性，这种表达式只能作为索引视图的非键列加入。不含浮点表达式的具有确定性的表达式被认为是精确的。只有永久和/或精确的具有确定性的表达式才可加入键列以及索引视图的 WHERE 或 GROUP BY 子句。永久性表达式是对已保存列的引用，包括一般列和标为 PERSISTED 的计算列。
用 COLUMNPROPERTY 函数和 IsDeterministic 属性确定视图列是否具有确定性。用 COLUMNPROPERTY 函数和 IsPrecise 属性确定带有 SCHEMABINDING 的视图中的具有确定性的列是精确的。如果属性为 TRUE，COLUMNPROPERTY 将返回 1；如为 FALSE，则返回 0；而如果为 NULL，则表示无效输入。例如，在此脚本中
CREATE TABLE T(a int, b real, c as getdate(), d as a+b)
CREATE VIEW VT WITH SCHEMABINDING AS SELECT a, b, c, d FROM dbo.T
SELECT object_id('VT'), COLUMNPROPERTY(object_id('VT'),'b','IsPrecise')SELECT 对 IsPrecise 返回 0，因为 b 列为实型。可通过 COLUMNPROPERTY 做一些实验，确认 T 的其他列是否具有确定性并是精确的。
其他要求可索引的视图集合是可能的视图集合的一个子集。任何可索引的视图在有或没有索引的情况下都可存在。
除了设计方针（“使用 SET 选项获得一致的结果”和“使用具有确定性的函数”这两节）中所列的要求外，还必须满足下列要求，以便在视图上创建唯一的聚集索引。
有关基表的要求| • | 视图所引用的基表必须具有在创建表时所设的 SET 选项 ANSI_NULLS 的正确的值。可用 OBJECTPROPERTY 函数检查现有表上的 ANSI_NULLS 的值。 |
| • | 必须使用 WITH SCHEMABINDING 选项创建视图所引用的用户定义的函数。 |
| • | 必须使用 WITH SCHEMABINDING 选项创建视图。 |
| • | 必须由使用双结构名称 (schemaname.tablename) 的视图引用表。 |
| • | 必须由使用双结构名称 (schemaname.functionname) 的视图引用用户定义的函数。 |
| • | 必须正确设置 SET 选项 ANSI_NULLS 和 QUOTED_IDENTIFIER。 |
如要在 SQL Server 2005 中的视图上创建一个索引，相应的视图定义必须包含：
ANY、NOT ANY | OPENROWSET、OPENQUERY、OPENDATASOURCE |
不精确的（浮型、实型）值上的算术 | OPENXML |
COMPUTE、COMPUTE BY | ORDER BY |
CONVERT 生成一个不精确的结果 | OUTER 联接 |
COUNT(*) | 引用带有一个已禁用的聚集索引的基表 |
GROUP BY ALL | 引用不同数据库中的表或函数 |
派生的表（FROM 列表中的子查询） | 引用另一个视图 |
DISTINCT | ROWSET 函数 |
EXISTS、NOT EXISTS | 自联接 |
聚合结果（比如：SUM(x)+SUM(x)）上的表达式 | STDEV、STDEVP、VAR、VARP、AVG |
全文谓词 (CONTAINS、FREETEXT、CONTAINSTABLE、FREETEXTTABLE) | 子查询 |
不精确的常量（比如：2.34e5） | 可为空的表达式上的 SUM |
内嵌或表值函数 | 表提示（比如：NOLOCK） |
MIN、MAX | text、ntext、image、filestream 或 XML 列 |
不具有确定性的表达式 | TOP |
非 unicode 排序 | UNION |
SQL Server 2005 可检测到的矛盾情况表示视图将为空（比如，当 0=1 及 ...） |
注意 索引视图可能包含浮型和实型列；但是，如果这类列为非永久性的计算列，则不能包含在聚集索引键中。
GROUP BY 限制如果存在 GROUP BY，VIEW 定义为：
| • | 一定包含 COUNT_BIG(*)。 |
| • | 一定不包含 HAVING、CUBE、ROLLUP 或 GROUPING()。 |
这些限制仅适用于索引视图定义。即便不能满足上述 GROUP BY 限制，查询也可以在其执行计划中使用索引视图。
有关索引的要求| • | 执行 CREATE INDEX 语句的用户必须是视图所有者。 |
| • | 如果视图定义包含 GROUP BY 子句，唯一的聚集索引的键只能引用 GROUP BY 子句所指定的列。 |
| • | 一定不能在启用 IGNORE_DUP_KEY 选项的情况下创建索引。 |
六、示例
本节中的例子阐述了如何结合两类主要的查询使用索引视图：聚合和联接。同时，说明了查询优化器在确定某个索引视图是否适用时所用的条件。有关完整的条件列表的信息，参阅“查询优化器如何使用索引视图”。
这些查询基于 AdventureWorks 中的表。AdventureWorks 是 SQL Server 2005 所提供的示例数据库，并可作为写入方式来执行。在创建视图前后，用户可能想用 SQL Server Management Studio 中显示预计的执行计划工具，来查看查询优化器所选择的计划。虽然这些例子说明了优化器选择低成本执行计划的方式，但是 AdventureWorks 示例由于太小而无法显示出性能方面的提升。
在开始运用这些示例之前，确保通过运行下列命令对会话设置正确的选项：
设置
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET CONCAT_NULL_YIELDS_NULL ON
SET NUMERIC_ROUNDABORT OFF
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON下列查询显示了两种方法用于从 Sales.SalesOrderDetail 表返回具有最大总折扣的五个产品。
查询 1
SELECT TOP 5 ProductID, Sum(UnitPrice*OrderQty) -
Sum(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rebate
FROM Sales.SalesOrderDetail
GROUP BY ProductID
ORDER BY Rebate DESC查询 2
SELECT TOP 5 ProductID,
SUM(UnitPrice*OrderQty*UnitPriceDiscount) AS Rebate
FROM Sales.SalesOrderDetail
GROUP BY ProductID
ORDER BY Rebate DESC查询优化器所选的执行计划包含：
| • | 一个聚集索引扫描，位于估计行数为 121,317 的 Sales.SalesOrderDetail 表上。 |
| • | 一个哈希匹配/聚合操作符，用于将所选的行放入基于 GROUP BY 列的哈希表，并计算每行的 SUM 聚合。 |
| • | 一个 TOP 5 分类操作符，基于 ORDER BY 子句。 |
视图 1
添加包含 Rebate 列所需聚合的索引视图将更改“查询 1”的查询执行计划。在大型表（含数百万行）上，查询的性能也会得到大幅提升。
CREATE VIEW Vdiscount1 WITH SCHEMABINDING AS
SELECT SUM(UnitPrice*OrderQty) AS SumPrice,
SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS SumDiscountPrice,
COUNT_BIG(*) AS Count, ProductID
FROM Sales.SalesOrderDetail
GROUP BY ProductID
GO
CREATE UNIQUE CLUSTERED INDEX VDiscountInd ON Vdiscount1 (ProductID)第一个查询的执行计划显示 Vdiscount1 视图被优化器所用。然而，该视图将不被第二个查询所用，因为其不包含 SUM(UnitPrice*OrderQty*UnitPriceDiscount) 聚合。可再创建一个索引视图，来同时应付这两个查询。
视图 2
CREATE VIEW Vdiscount2 WITH SCHEMABINDING AS
SELECT SUM(UnitPrice*OrderQty)AS SumPrice,
SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount))AS SumDiscountPrice,
SUM(UnitPrice*OrderQty*UnitPriceDiscount)AS SumDiscountPrice2,
COUNT_BIG(*) AS Count, ProductID
FROM Sales.SalesOrderDetail
GROUP BY ProductID
GO
CREATE UNIQUE CLUSTERED INDEX VDiscountInd ON Vdiscount2 (ProductID)使用这个索引视图，在丢弃 Vdiscount1 后，这两个查询的查询执行计划现在包含：
| • | 一个聚集索引扫描，位于估计行数为 266 的 Vdiscount2 视图上 |
| • | 一个 TOP 5 分类函数，基于 ORDER BY 子句 |
查询优化器选择了该视图，因为虽然没有在查询中引用该视图，但其提供了最低的执行成本。
查询 3
“查询 3”与上述查询类似，但 ProductID 被列 SalesOrderID （未包含在视图定义中）所替换。这违反了条件：视图定义中表上的选择列表中的所有表达式必须派生自视图选择列表，以便使用查询计划中的索引视图。
SELECT TOP 3 SalesOrderID,
SUM(UnitPrice*OrderQty*UnitPriceDiscount) OrderRebate
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
ORDER BY OrderRebate DESC必须用一个单独的索引视图来应付该查询。可修改 Vdiscount2 以包含 SalesOrderID；但是，结果视图将和原始表包含同样多的行，并不会通过使用基表提高性能。
查询 4
该查询可生成每个产品的平均价格。
SELECT p.Name, od.ProductID,
AVG(od.UnitPrice*(1.00-od.UnitPriceDiscount)) AS AvgPrice,
SUM(od.OrderQty) AS Units
FROM Sales.SalesOrderDetail AS od, Production.Product AS p
WHERE od.ProductID=p.ProductID
GROUP BY p.Name, od.ProductID复杂的聚合（比如：STDEV、VARIANCE、AVG）不能包含在索引视图的定义中。然而，通过包含（经组合）执行复杂聚合的一些简单的聚合函数，索引视图可用以执行含 AVG 的查询。
视图 3
该索引视图包含执行 AVG 函数所需的简单聚合函数。在创建“视图 3”后执行“查询 4”时，执行计划将显示所用的视图。优化器可从视图的简单聚合列 Price 和 Count 派生 AVG 表达式。
CREATE VIEW View3 WITH SCHEMABINDING AS
SELECT ProductID, SUM(UnitPrice*(1.00-UnitPriceDiscount)) AS Price,
COUNT_BIG(*) AS Count, SUM(OrderQty) AS Units
FROM Sales.SalesOrderDetail
GROUP BY ProductID
GO
CREATE UNIQUE CLUSTERED INDEX iv3 ON View3 (ProductID)查询 5
该查询与“查询 4”相同，但包含一个附加的搜索条件。即使附加的搜索条件只从未包含在视图定义中的表引用列，“视图 3”也将作用于该查询。
SELECT p.Name, od.ProductID,
AVG(od.UnitPrice*(1.00-UnitPriceDiscount)) AS AvgPrice,
SUM(od.OrderQty) AS Units
FROM Sales.SalesOrderDetail AS od, Production.Product AS p
WHERE od.ProductID=p.ProductID AND p.Name like '%Red%'
GROUP BY p.Name, od.ProductID查询 6
查询优化器无法对该查询使用“视图 3”。添加的搜索条件 od.UnitPrice>10 包含来自视图定义中表的列，但该列不显示在 GROUP BY 列表中，而搜索谓词也不显示在视图定义中。
SELECT p.Name, od.ProductID,
AVG(od.UnitPrice*(1.00-UnitPriceDiscount)) AS AvgPrice,
SUM(od.OrderQty) AS Units
FROM Sales.SalesOrderDetail AS od, Production.Product AS p
WHERE od.ProductID=p.ProductID AND p.Name like '%Red%'
GROUP BY p.Name, od.ProductID查询 7
相反，查询优化器可对“查询 7”使用“视图 3”，因为新的搜索条件 od.ProductID in (1,2,13,41) 中定义的列包含在视图定义的 GROUP BY 子句中。
SELECT p.Name, od.ProductID,
AVG(od.UnitPrice*(1.00-UnitPriceDiscount)) AS AvgPrice,
SUM(od.OrderQty) AS Units
FROM Sales.SalesOrderDetail AS od, Production.Product AS p
WHERE od.ProductID = p.ProductID AND od.UnitPrice > 10
GROUP BY p.Name, od.ProductID视图 4
通过包含视图定义中的 SumPrice 和 Count 列以便计算查询中的 AVG，该视图将满足“查询 6”的条件。
CREATE VIEW View4 WITH SCHEMABINDING AS
SELECT p.Name, od.ProductID,
SUM(od.UnitPrice*(1.00-UnitPriceDiscount)) AS SumPrice,
SUM(od.OrderQty) AS Units, COUNT_BIG(*) AS Count
FROM Sales.SalesOrderDetail AS od, Production.Product AS p
WHERE od.ProductID = p.ProductID AND od.UnitPrice > 10
GROUP BY p.Name, od.ProductID
GO
CREATE UNIQUE CLUSTERED INDEX VdiscountInd on View4 (Name, ProductID)查询 8
“视图 4”上相同的索引也将用于在其中添加对表 Sales.SalesOrderHeader 的联接的查询。该查询满足条件：查询 FROM 子句中所列的表是索引视图的 FROM 子句中的表的超集。
SELECT p.Name, od.ProductID,
AVG(od.UnitPrice*(1.00-UnitPriceDiscount)) AS AvgPrice,
SUM(od.OrderQty) AS Units
FROM Sales.SalesOrderDetail AS od, Production.Product AS p,
Sales.SalesOrderHeader AS o
WHERE od.ProductID = p.ProductID AND o.SalesOrderID = od.SalesOrderID
AND od.UnitPrice > 10
GROUP BY p.Name, od.ProductID最后两个查询在“查询 8”的基础上进行了修改。每个修改后的查询都违反了优化器的条件之一，并且不同于“查询 8”，无法使用“视图 4”。
查询 8a
“查询 8a”(Q8a) 无法使用索引视图，因为 WHERE 子句无法将视图定义中的 UnitPrice > 10 与查询中的 UnitPrice > 25 相匹配，而且 UnitPrice 未出现在视图中。查询搜索条件谓词必须是视图定义中的搜索条件谓词的超集。
SELECT p.Name, od.ProductID, AVG(od.UnitPrice*(1.00-UnitPriceDiscount))
AvgPrice, SUM(od.OrderQty) AS Units
FROM Sales.SalesOrderDetail AS od, Production.Product AS p,
Sales.SalesOrderHeader AS o
WHERE od.ProductID = p.ProductID AND o.SalesOrderID = od.SalesOrderID
AND od.UnitPrice > 25
GROUP BY p.Name, od.ProductID查询 8b
注意，表 Sales.SalesOrderHeader 不加入索引视图 V4 定义。尽管这样，在该表上添加一个谓词将不允许使用索引视图，因为所添加的谓词可能会更改或消除加入下方“查询 8b”所示的聚合的其他行。
SELECT p.Name, od.ProductID, AVG(od.UnitPrice*(1.00-UnitPriceDiscount))
AS AvgPrice, SUM(od.OrderQty) AS Units
FROM Sales.SalesOrderDetail AS od, Production.Product AS p,
Sales.SalesOrderHeader AS o
WHERE od.ProductID = p.ProductID AND o.SalesOrderID = od.SalesOrderID
AND od.UnitPrice > 10 AND o.OrderDate > '20040728'
GROUP BY p.Name, od.ProductID视图 4a
“视图 4a”通过将 UnitPrice 列包含在选择列表和 GROUP BY 子句中，扩展了“视图 4”。“查询 8a”可使用“视图 4a”，因为将进一步筛选 UnitPrice 值（已知大于 10）以便只留下大于 25 的值。以下是间隔归入的一个例子。
CREATE VIEW View4a WITH SCHEMABINDING AS
SELECT p.Name, od.ProductID, od.UnitPrice,
SUM(od.UnitPrice*(1.00-UnitPriceDiscount)) AS SumPrice,
SUM(od.OrderQty) AS Units, COUNT_BIG(*) AS Count
FROM Sales.SalesOrderDetail AS od, Production.Product AS p
WHERE od.ProductID = p.ProductID AND od.UnitPrice > 10
GROUP BY p.Name, od.ProductID, od.UnitPrice
GO
CREATE UNIQUE CLUSTERED INDEX VdiscountInd
ON View4a (Name, ProductID, UnitPrice)视图 5
“视图 5”在其选择和 GROUP BY 列表中包含一个表达式。请注意，LineTotal 是一个计算列，因此本身是一个表达式。反过来，该表达式嵌套在对 FLOOR 函数的调用中。
CREATE VIEW View5 WITH SCHEMABINDING AS
SELECT FLOOR(LineTotal) FloorTotal, COUNT_BIG(*) C
FROM Sales.SalesOrderDetail
GROUP BY FLOOR(LineTotal)
GO
CREATE UNIQUE CLUSTERED INDEX iView5 ON View5(FloorTotal)查询 9
“查询 9”在其选择和 GROUP BY 列表中包含表达式 FLOOR(LineTotal)。通过对 SQL Server 2005 中表达式的视图匹配的新扩展，该查询使用“视图 5”上的索引。
SELECT TOP 5 FLOOR(LineTotal), Count(*)
FROM Sales.SalesOrderDetail
GROUP BY FLOOR(LineTotal)
ORDER BY COUNT(*) DESC视图 6
“视图 6”存储月末三天中有关线项目的信息。这样可将这些行聚集在少量页面上，从而可以迅速应对这些天里对 Sales.SalesOrderDetail 的查询。
CREATE VIEW View6 WITH SCHEMABINDING AS
SELECT SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty,
ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount, rowguid,
ModifiedDate
FROM Sales.SalesOrderDetail
WHERE ModifiedDate IN ( convert(datetime, '2004-07-31', 120),
convert(datetime, '2004-07-30', 120),
convert(datetime, '2004-07-29', 120) )
GO
CREATE UNIQUE CLUSTERED INDEX VEndJulyO4Ind
ON View6(SalesOrderID, SalesOrderDetailID)
GO查询 10
下面的查询可匹配“视图 6”，同时系统可生成一个计划，用于扫描视图上的 VendJuly04Ind 索引，但不扫描整个 Sales.SalesOrderDetail 表。此查询还说明了表达式等价（由于查询中日期的顺序不同于视图，而且数据格式也不同）和谓词归入（由于查询要求将结果的子集保存在视图中）。
SELECT h.*, SalesOrderDetailID, CarrierTrackingNumber, OrderQty,
ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount, d.rowguid,
d.ModifiedDate
FROM Sales.SalesOrderHeader as h, Sales.SalesOrderDetail as d
WHERE (d.ModifiedDate = '20040729' OR d.ModifiedDate = '20040730')
and d.SalesOrderID=h.SalesOrderID视图 7
开发人员有时还会发现使用索引视图强制专门的完整性约束很方便。例如，可通过索引视图强制约束：“除非列中存在多个 0 值，否则表 T 的列 a 就是唯一的”。下方索引视图“视图 7”就强制了这一约束。如果运行下面的脚本，其将成功运行直至最终的插入操作。该语句被禁止，因为其添加了一个非零重复值。
USE tempdb
GO
CREATE TABLE T(a int)
GO
CREATE VIEW View7 WITH SCHEMABINDING
AS SELECT a
FROM dbo.T
WHERE a <> 0
GO
CREATE UNIQUE CLUSTERED INDEX IV on View7(a)
GO
-- legal:
INSERT INTO T VALUES(1)
INSERT INTO T VALUES(2)
INSERT INTO T VALUES(0)
INSERT INTO T VALUES(0) -- duplicate 0
-- dissalowed:
INSERT INTO T VALUES(2)七、有关索引视图的常见问题
问：为何对可创建索引的视图类型存在限制？
答：为了确保在逻辑上可对视图进行增量维护，限制创建维护成本较高的视图，并限制 SQL Server 实施的复杂性。较大的视图集不具有确定性并与内容相关；其内容的“更改”独立于 DML 操作。无法对这些内容进行索引。在其定义中调用 GETDATE 或 SUSER_SNAME 的任何视图就属于这类视图。
问：视图上的第一个索引为何必须为 CLUSTERED 和 UNIQUE？
答：必须为 UNIQUE 以便在维护索引视图期间，轻松地按键值查找视图中的记录，并阻止创建带有重复项目的视图（要求维护特殊的逻辑）。必须为 CLUSTERED，因为只有聚集索引才能在强制唯一性的同时存储行。
问：为何查询优化器不选取我的索引视图用于查询计划？
答：优化器不选取索引视图主要有三种原因：
(1) 使用 SQL Server Enterprise 或 Developer 版本之外的其他版本。只有 Enterprise 和 Developer 版本才支持自动的查询对索引视图匹配。按名称引用索引视图并包含 NOEXPAND 提示，让查询处理器使用所有其他版本中的索引视图。
(2) 使用索引视图的成本可能超出从基表获取数据的成本，或者查询过于简单，使得针对基表的查询的速度既快又容易查找。当在较小的表上定义索引视图时，经常会发生这种情况。如要强制查询处理器使用索引视图，那么可使用 NOEXPAND 提示。如果最初不通过显式的方式引用视图，这样做就可能要求重新编写查询。您可获得带有 NOEXPAND 的查询的实际成本，并将之与不引用该视图的查询计划的实际成本相比较。如果两者的成本相近，那么您就可以认定用不用索引视图都不重要。
(3) 查询优化器不将查询与索引视图相匹配。重新检查视图和查询的定义，确保两者在结构上可相匹配。CASTS、converts 以及其他在逻辑上不会更改查询结果的表达式可能会阻止匹配。另外，表达式规范化和等价以及 SQL Server 执行的归入测试方面存在一些限制。可能无法显示某些等价表达式是相同的，或者逻辑上被其他表达式归入的表达式被真正归入，因此可能会错失匹配。
问：我每周更新一次数据仓库。索引视图使查询速度大大提升，却降低了每周更新的速度？该怎么办呢？
答：可以考虑在每周更新前丢弃索引视图，更新完后再重新创建。
问：我的视图存在重复项目，而我确实想对其进行维护。该怎么办呢？
答：可以考虑创建一个视图，按您所要的视图中的所有列和表达式进行分组，并添加一个 COUNT_BIG(*) 列，然后在组合的列上创建一个唯一的聚集索引。分组过程可确保唯一性。虽然不是完全相同的视图，但可以满足您的需要。
问：我在一个视图上定义了另一个视图。SQL Server 不让我索引顶级视图。该怎么办呢？
答：可以考虑手动将嵌套视图的定义扩展到顶级视图，然后对其进行索引（索引最低层的视图，或者不索引该视图）。
问：为何一定要对索引视图定义 WITH SCHEMABINDING？
答：为了
•
使用 schemaname.objectname 明确识别视图所引用的所有对象，而不管是哪个用户访问该视图，同时
•
不会以导致视图定义非法或强制 SQL Server 在该视图上重新创建索引的方式，更改视图定义中所引用的对象。
问：为何不能在索引视图中使用 OUTER JOIN？
答：当将数据插入基表时，行会在逻辑上从基于 OUTER JOIN 的索引视图上消失。这会使执行 OUTER JOIN 视图的增量更新变得相对复杂，而执行性能将比基于标准 (INNER) JOIN 的视图慢一些。
- ››SQL Server 2008 R2 下如何清理数据库日志文件
- ››sqlite 存取中文的解决方法
- ››SQL2005、2008、2000 清空删除日志
- ››SQL Server 2005和SQL Server 2000数据的相互导入...
- ››sql server 2008 在安装了活动目录以后无法启动服...
- ››sqlserver 每30分自动生成一次
- ››sqlite 数据库 对 BOOL型 数据的插入处理正确用法...
- ››sql server自动生成批量执行SQL脚本的批处理
- ››sql server 2008亿万数据性能优化
- ››SQL Server 2008清空数据库日志方法
- ››通过远程管理更改ESXi主机root用户密码
- ››sqlserver安装和简单的使用
中查找“通过 SQL Server 2005 索引视图提高性能”更多相关内容
中查找“通过 SQL Server 2005 索引视图提高性能”更多相关内容更多精彩
赞助商链接
