SQL Server：創建索引視圖
2024-08-31 00:47:56
供稿：網友
視圖也稱為虛擬表，這是因為由視圖返回的結果集其一般格式與由列和行組成的表相似，并且，在 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_nullsansi_paddingansi_warningsarithabortconcat_null_yields_nullquoted_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
