【探討】索引視圖如何提高性能

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。在視圖上創建了唯一的聚集索引后，該視圖的結果集隨即被具體化，并保存在數據庫的物理存儲中，從而在執行時節省了執行這一高成本操作的開銷。

在查詢執行中，可通過兩種方式使用索引視圖。查詢可直接引用索引視圖，或者更重要的是，如果查詢優化器確定該視圖可替換成本最低的查詢計劃中的部分或全部查詢，那么就可以選定它。在第二種情況中，使用索引視圖替代基礎表及其一般索引。不必在查詢中引用視圖以使查詢優化器在查詢執行時使用該視圖。這使得現有的應用程序可以從新創建的索引視圖中受益，而不必進行更改。

索引視圖可通過以下方式提高查詢性能：

　?。?）可預先計算聚合并將其保存在索引中，從而在查詢執行時，最小化高成本的計算。

　　（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、創建索引視圖

　　創建索引視圖所需的步驟對于視圖的成功執行至關重要。

　?。?）針對將在視圖中引用的所有現有表，確認 ANSI_NULLS 的設置正確無誤。

　　（2）創建任何新表之前，確認對下表所示的當前會話正確設置了 ANSI_NULLS。

　　（3）創建任何新表之前，確認對下表所示的當前會話正確設置了 ANSI_NULLS 和 QUOTED_IDENTIFIER。

　?。?）確認視圖定義具有確定性。

　?。?）使用 WITH SCHEMABINDING 選項創建視圖。

　?。?）在視圖上創建唯一的聚集索引之前，確認會話的 SET 選項的設置正確無誤。

　?。?）在視圖上創建唯一的聚集索引。

　?。?）可用 OBJECTPROPERTY 函數檢查現有表或視圖上 ANSI_NULLS 和 QUOTED_IDENTIFIER 的值。

　　4、索引視圖的常見問題

　?。?）為何對可創建索引的視圖類型存在限制？

為了確保在邏輯上可對視圖進行增量維護，限制創建維護成本較高的視圖，并限制 SQL Server 實施的復雜性。較大的視圖集不具有確定性并與內容相關；其內容的“更改”獨立于 DML 操作。無法對這些內容進行索引。在其定義中調用 GETDATE 或 SUSER_SNAME 的任何視圖就屬于這類視圖。

　?。?）視圖上的第一個索引為何必須為 CLUSTERED 和 UNIQUE？

必須為 UNIQUE 以便在維護索引視圖期間，輕松地按鍵值查找視圖中的記錄，并阻止創建帶有重復項目的視圖（要求維護特殊的邏輯）。必須為 CLUSTERED，因為只有聚集索引才能在強制唯一性的同時存儲行。

　?。?）為何查詢優化器不選取我的索引視圖用于查詢計劃？

優化器不選取索引視圖主要有三種原因：

　　• 使用 SQL Server Enterprise 或 Developer 版本之外的其他版本。只有 Enterprise 和 Developer 版本才支持自動的查詢對索引視圖匹配。按名稱引用索引視圖并包含 NOEXPAND 提示，讓查詢處理器使用所有其他版本中的索引視圖。

　　• 使用索引視圖的成本可能超出從基表獲取數據的成本，或者查詢過于簡單，使得針對基表的查詢的速度既快又容易查找。當在較小的表上定義索引視圖時，經常會發生這種情況。如要強制查詢處理器使用索引視圖，那么可使用 NOEXPAND 提示。如果最初不通過顯式的方式引用視圖，這樣做就可能要求重新編寫查詢。您可獲得帶有 NOEXPAND 的查詢的實際成本，并將之與不引用該視圖的查詢計劃的實際成本相比較。如果兩者的成本相近，那么您就可以認定用不用索引視圖都不重要。

　　• 查詢優化器不將查詢與索引視圖相匹配。重新檢查視圖和查詢的定義，確保兩者在結構上可相匹配。CASTS、converts 以及其他在邏輯上不會更改查詢結果的表達式可能會阻止匹配。另外，表達式規范化和等價以及 SQL Server 執行的歸入測試方面存在一些限制。可能無法顯示某些等價表達式是相同的，或者邏輯上被其他表達式歸入的表達式被真正歸入，因此可能會錯失匹配。

　　（4）每周更新一次數據倉庫。索引視圖使查詢速度大大提升，卻降低了每周更新的速度？該怎么辦呢？

　　　　可以考慮在每周更新前丟棄索引視圖，更新完后再重新創建。

　　（5）視圖存在重復項目，而想對其進行維護。該怎么辦呢？

　　可以考慮創建一個視圖，按您所要的視圖中的所有列和表達式進行分組，并添加一個 COUNT_BIG(*) 列，然后在組合的列上創建一個唯一的聚集索引。

　　分組過程可確保唯一性。雖然不是完全相同的視圖，但可以滿足您的需要。

　?。?）在一個視圖上定義了另一個視圖。SQL Server 不讓索引頂級視圖。該怎么辦呢？

　　可以考慮手動將嵌套視圖的定義擴展到頂級視圖，然后對其進行索引（索引最低層的視圖，或者不索引該視圖）。

　?。?）為何一定要對索引視圖定義 WITH SCHEMABINDING？

　　　　• 使用 schemaname.objectname 明確識別視圖所引用的所有對象，而不管是哪個用戶訪問該視圖，同時

　　　　• 不會以導致視圖定義非法或強制 SQL Server 在該視圖上重新創建索引的方式，更改視圖定義中所引用的對象。

　?。?）為何不能在索引視圖中使用 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格式