SQL Server：建立索引檢視表

視圖也稱為虛擬表，這是因為由視圖返回的結果集其一般格式與由列和行組成的表相似，並且，在 SQL 陳述式中引用視圖的方式也與參考資料表的方式相同。標準視圖的結果集不是永久地儲存在資料庫中。查詢每次引用視圖時，Microsoft SQL Server 2000 會動態地將產生視圖結果集所需的邏輯合并到從基表資料產生完整查詢結果集所需的邏輯中。產生視圖結果的過程稱為視圖具體化。有關更多資訊，請參見視圖解析。

對於標準視圖而言，為每個引用視圖的查詢動態產生結果集的開銷很大，特別是對於那些涉及對大量行進行複雜處理（如彙總大量資料或聯結許多行）的視圖更為可觀。若經常在查詢中引用這類別檢視，可通過在視圖上建立唯一叢集索引來提高效能。在視圖上建立唯一叢集索引時將執行該視圖，並且結果集在資料庫中的儲存方式與帶叢集索引的表的儲存方式相同。

說明  只有安裝了 Microsoft SQL Server 2000 企業版或 Microsoft SQL Server 2000 開發版，才可以建立索引檢視表。

在視圖上建立索引的另一個好處是：查詢最佳化工具開始在查詢中使用視圖索引，而不是直接在 FROM 子句中命名視圖。這樣一來，可從索引檢視表檢索資料而無需重新編碼，由此帶來的高效率也使常設查詢獲益。有關更多資訊，請參見在視圖上使用索引。

在視圖上建立叢集索引可儲存建立索引時存在的資料。索引檢視表還自動反映自建立索引後對基表資料所做的更改，這一點與在基表上建立的索引相同。當對基表中的資料變更時，索引檢視表中儲存的資料也反映資料更改。視圖的叢集索引必須唯一，從而提高了 SQL Server 在索引中尋找受任何資料更改影響的行的效率。

與基表上的索引相比，對索引檢視表的維護可能更複雜。只有當視圖的結果檢索速度的效益超過了修改所需的開銷時，才應在視圖上建立索引。這樣的視圖通常包括映射到相對靜態資料上、處理多行以及由許多查詢引用的視圖。

視圖的要求

在視圖上建立叢集索引之前，該視圖必須滿足下列要求：

• 當執行 CREATE VIEW 語句時，ANSI_NULLS 和 QUOTED_IDENTIFIER 選項必須設定為 ON。OBJECTPROPERTY 函數通過 ExecIsAnsiNullsOn 或 ExecIsQuotedIdentOn 屬性為視圖報告此資訊。

• 為執行所有 CREATE TABLE 語句以建立視圖引用的表，ANSI_NULLS 選項必須設定為 ON。
• 視圖不能引用任何其它視圖，只能引用基表。
• 視圖引用的所有基表必須與視圖位於同一個資料庫中，並且所有者也與視圖相同。
• 必須使用 SCHEMABINDING 選項建立視圖。SCHEMABINDING 將視圖綁定到基礎基表的架構。
• 必須已使用 SCHEMABINDING 選項建立了視圖中引用的使用者定義的函數。
• 表和使用者定義的函數必須由 2 部分的名稱引用。不允許使用 1 部分、3 部分和 4 部分的名稱。
• 視圖中的運算式所引用的所有函數必須是確定性。OBJECTPROPERTY 函數的 IsDeterministic 屬性報告使用者定義的函數是否是確定性。有關更多資訊，請參見確定性函數和非確定性函數。
• 視圖中的 SELECT 語句不能包含下列 Transact-SQL 文法元素：
  • 挑選清單不能使用 * 或 table_name.* 文法指定列。必須顯式給出列名。

  • 不能在多個視圖列中指定用作簡單運算式的表的列名。如果對列的所有（或只有一個例外）引用是複雜運算式的一部分或是函數的一個參數，則可多次引用該列。例如，下列挑選清單是非法的：

    SELECT ColumnA, ColumnB, ColumnA

    下列挑選清單是合法的：

    SELECT ColumnA, AVG(ColumnA), ColumnA + Column B AS AddColAColBSELECT SUM(ColumnA), ColumnA % ColumnB AS ModuloColAColB

  • 派生表。
  • 行集合函式。
  • UNION 運算子。
  • 子查詢。
  • 外聯結或自我聯結。
  • TOP 子句。
  • ORDER BY 子句。
  • DISTINCT 關鍵字。
  • COUNT(*)（允許 COUNT_BIG(*)。）
    
  • AVG、MAX、MIN、STDEV、STDEVP、VAR 或 VARP 彙總函式。如果在引用索引檢視表的查詢中指定 AVG、MAX、MIN、STDEV、STDEVP、VAR 或 VARP，如果視圖挑選清單包含以下替換函數，則最佳化器會經常計算需要的結果。
    

    複雜彙總函式	替代簡單彙總函式
    AVG(X)	SUM(X), COUNT_BIG(X)
    STDEV(X)	SUM(X), COUNT_BIG(X), SUM(X**2)
    STDEVP(X)	SUM(X), COUNT_BIG(X), SUM(X**2)
    VAR(X)	SUM(X), COUNT_BIG(X), SUM(X**2)
    VARP(X)	SUM(X), COUNT_BIG(X), SUM(X**2)

    例如，索引檢視表挑選清單不能包含運算式 AVG(SomeColumn)。如果視圖挑選清單包含運算式 SUM(SomeColumn) 和 COUNT_BIG(SomeColumn)，則 SQL Server 可為引用視圖並指定 AVG(SomeColumn) 的查詢計算平均數。

  • 引用可為空白的運算式的 SUM 函數。
  • 全文謂詞 CONTAINS 或 FREETEXT。
  • COMPUTE 或 COMPUTE BY 子句。
• 如果沒有指定 GROUP BY，則視圖挑選清單不能包含彙總運算式。
• 如果指定了 GROUP BY，則視圖挑選清單必須包含 COUNT_BIG(*) 運算式，並且，視圖定義不能指定 HAVING、CUBE 或 ROLLUP。
• 通過一個既可以取值為 float 值也可以使用 float 運算式求值的運算式而產生的列不能作為索引檢視表或表的索引的鍵。

CREATE INDEX 語句的要求

在視圖上建立的第一個索引必須是唯一叢集索引。在建立唯一叢集索引後，可建立其它非叢集索引。視圖上的索引命名規則與表上的索引命名規則相同。唯一區別是表名由視圖名替換。有關更多資訊，請參見 CREATE INDEX。

除了一般的 CREATE INDEX 要求外，CREATE INDEX 語句還必須滿足下列要求：

• 執行 CREATE INDEX 語句的使用者必須是視圖的所有者。
• 當執行 CREATE INDEX 語句時，下列 SET 選項必須設定為 ON：
  • ANSI_NULLS
  • ANSI_PADDING
  • ANSI_WARNINGS
  • ARITHABORT
  • CONCAT_NULL_YIELDS_NULL
  • QUOTED_IDENTIFIERS
• 必須將選項 NUMERIC_ROUNDABORT 選項設定為 OFF。
• 視圖不能包含 text、ntext 或 image 列，即使在 CREATE INDEX 語句中沒有引用它們。
• 如果視圖定義中的 SELECT 語句指定了一個 GROUP BY 子句，則唯一叢集索引的鍵只能引用在 GROUP BY 子句中指定的列。

注意事項

建立叢集索引後，對於任何試圖為視圖修改基本資料而進行的串連，其選項設定必須與建立索引所需的選項設定相同。如果這個執行語句的串連沒有適當的選項設定，則 SQL Server 建置錯誤並復原任何會影響視圖結果集的 INSERT、UPDATE 或 DELETE 語句。有關更多資訊，請參見影響結果的 SET 選項。

若除去視圖，視圖上的所有索引也將被除去。若除去叢集索引，視圖上的所有非叢集索引也將被除去。可分別除去非叢集索引。除去視圖上的叢集索引將刪除儲存的結果集，並且最佳化器將重新象處理標準視圖那樣處理視圖。

儘管 CREATE UNIQUE CLUSTERED INDEX 語句僅指定組成叢集索引鍵的列，但視圖的完整結果集將儲存在資料庫中。與基表上的叢集索引一樣，叢集索引的 B 樹結構僅包含鍵列，但資料行包含視圖結果集中的所有列。

若想為現有系統中的視圖添加索引，必須計劃綁定任何想要放入索引的視圖。可以：

• 除去視圖並通過指定 WITH SCHEMABINDING 重新建立它。
• 建立另一個視圖，使其具有與現有視圖相同的文本，但是名稱不同。最佳化器將考慮新視圖上的索引，即使在查詢的 FROM 子句中沒有直接引用它。

說明  不能除去參與到用 SCHEMABINDING 子句建立的視圖中的表或視圖，除非該視圖已被除去或更改而不再具有架構綁定。另外，如果對參與具有架構綁定的視圖的表執行 ALTER TABLE 語句，而這些語句又會影響視圖定義，則這些語句將會失敗。

必須確保新視圖滿足索引檢視表的所有要求。這可能需要更改視圖及其所引用的所有基表的所有權，以便它們都為同一使用者所擁有。

轉自：http://goaler.xicp.net/ShowLog.asp?ID=526