【探讨】索引视图如何提高性能

2019-11-17 03:51:27

字体：大中小

来源：转载

供稿：网友

　最近的一个项目中，由于数据量特别大，导致生成一个报表需要等待10多分钟，所以必须对系统进行优化，跟踪程序后发现数据是从由六个表联接而成的视图中过滤出来的，并且六个表中每个表中的数据都是百万级的，这样联接后效率可想而知了？所以得先对视图进行优化，检查这六个表后发现其中的两个表未建索引，于是马上对其设置索引，再把select的没用字段去除后，再重新执行一下查询语句后，发现效率提高了差不多20%，但是总的来说时间还是比较长，看来还得继续改进。听朋友说索引视图可以大大提高效率，于是从网上查找了一些关于索引视图的资料，并对其进行了简单学习，现总结如下：

一、实例

1、  优化前的视图（执行select * from Query_NoEmployRegist用时127s）

代码
1 SET ANSI_NULLS ON
2 GO
3 SET QUOTED_IDENTIFIER ON
4 GO
5 ALTER VIEW [dbo].[Query_NoEmployRegist]
6 AS
7 SELECT dbo.Person_BasicInfo.*, dbo.Graduater_GraduaterRegist.RegistNO AS RegistNO,
8       dbo.Graduater_GraduaterRegist.RegistTime AS BaoDaoTime,
9       dbo.Graduater_GraduaterRegist.RegistMan AS RegistMan,
10       dbo.Graduater_Business.ComeFrom AS ComeFrom,
11       dbo.Graduater_Business.Code AS Code, dbo.Graduater_Business.Status AS Status,
12       dbo.Graduater_Business.ApPRoveResult AS ApproveResult,
13       dbo.Graduater_Business.NewCorp AS NewCorp,
14       dbo.Graduater_Business.CommendNumber AS CommendNumber,
15       dbo.Graduater_Business.EmployStatus AS EmployStatus,
16       dbo.Graduater_Business.NewCommendTime AS NewCommendTime,
17       dbo.Graduater_Business.GetSource AS GetSource,
18       dbo.Graduater_Business.EmployTime AS EmployTime,
19       dbo.Graduater_Business.Job AS Job, dbo.Graduater_Business.FillMan AS FillMan,
20       dbo.Graduater_Business.FillTime AS FillTime,
21       dbo.Graduater_Business.IsCommendOK AS IsCommendOK,
22       dbo.Graduater_Business.ApproveUser AS ApproveUser,
23       dbo.Graduater_Business.ApproveTime AS ApproveTime,
24       dbo.Graduater_Business.RegistTime AS RegistTime,
25       dbo.Graduater_Business.EmployCorp AS EmployCorp,
26       dbo.Graduater_Business.JobRemark AS JobRemark,
31       dbo.Person_Contact.Address AS Address, dbo.Person_Contact.Zip AS Zip,
32       dbo.Person_Contact.Telephone AS Telephone, dbo.Person_Contact.Mobile AS Mobile,
33       dbo.Person_Contact.Email AS Email, dbo.Person_Contact.IM AS IM,
34       dbo.Person_Skill.ForeignLanguage AS ForeignLanguage,
35       dbo.Person_Skill.ForeignLanguageLevel AS ForeignLanguageLevel,
36       dbo.Person_Skill.CantoneseLevel AS CantoneseLevel,
37       dbo.Person_Skill.MandarinLevel AS MandarinLevel,
38       dbo.Person_Skill.Language AS Language,
39       dbo.Person_Skill.TechnicalTitle AS TechnicalTitle,
40       dbo.Person_Skill.ComputerLevel AS ComputerLevel,
41       dbo.Person_EmployPurpose.JobType AS JobType,
42       dbo.Person_EmployPurpose.Vocation AS Vocation,
43       dbo.Person_EmployPurpose.JobPlace AS JobPlace,
44       dbo.Person_EmployPurpose.Salary AS Salary,
45       dbo.Person_EmployPurpose.OnJobDate AS OnJobDate,
46       dbo.Person_EmployPurpose.CorpType AS CorpType,
49       dbo.Graduater_Business.EmployType AS EmployType,
50       dbo.Graduater_Business.EmployTypeCode AS EmployTypeCode,
51       dbo.Graduater_Business.EmployCorpType AS EmployCorpType
56 FROM dbo.Person_BasicInfo INNER JOIN
57       dbo.Graduater_Business ON
58       dbo.Person_BasicInfo.PersonID = dbo.Graduater_Business.PersonID LEFT OUTER JOIN
59       dbo.Graduater_GraduaterRegist ON
60       dbo.Graduater_Business.GradBusinessID = dbo.Graduater_GraduaterRegist.GraduaterGUID
61        INNER JOIN
62       dbo.Person_Contact ON
63       dbo.Person_BasicInfo.PersonID = dbo.Person_Contact.PersonID INNER JOIN
64       dbo.Person_Skill ON
65       dbo.Person_BasicInfo.PersonID = dbo.Person_Skill.PersonID INNER JOIN
66       dbo.Person_EmployPurpose ON
67       dbo.Person_BasicInfo.PersonID = dbo.Person_EmployPurpose.PersonID
68 GO
69 SET ANSI_NULLS OFF
70 GO
71 SET QUOTED_IDENTIFIER OFF
72 GO
　　2、  优化后的视图（执行select * from Query_NoEmployRegist用时98s）　　

代码
1 SET ANSI_NULLS on
2 GO
3 SET QUOTED_IDENTIFIER on
4 GO
5 ALTER VIEW [dbo].[Query_NoEmployRegist]
6 AS
7 SELECT
8       dbo.Person_BasicInfo.PersonID,
9       dbo.Person_BasicInfo.IdentityID,
10       dbo.Person_BasicInfo.Name,
11       dbo.Person_BasicInfo.Sex,
12       dbo.Person_BasicInfo.Folk,
13       dbo.Person_BasicInfo.Politics,
14       dbo.Person_BasicInfo.Birthday,
15       dbo.Person_BasicInfo.StudentSource,
16       dbo.Person_BasicInfo.StudentSourceCode,
17       dbo.Person_BasicInfo.EduLevel,
18       dbo.Person_BasicInfo.EduLevelCode,
19       dbo.Person_BasicInfo.EduNumber,
20       dbo.Person_BasicInfo.Stature,
21       dbo.Person_BasicInfo.Avoirdupois,
22       dbo.Person_BasicInfo.MarriageStatus,
23       dbo.Person_BasicInfo.College,
24       dbo.Person_BasicInfo.GraduatedDate,
25       dbo.Person_BasicInfo.Train,
26       dbo.Person_BasicInfo.Major,
27       dbo.Person_BasicInfo.Degree,
28       dbo.Person_BasicInfo.DegreeCertificate,
29       dbo.Person_BasicInfo.StudyMode,
30       dbo.Graduater_GraduaterRegist.RegistNO AS RegistNO,
31       dbo.Graduater_GraduaterRegist.RegistTime AS BaoDaoTime,
32       dbo.Graduater_GraduaterRegist.RegistMan AS RegistMan,
33       dbo.Graduater_Business.ComeFrom AS ComeFrom,
34       dbo.Graduater_Business.Code AS Code, dbo.Graduater_Business.Status AS Status,
35       dbo.Graduater_Business.ApproveResult AS ApproveResult,
36       dbo.Graduater_Business.NewCorp AS NewCorp,
37       dbo.Graduater_Business.CommendNumber AS CommendNumber,
38       dbo.Graduater_Business.EmployStatus AS EmployStatus,
39       dbo.Graduater_Business.NewCommendTime AS NewCommendTime,
40       dbo.Graduater_Business.GetSource AS GetSource,
41       dbo.Graduater_Business.EmployTime AS EmployTime,
42       dbo.Graduater_Business.Job AS Job, dbo.Graduater_Business.FillMan AS FillMan,
43       dbo.Graduater_Business.FillTime AS FillTime,
44       dbo.Graduater_Business.IsCommendOK AS IsCommendOK,
45       dbo.Graduater_Business.ApproveUser AS ApproveUser,
46       dbo.Graduater_Business.ApproveTime AS ApproveTime,
47       dbo.Graduater_Business.RegistTime AS RegistTime,
48       dbo.Graduater_Business.EmployCorp AS EmployCorp,
49       dbo.Graduater_Business.JobRemark AS JobRemark,
54       dbo.Person_Contact.Address AS Address, dbo.Person_Contact.Zip AS Zip,
55       dbo.Person_Contact.Telephone AS Telephone, dbo.Person_Contact.Mobile AS Mobile,
56       dbo.Person_Contact.Email AS Email, dbo.Person_Contact.IM AS IM,
57       dbo.Person_Skill.ForeignLanguage AS ForeignLanguage,
58       dbo.Person_Skill.ForeignLanguageLevel AS ForeignLanguageLevel,
59       dbo.Person_Skill.CantoneseLevel AS CantoneseLevel,
60       dbo.Person_Skill.MandarinLevel AS MandarinLevel,
61       dbo.Person_Skill.Language AS Language,
62       dbo.Person_Skill.TechnicalTitle AS TechnicalTitle,
63       dbo.Person_Skill.ComputerLevel AS ComputerLevel,
64       dbo.Person_EmployPurpose.JobType AS JobType,
65       dbo.Person_EmployPurpose.Vocation AS Vocation,
66       dbo.Person_EmployPurpose.JobPlace AS JobPlace,
67       dbo.Person_EmployPurpose.Salary AS Salary,
68       dbo.Person_EmployPurpose.OnJobDate AS OnJobDate,
69       dbo.Person_EmployPurpose.CorpType AS CorpType,
72       dbo.Graduater_Business.EmployType AS EmployType,
73       dbo.Graduater_Business.EmployTypeCode AS EmployTypeCode,
74       dbo.Graduater_Business.EmployCorpType AS EmployCorpType,
79 FROM dbo.Person_BasicInfo INNER JOIN
80       dbo.Graduater_Business ON
81       dbo.Person_BasicInfo.PersonID = dbo.Graduater_Business.PersonID LEFT OUTER JOIN
82       dbo.Graduater_GraduaterRegist ON
83       dbo.Graduater_Business.GradBusinessID = dbo.Graduater_GraduaterRegist.GraduaterGUID
84        INNER JOIN
85       dbo.Person_Contact ON
86       dbo.Person_BasicInfo.PersonID = dbo.Person_Contact.PersonID INNER JOIN
87       dbo.Person_Skill ON
88       dbo.Person_BasicInfo.PersonID = dbo.Person_Skill.PersonID INNER JOIN
89       dbo.Person_EmployPurpose ON
90       dbo.Person_BasicInfo.PersonID = dbo.Person_EmployPurpose.PersonID
91 GO
92 SET ANSI_NULLS OFF
93 GO
94 SET QUOTED_IDENTIFIER OFF
95 GO
　　3、  创建索引的视图（执行select * from Query_NoEmployRegist用时51s）

代码
1 SET ANSI_NULLS on
2 GO
3 SET QUOTED_IDENTIFIER on
4 GO
5
6 ALTER VIEW [dbo].[Query_NoEmployRegist]
7 WITH SCHEMABINDING AS
8 SELECT
9       dbo.Person_BasicInfo.PersonID,
10       dbo.Person_BasicInfo.IdentityID,
11       dbo.Person_BasicInfo.Name,
12       dbo.Person_BasicInfo.Sex,
13       dbo.Person_BasicInfo.Folk,
14       dbo.Person_BasicInfo.Politics,
15       dbo.Person_BasicInfo.Birthday,
16       dbo.Person_BasicInfo.StudentSource,
17       dbo.Person_BasicInfo.StudentSourceCode,
18       dbo.Person_BasicInfo.EduLevel,
19       dbo.Person_BasicInfo.EduLevelCode,
20       dbo.Person_BasicInfo.EduNumber,
21       dbo.Person_BasicInfo.Stature,
22       dbo.Person_BasicInfo.Avoirdupois,
23       dbo.Person_BasicInfo.MarriageStatus,
24       dbo.Person_BasicInfo.College,
25       dbo.Person_BasicInfo.GraduatedDate,
26       dbo.Person_BasicInfo.Train,
27       dbo.Person_BasicInfo.Major,
28       dbo.Person_BasicInfo.Degree,
29       dbo.Person_BasicInfo.DegreeCertificate,
30       dbo.Person_BasicInfo.StudyMode,
31       dbo.Graduater_GraduaterRegist.RegistNO AS RegistNO,
32       dbo.Graduater_GraduaterRegist.RegistTime AS BaoDaoTime,
33       dbo.Graduater_GraduaterRegist.RegistMan AS RegistMan,
34       dbo.Graduater_Business.ComeFrom AS ComeFrom,
35       dbo.Graduater_Business.Code AS Code, dbo.Graduater_Business.Status AS Status,
36       dbo.Graduater_Business.ApproveResult AS ApproveResult,
37       dbo.Graduater_Business.NewCorp AS NewCorp,
38       dbo.Graduater_Business.CommendNumber AS CommendNumber,
39       dbo.Graduater_Business.EmployStatus AS EmployStatus,
40       dbo.Graduater_Business.NewCommendTime AS NewCommendTime,
41       dbo.Graduater_Business.GetSource AS GetSource,
42       dbo.Graduater_Business.EmployTime AS EmployTime,
43       dbo.Graduater_Business.Job AS Job, dbo.Graduater_Business.FillMan AS FillMan,
44       dbo.Graduater_Business.FillTime AS FillTime,
45       dbo.Graduater_Business.IsCommendOK AS IsCommendOK,
46       dbo.Graduater_Business.ApproveUser AS ApproveUser,
47       dbo.Graduater_Business.ApproveTime AS ApproveTime,
48       dbo.Graduater_Business.RegistTime AS RegistTime,
49       dbo.Graduater_Business.EmployCorp AS EmployCorp,
50       dbo.Graduater_Business.JobRemark AS JobRemark,
51       dbo.Person_Contact.Address AS Address, dbo.Person_Contact.Zip AS Zip,
52       dbo.Person_Contact.Telephone AS Telephone, dbo.Person_Contact.Mobile AS Mobile,
53       dbo.Person_Contact.Email AS Email, dbo.Person_Contact.IM AS IM,
54       dbo.Person_Skill.ForeignLanguage AS ForeignLanguage,
55       dbo.Person_Skill.ForeignLanguageLevel AS ForeignLanguageLevel,
56       dbo.Person_Skill.CantoneseLevel AS CantoneseLevel,
57       dbo.Person_Skill.MandarinLevel AS MandarinLevel,
58       dbo.Person_Skill.Language AS Language,
59       dbo.Person_Skill.TechnicalTitle AS TechnicalTitle,
60       dbo.Person_Skill.ComputerLevel AS ComputerLevel,
61       dbo.Person_EmployPurpose.JobType AS JobType,
62       dbo.Person_EmployPurpose.Vocation AS Vocation,
63       dbo.Person_EmployPurpose.JobPlace AS JobPlace,
64       dbo.Person_EmployPurpose.Salary AS Salary,
65       dbo.Person_EmployPurpose.OnJobDate AS OnJobDate,
66       dbo.Person_EmployPurpose.CorpType AS CorpType,
67       dbo.Person_EmployPurpose.Job AS RequireJob,
68       dbo.Graduater_Business.EmployType AS EmployType,
69       dbo.Graduater_Business.EmployTypeCode AS EmployTypeCode,
70       dbo.Graduater_Business.EmployCorpType AS EmployCorpType,
71 FROM dbo.Person_BasicInfo INNER JOIN
72       dbo.Graduater_Business ON
73       dbo.Person_BasicInfo.PersonID = dbo.Graduater_Business.PersonID INNER JOIN
74       dbo.Graduater_GraduaterRegist ON
75       dbo.Graduater_Business.GradBusinessID = dbo.Graduater_GraduaterRegist.GraduaterGUID
76        INNER JOIN
77       dbo.Person_Contact ON
78       dbo.Person_BasicInfo.PersonID = dbo.Person_Contact.PersonID INNER JOIN
79       dbo.Person_Skill ON
80       dbo.Person_BasicInfo.PersonID = dbo.Person_Skill.PersonID INNER JOIN
81       dbo.Person_EmployPurpose ON
82       dbo.Person_BasicInfo.PersonID = dbo.Person_EmployPurpose.PersonID
83 GO
84 CREATE UNIQUE CLUSTERED INDEX Query_NoEmployRegist_Ind
85      ON Query_NoEmployRegist(GraduatedDate, StudentSourceCode,RegistTime,ApproveTime,PrintTime,ComeFrom)
86 SET ANSI_NULLS ON
87 GO
88 SET QUOTED_IDENTIFIER ON
89 GO

看来还得优化，希望各位博友指点一下！

二、索引视图的学习总结

       1、什么是索引视图？

在视图上创建唯一的聚集索引及非聚集索引，来提高最复杂的查询的数据访问性能。具有唯一的聚集索引的视图即为索引视图。从数据库管理系统 (DBMS) 的角度看来，视图是对数据（一种元数据类型）的一种描述。当创建了一个典型视图时，通过封装一个 SELECT 语句（定义一个结果集来表示为虚拟表）来定义元数据。当在另一个查询的 FROM 子句中引用视图时，将从系统目录检索该元数据，并替代该视图的引用扩展元数据。视图扩展之后，SQL Server 查询优化器会为执行查询编译一个执行计划。查询优化器会搜索针对某个查询的一组可能的执行计划，并根据对执行每个查询计划所需的实际时间的估计，选择所能找到的成本最低的计划。

对于非索引视图，解析查询所必需的视图部分会在运行时被具体化。任何计算（比如：联接或聚合）都在每个引用视图的查询执行时完成1。在视图上创建了唯一的聚集索引后，该视图的结果集随即被具体化，并保存在数据库的物理存储中，从而在执行时节省了执行这一高成本操作的开销。

在查询执行中，可通过两种方式使用索引视图。查询可直接引用索引视图，或者更重要的是，如果查询优化器确定该视图可替换成本最低的查询计划中的部分或全部查询，那么就可以选定它。在第二种情况中，使用索引视图替代基础表及其一般索引。不必在查询中引用视图以使查询优化器在查询执行时使用该视图。这使得现有的应用程序可以从新创建的索引视图中受益，而不必进行更改。

索引视图可通过以下方式提高查询性能：

　　（1）可预先计算聚合并将其保存在索引中，从而在查询执行时，最小化高成本的计算。

　　（2）可预先联接各个表并保存最终获得的数据集。

　　（3）可保存联接或聚合的组合。

　　2、应用索引视图的优点

　　在实施索引视图前，分析数据库工作负荷。运用查询及各种相关工具（比如：SQL Profiler）方面的知识来确定可从索引视图获益的查询。频繁发生聚合和联接的情况最适合使用索引视图。无论是否频繁发生，只要某个查询需要很长的响应时间，同时快速获得响应的开销很高，那么就适合使用索引视图。

不是所有的查询都能从索引视图中获益。与一般索引类似，如果未使用索引视图，就无法从中受益。在这种情况下，不仅无法实现性能改善，而且会在磁盘空间、维护和优化方面产生额外的成本。然而，当使用索引视图时，可大大改善（在数量级上）数据访问。这是因为查询优化器使用存储在索引视图（大幅降低了查询执行的成本）中预先计算的结果。

　　从查询类型和模式方面来看，获益的应用程序一般包含：

　　• 大型表的联接和聚合

　　• 查询的重复模式

　　• 几组相同或重叠的列上的重复聚合

　　• 相同键上相同表的重复联接

　　• 以上各项的组合

　　查询优化器如何使用索引视图

　　SQL Server 查询优化器自动决定何时对给定的查询执行使用索引视图。不必在查询中直接引用视图以供优化器在查询执行计划中使用。所以，现有的应用程序可运用索引视图，而不用更改应用程序本身；只是必须创建索引视图。

　　优化器考虑事项

　　查询优化器通过考虑几个条件来决定索引视图能否涵盖整个或部分查询。这些条件对应查询中的一个 FROM 子句并由下列这几个部分组成：

　　• 查询 FROM 子句中的表必须是索引视图 FROM 子句中的表的超集。

　　• 查询中的联接条件必须是视图中的联接条件的超集。

　　• 查询中的聚合列必须可从视图中的聚合列的子集派生。

　　• 查询选择列表中的所有表达式必须可从视图选择列表或未包含在视图定义中的表派生。

　　• 如果与其他谓词所匹配的行的超集相匹配，那么该谓词将归入另一个谓词。例如，“T.a=10”归入“T.a=10 and T.b=20”。任何谓词都可归入其自身。视图中限　　　　　　　　制表值的那部分谓词必须归入查询中限制相同表的那部分谓词。此外，必须以 SQL Server 可验证的方式实现这一点。

　　• 属于视图定义中的表的查询搜索条件谓词的所有列必须出现在下列视图定义的一项或多项中：

　　(1) 一个 GROUP BY 列表。

　　(2) 视图选择列表（如不存在 GROUP BY）。

　　(3) 视图定义中相同或等价的谓词。

　　情况 (1) 和 (2) 允许 SQL Server 对视图的列应用查询谓词，以便进一步限制视图的列。情况 (3) 比较特殊。在这种情况下，不需要对列进行筛选，因此该列不必出现在视图中。如果查询不止包含一个 FROM 子句（子查询、派生表、UNION），优化器可能选择几个索引视图来处理查询，并将它们应用到不同 FROM 子句。

　　3、创建索引视图

　　创建索引视图所需的步骤对于视图的成功执行至关重要。

　　（1）针对将在视图中引用的所有现有表，确认 ANSI_NULLS 的设置正确无误。

　　（2）创建任何新表之前，确认对下表所示的当前会话正确设置了 ANSI_NULLS。

　　（3）创建任何新表之前，确认对下表所示的当前会话正确设置了 ANSI_NULLS 和 QUOTED_IDENTIFIER。

　　（4）确认视图定义具有确定性。

　　（5）使用 WITH SCHEMABINDING 选项创建视图。

　　（6）在视图上创建唯一的聚集索引之前，确认会话的 SET 选项的设置正确无误。

　　（7）在视图上创建唯一的聚集索引。

　　（8）可用 OBJECTPROPERTY 函数检查现有表或视图上 ANSI_NULLS 和 QUOTED_IDENTIFIER 的值。

　　4、索引视图的常见问题

　　（1）为何对可创建索引的视图类型存在限制？

为了确保在逻辑上可对视图进行增量维护，限制创建维护成本较高的视图，并限制 SQL Server 实施的复杂性。较大的视图集不具有确定性并与内容相关；其内容的“更改”独立于 DML 操作。无法对这些内容进行索引。在其定义中调用 GETDATE 或 SUSER_SNAME 的任何视图就属于这类视图。

　　（2）视图上的第一个索引为何必须为 CLUSTERED 和 UNIQUE？

必须为 UNIQUE 以便在维护索引视图期间，轻松地按键值查找视图中的记录，并阻止创建带有重复项目的视图（要求维护特殊的逻辑）。必须为 CLUSTERED，因为只有聚集索引才能在强制唯一性的同时存储行。

　　（3）为何查询优化器不选取我的索引视图用于查询计划？

优化器不选取索引视图主要有三种原因：

　　• 使用 SQL Server Enterprise 或 Developer 版本之外的其他版本。只有 Enterprise 和 Developer 版本才支持自动的查询对索引视图匹配。按名称引用索引视图并包含 NOEXPAND 提示，让查询处理器使用所有其他版本中的索引视图。

　　• 使用索引视图的成本可能超出从基表获取数据的成本，或者查询过于简单，使得针对基表的查询的速度既快又容易查找。当在较小的表上定义索引视图时，经常会发生这种情况。如要强制查询处理器使用索引视图，那么可使用 NOEXPAND 提示。如果最初不通过显式的方式引用视图，这样做就可能要求重新编写查询。您可获得带有 NOEXPAND 的查询的实际成本，并将之与不引用该视图的查询计划的实际成本相比较。如果两者的成本相近，那么您就可以认定用不用索引视图都不重要。

　　• 查询优化器不将查询与索引视图相匹配。重新检查视图和查询的定义，确保两者在结构上可相匹配。CASTS、converts 以及其他在逻辑上不会更改查询结果的表达式可能会阻止匹配。另外，表达式规范化和等价以及 SQL Server 执行的归入测试方面存在一些限制。可能无法显示某些等价表达式是相同的，或者逻辑上被其他表达式归入的表达式被真正归入，因此可能会错失匹配。

　　（4）每周更新一次数据仓库。索引视图使查询速度大大提升，却降低了每周更新的速度？该怎么办呢？

　　　　可以考虑在每周更新前丢弃索引视图，更新完后再重新创建。

　　（5）视图存在重复项目，而想对其进行维护。该怎么办呢？

　　可以考虑创建一个视图，按您所要的视图中的所有列和表达式进行分组，并添加一个 COUNT_BIG(*) 列，然后在组合的列上创建一个唯一的聚集索引。

　　分组过程可确保唯一性。虽然不是完全相同的视图，但可以满足您的需要。

　　（6）在一个视图上定义了另一个视图。SQL Server 不让索引顶级视图。该怎么办呢？

　　可以考虑手动将嵌套视图的定义扩展到顶级视图，然后对其进行索引（索引最低层的视图，或者不索引该视图）。

　　（7）为何一定要对索引视图定义 WITH SCHEMABINDING？

　　　　• 使用 schemaname.objectname 明确识别视图所引用的所有对象，而不管是哪个用户访问该视图，同时

　　　　• 不会以导致视图定义非法或强制 SQL Server 在该视图上重新创建索引的方式，更改视图定义中所引用的对象。

　　（8）为何不能在索引视图中使用 OUTER JOIN？

　　  当将数据插入基表时，行会在逻辑上从基于 OUTER JOIN 的索引视图上消失。这会使执行 OUTER JOIN 视图的增量更新变得相对复杂，而执行性能将比基于标准 (INNER) JOIN 的视图慢一些。

　　更多资料：http://www.microsoft.com/china/technet/prodtechnol/sql/2005/ipsql05iv.mspx

上一篇：CuteEditor6 破解注册文件生成

下一篇：jQuery 1.4中文API文档发布，chm格式