- 相關(guān)軟件
>CREATE INDEX 創(chuàng)建者：webmaster 更新時(shí)間：2006-02-16 15:51為給定表或視圖創(chuàng)建索引。
只有表或視圖的所有者才能為表創(chuàng)建索引。表或視圖的所有者可以隨時(shí)創(chuàng)建索引，無論表中是否有數(shù)據(jù)?？梢酝ㄟ^指定限定的數(shù)據(jù)庫(kù)名稱，為另一個(gè)數(shù)據(jù)庫(kù)中的表或視圖創(chuàng)建索引。
語法
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )
[ WITH < index_option > [ ,...n] ]
[ ON filegroup ]< index_option > ::=
{ PAD_INDEX |
FILLFACTOR = fillfactor |
IGNORE_DUP_KEY |
DROP_EXISTING |
STATISTICS_NORECOMPUTE |
SORT_IN_TEMPDB
}參數(shù)
UNIQUE
為表或視圖創(chuàng)建唯一索引（不允許存在索引值相同的兩行）。視圖上的聚集索引必須是 UNIQUE 索引。
在創(chuàng)建索引時(shí)，如果數(shù)據(jù)已存在，Microsoft® SQL Server™ 會(huì)檢查是否有重復(fù)值，并在每次使用 INSERT 或 UPDATE 語句添加數(shù)據(jù)時(shí)進(jìn)行這種檢查。如果存在重復(fù)的鍵值，將取消 CREATE INDEX 語句，并返回錯(cuò)誤信息，給出第一個(gè)重復(fù)值。當(dāng)創(chuàng)建 UNIQUE 索引時(shí)，有多個(gè) NULL 值被看作副本。
如果存在唯一索引，那么會(huì)產(chǎn)生重復(fù)鍵值的 UPDATE 或 INSERT 語句將回滾，SQL Server 將顯示錯(cuò)誤信息。即使 UPDATE 或 INSERT 語句更改了許多行但只產(chǎn)生了一個(gè)重復(fù)值，也會(huì)出現(xiàn)這種情況。如果在有唯一索引并且指定了 IGNORE_DUP_KEY 子句情況下輸入數(shù)據(jù)，則只有違反 UNIQUE 索引的行才會(huì)失敗。在處理 UPDATE 語句時(shí)，IGNORE_DUP_KEY 不起作用。
SQL Server 不允許為已經(jīng)包含重復(fù)值的列創(chuàng)建唯一索引，無論是否設(shè)置了 IGNORE_DUP_KEY。如果嘗試這樣做，SQL Server 會(huì)顯示錯(cuò)誤信息；重復(fù)值必須先刪除，才能為這些列創(chuàng)建唯一索引。
CLUSTERED
創(chuàng)建一個(gè)對(duì)象，其中行的物理排序與索引排序相同，并且聚集索引的最低一級(jí)（葉級(jí)）包含實(shí)際的數(shù)據(jù)行。一個(gè)表或視圖只允許同時(shí)有一個(gè)聚集索引。
具有聚集索引的視圖稱為索引視圖。必須先為視圖創(chuàng)建唯一聚集索引，然后才能為該視圖定義其它索引。
在創(chuàng)建任何非聚集索引之前創(chuàng)建聚集索引。創(chuàng)建聚集索引時(shí)重建表上現(xiàn)有的非聚集索引。
如果沒有指定 CLUSTERED，則創(chuàng)建非聚集索引。
說明 因?yàn)榘凑斩x，聚集索引的葉級(jí)與其數(shù)據(jù)頁(yè)相同，所以創(chuàng)建聚集索引時(shí)使用 ON filegroup 子句實(shí)際上會(huì)將表從創(chuàng)建該表時(shí)所用的文件移到新的文件組中。在特定的文件組上創(chuàng)建表或索引之前，應(yīng)確認(rèn)哪些文件組可用并且有足夠的空間供索引使用。文件組的大小必須至少是整個(gè)表所需空間的 1.2 倍，這一點(diǎn)很重要。
NONCLUSTERED
創(chuàng)建一個(gè)指定表的邏輯排序的對(duì)象。對(duì)于非聚集索引，行的物理排序獨(dú)立于索引排序。非聚集索引的葉級(jí)包含索引行。每個(gè)索引行均包含非聚集鍵值和一個(gè)或多個(gè)行定位器（指向包含該值的行）。如果表沒有聚集索引，行定位器就是行的磁盤地址。如果表有聚集索引，行定位器就是該行的聚集索引鍵。
每個(gè)表最多可以有 249 個(gè)非聚集索引（無論這些非聚集索引的創(chuàng)建方式如何：是使用 PRIMARY KEY 和 UNIQUE 約束隱式創(chuàng)建，還是使用 CREATE INDEX 顯式創(chuàng)建）。每個(gè)索引均可以提供對(duì)數(shù)據(jù)的不同排序次序的訪問。
對(duì)于索引視圖，只能為已經(jīng)定義了聚集索引的視圖創(chuàng)建非聚集索引。因此，索引視圖中非聚集索引的行定位器一定是行的聚集鍵。
index_name
是索引名。索引名在表或視圖中必須唯一，但在數(shù)據(jù)庫(kù)中不必唯一。索引名必須遵循標(biāo)識(shí)符規(guī)則。
table
包含要?jiǎng)?chuàng)建索引的列的表?？梢赃x擇指定數(shù)據(jù)庫(kù)和表所有者。
view
要建立索引的視圖的名稱。必須使用 SCHEMABINDING 定義視圖才能在視圖上創(chuàng)建索引。視圖定義也必須具有確定性。如果選擇列表中的所有表達(dá)式、WHERE 和 GROUP BY 子句都具有確定性，則視圖也具有確定性。而且，所有鍵列必須是精確的。只有視圖的非鍵列可能包含浮點(diǎn)表達(dá)式（使用 float 數(shù)據(jù)類型的表達(dá)式），而且 float 表達(dá)式不能在視圖定義的其它任何位置使用。
若要在確定性視圖中查找列，請(qǐng)使用 COLUMNPROPERTY 函數(shù)（IsDeterministic 屬性）。該函數(shù)的 IsPrecise 屬性可用來確定鍵列是否精確。
必須先為視圖創(chuàng)建唯一的聚集索引，才能為該視圖創(chuàng)建非聚集索引。
在 SQL Server 企業(yè)版或開發(fā)版中，查詢優(yōu)化器可使用索引視圖加快查詢的執(zhí)行速度。要使優(yōu)化程序考慮將該視圖作為替換，并不需要在查詢中引用該視圖。
在創(chuàng)建索引視圖或?qū)⑴c索引視圖的表中的行進(jìn)行操作時(shí)，有 7 個(gè) SET 選項(xiàng)必須指派特定的值。SET 選項(xiàng) ARITHABORT、CONCAT_NULL_YIELDS_NULL、QUOTED_IDENTIFIER、ANSI_NULLS、ANSI_PADDING 和 ANSI_WARNING 必須為 ON。SET 選項(xiàng) NUMERIC_ROUNDABORT 必須為 OFF。
如果與上述設(shè)置有所不同，對(duì)索引視圖所引用的任何表執(zhí)行的數(shù)據(jù)修改語句 (INSERT、UPDATE、DELETE) 都將失敗，SQL Server 會(huì)顯示一條錯(cuò)誤信息，列出所有違反設(shè)置要求的 SET 選項(xiàng)。此外，對(duì)于涉及索引視圖的 SELECT 語句，如果任何 SET 選項(xiàng)的值不是所需的值，則 SQL Server 在處理該 SELECT 語句時(shí)不考慮索引視圖替換。在受上述 SET 選項(xiàng)影響的情況中，這將確保查詢結(jié)果的正確性。
如果應(yīng)用程序使用 DB-Library 連接，則必須為服務(wù)器上的所有 7 個(gè) SET 選項(xiàng)指派所需的值。（默認(rèn)情況下，OLE DB 和 ODBC 連接已經(jīng)正確設(shè)置了除 ARITHABORT 外所有需要的 SET 選項(xiàng)。）
如果并非所有上述 SET 選項(xiàng)均有所需的值，則某些操作（例如 BCP、復(fù)制或分布式查詢）可能無法對(duì)參與索引視圖的表執(zhí)行更新。在大多數(shù)情況下，將 ARITHABORT 設(shè)置為 ON（通過服務(wù)器配置選項(xiàng)中的 user options）可以避免這一問題。
強(qiáng)烈建議在服務(wù)器的任一數(shù)據(jù)庫(kù)中創(chuàng)建計(jì)算列上的第一個(gè)索引視圖或索引后，盡早在服務(wù)器范圍內(nèi)將 ARITHABORT 用戶選項(xiàng)設(shè)置為 ON。
有關(guān)索引視圖注意事項(xiàng)和限制的更多信息，請(qǐng)參見注釋部分。
column
應(yīng)用索引的列。指定兩個(gè)或多個(gè)列名，可為指定列的組合值創(chuàng)建組合索引。在 table 后的圓括號(hào)中列出組合索引中要包括的列（按排序優(yōu)先級(jí)排列）。
說明 由 ntext、text 或 image 數(shù)據(jù)類型組成的列不能指定為索引列。另外，視圖不能包括任何 text、ntext 或 image 列，即使在 CREATE INDEX 語句中沒有引用這些列。
當(dāng)兩列或多列作為一個(gè)單位搜索最好，或者許多查詢只引用索引中指定的列時(shí)，應(yīng)使用組合索引。最多可以有 16 個(gè)列組合到一個(gè)組合索引中。組合索引中的所有列必須在同一個(gè)表中。組合索引值允許的最大大小為 900 字節(jié)。也就是說，組成組合索引的固定大小列的總長(zhǎng)度不得超過 900 字節(jié)。有關(guān)組合索引中可變類型列的更多信息，請(qǐng)參見注釋部分。
[ASC | DESC]
確定具體某個(gè)索引列的升序或降序排序方向。默認(rèn)設(shè)置為 ASC。
n
表示可以為特定索引指定多個(gè) columns 的占位符。
PAD_INDEX
指定索引中間級(jí)中每個(gè)頁(yè)（節(jié)點(diǎn)）上保持開放的空間。PAD_INDEX 選項(xiàng)只有在指定了 FILLFACTOR 時(shí)才有用，因?yàn)?PAD_INDEX 使用由 FILLFACTOR 所指定的百分比。默認(rèn)情況下，給定中間級(jí)頁(yè)上的鍵集，SQL Server 將確保每個(gè)索引頁(yè)上的可用空間至少可以容納一個(gè)索引允許的最大行。如果為 FILLFACTOR 指定的百分比不夠大，無法容納一行，SQL Server 將在內(nèi)部使用允許的最小值替代該百分比。
說明 中間級(jí)索引頁(yè)上的行數(shù)永遠(yuǎn)都不會(huì)小于兩行，無論 FILLFACTOR 的值有多小。
FILLFACTOR = fillfactor
指定在 SQL Server 創(chuàng)建索引的過程中，各索引頁(yè)葉級(jí)的填滿程度。如果某個(gè)索引頁(yè)填滿，SQL Server 就必須花時(shí)間拆分該索引頁(yè)，以便為新行騰出空間，這需要很大的開銷。對(duì)于更新頻繁的表，選擇合適的 FILLFACTOR 值將比選擇不合適的 FILLFACTOR 值獲得更好的更新性能。FILLFACTOR 的原始值將在 sysindexes 中與索引一起存儲(chǔ)。
如果指定了 FILLFACTOR，SQL Server 會(huì)向上舍入每頁(yè)要放置的行數(shù)。例如，發(fā)出 CREATE CLUSTERED INDEX ...FILLFACTOR = 33 將創(chuàng)建一個(gè) FILLFACTOR 為 33% 的聚集索引。假設(shè) SQL Server 計(jì)算出每頁(yè)空間的 33% 為 5.2 行。SQL Server 將其向上舍入，這樣，每頁(yè)就放置 6 行。
說明 顯式的 FILLFACTOR 設(shè)置只是在索引首次創(chuàng)建時(shí)應(yīng)用。SQL Server 并不會(huì)動(dòng)態(tài)保持頁(yè)上可用空間的指定百分比。
用戶指定的 FILLFACTOR 值可以從 1 到 100。如果沒有指定值，默認(rèn)值為 0。如果 FILLFACTOR 設(shè)置為 0，則只填滿葉級(jí)頁(yè)?？梢酝ㄟ^執(zhí)行 sp_configure 更改默認(rèn)的 FILLFACTOR 設(shè)置。
只有不會(huì)出現(xiàn) INSERT 或 UPDATE 語句時(shí)（例如對(duì)只讀表），才可以使用 FILLFACTOR 100。如果 FILLFACTOR 為 100，SQL Server 將創(chuàng)建葉級(jí)頁(yè) 100% 填滿的索引。如果在創(chuàng)建 FILLFACTOR 為 100% 的索引之后執(zhí)行 INSERT 或 UPDATE，會(huì)對(duì)每次 INSERT 操作以及有可能每次 UPDATE 操作進(jìn)行頁(yè)拆分。
如果 FILLFACTOR 值較?。? 除外），就會(huì)使 SQL Server 創(chuàng)建葉級(jí)頁(yè)不完全填充的新索引。例如，如果已知某個(gè)表包含的數(shù)據(jù)只是該表最終要包含的數(shù)據(jù)的一小部分，那么為該表創(chuàng)建索引時(shí)，F(xiàn)ILLFACTOR 為 10 會(huì)是合理的選擇。FILLFACTOR 值較小還會(huì)使索引占用較多的存儲(chǔ)空間。
下表說明如何在已指定 FILLFACTOR 的情況下填充索引頁(yè)。
FILLFACTOR 中間級(jí)頁(yè) 葉級(jí)頁(yè) 0 一個(gè)可用項(xiàng) 100% 填滿 1% -99 一個(gè)可用項(xiàng) <= FILLFACTOR% 填滿 100% 一個(gè)可用項(xiàng) 100% 填滿 一個(gè)可用項(xiàng)是指頁(yè)上可以容納另一個(gè)索引項(xiàng)的空間。
重要 用某個(gè) FILLFACTOR 值創(chuàng)建聚集索引會(huì)影響數(shù)據(jù)占用存儲(chǔ)空間的數(shù)量，因?yàn)?SQL Server 在創(chuàng)建聚集索引時(shí)會(huì)重新分布數(shù)據(jù)。
IGNORE_DUP_KEY
控制當(dāng)嘗試向?qū)儆谖ㄒ痪奂饕牧胁迦胫貜?fù)的鍵值時(shí)所發(fā)生的情況。如果為索引指定了 IGNORE_DUP_KEY，并且執(zhí)行了創(chuàng)建重復(fù)鍵的 INSERT 語句，SQL Server 將發(fā)出警告消息并忽略重復(fù)的行。
如果沒有為索引指定 IGNORE_DUP_KEY，SQL Server 會(huì)發(fā)出一條警告消息，并回滾整個(gè) INSERT 語句。
下表顯示何時(shí)可使用 IGNORE_DUP_KEY。
索引類型 選項(xiàng) 聚集 不允許 唯一聚集 允許使用 IGNORE_DUP_KEY 非聚集 不允許 唯一非聚集 允許使用 IGNORE_DUP_KEY DROP_EXISTING
指定應(yīng)除去并重建已命名的先前存在的聚集索引或非聚集索引。指定的索引名必須與現(xiàn)有的索引名相同。因?yàn)榉蔷奂饕奂I，所以在除去聚集索引時(shí)，必須重建非聚集索引。如果重建聚集索引，則必須重建非聚集索引，以便使用新的鍵集。
為已經(jīng)具有非聚集索引的表重建聚集索引時(shí)（使用相同或不同的鍵集），DROP_EXISTING 子句可以提高性能。DROP_EXISTING 子句代替了先對(duì)舊的聚集索引執(zhí)行 DROP INDEX 語句，然后再對(duì)新的聚集索引執(zhí)行 CREATE INDEX 語句的過程。非聚集索引只需重建一次，而且還只是在鍵不同的情況下才需要。
如果鍵沒有改變（提供的索引名和列與原索引相同），則 DROP_EXISTING 子句不會(huì)重新對(duì)數(shù)據(jù)進(jìn)行排序。在必須壓縮索引時(shí)，這樣做會(huì)很有用。
無法使用 DROP_EXISTING 子句將聚集索引轉(zhuǎn)換成非聚集索引；不過，可以將唯一聚集索引更改為非唯一索引，反之亦然。
說明 當(dāng)執(zhí)行帶 DROP_EXISTING 子句的 CREATE INDEX 語句時(shí)，SQL Server 假定索引是一致的（即索引沒有損壞）。指定索引中的行應(yīng)按 CREATE INDEX 語句中引用的指定鍵排序。
STATISTICS_NORECOMPUTE
指定過期的索引統(tǒng)計(jì)不會(huì)自動(dòng)重新計(jì)算。若要恢復(fù)自動(dòng)更新統(tǒng)計(jì)，可執(zhí)行沒有 NORECOMPUTE 子句的 UPDATE STATISTICS。
重要 如果禁用分布統(tǒng)計(jì)的自動(dòng)重新計(jì)算，可能會(huì)妨礙 SQL Server 查詢優(yōu)化器為涉及該表的查詢選取最佳執(zhí)行計(jì)劃。
SORT_IN_TEMPDB
指定用于生成索引的中間排序結(jié)果將存儲(chǔ)在 tempdb 數(shù)據(jù)庫(kù)中。如果 tempdb 與用戶數(shù)據(jù)庫(kù)不在同一磁盤集，則此選項(xiàng)可能減少創(chuàng)建索引所需的時(shí)間，但會(huì)增加創(chuàng)建索引時(shí)使用的磁盤空間。
有關(guān)更多信息，請(qǐng)參見 tempdb 和索引創(chuàng)建。
ON filegroup
在給定的 filegroup 上創(chuàng)建指定的索引。該文件組必須已經(jīng)通過執(zhí)行 CREATE DATABASE 或 ALTER DATABASE 創(chuàng)建。
注釋
為表或索引分配空間時(shí)，每次遞增一個(gè)擴(kuò)展盤區(qū)（8 個(gè) 8 KB 的頁(yè)）。每填滿一個(gè)擴(kuò)展盤區(qū)，就會(huì)再分配一個(gè)。如果表非常小或是空表，其索引將使用單頁(yè)分配，直到向索引添加了 8 頁(yè)后，再轉(zhuǎn)而進(jìn)行擴(kuò)展盤區(qū)分配。若要獲得有關(guān)索引已分配和占用的空間數(shù)量的報(bào)表，請(qǐng)使用 sp_spaceused。
創(chuàng)建聚集索引要求數(shù)據(jù)庫(kù)中的可用空間大約為數(shù)據(jù)大小的 1.2 倍。該空間不包括現(xiàn)有表占用的空間；將對(duì)數(shù)據(jù)進(jìn)行復(fù)制以創(chuàng)建聚集索引，舊的無索引數(shù)據(jù)將在索引創(chuàng)建完成后刪除。使用 DROP_EXISTING 子句時(shí)，聚集索引所需的空間數(shù)量與現(xiàn)有索引的空間要求相同。所需的額外空間可能還受指定的 FILLFACTOR 的影響。
在 SQL Server 2000 中創(chuàng)建索引時(shí)，可以使用 SORT_IN_TEMPDB 選項(xiàng)指示數(shù)據(jù)庫(kù)引擎在 tempdb 中存儲(chǔ)中間索引排序結(jié)果。如果 tempdb 在不同于用戶數(shù)據(jù)庫(kù)所在的磁盤集上，則此選項(xiàng)可能減少創(chuàng)建索引所需的時(shí)間，但會(huì)增加用于創(chuàng)建索引的磁盤空間。除在用戶數(shù)據(jù)庫(kù)中創(chuàng)建索引所需的空間外，tempdb 還必須有大約相同的額外空間來存儲(chǔ)中間排序結(jié)果。有關(guān)更多信息，請(qǐng)參見 tempdb 和索引創(chuàng)建。
CREATE INDEX 語句同其它查詢一樣優(yōu)化。SQL Server 查詢處理器可以選擇掃描另一個(gè)索引，而不是執(zhí)行表掃描，以節(jié)省 I/O 操作。在某些情況下，可以不必排序。
在運(yùn)行 SQL Server 企業(yè)管理器和程序員版的多處理器計(jì)算機(jī)上，CREATE INDEX 自動(dòng)使用多個(gè)處理器執(zhí)行掃描和排序，與其它查詢的操作方式相同。執(zhí)行一條 CREATE INDEX 語句所使用的處理器數(shù)由配置選項(xiàng) max degree of parallelism 和當(dāng)前的工作負(fù)荷決定。如果 SQL Server 檢測(cè)到系統(tǒng)正忙，則在開始執(zhí)行語句之前，CREATE INDEX 操作的并發(fā)程度會(huì)自動(dòng)降低。
自上一次文件組備份以來受 CREATE INDEX 語句影響的全部文件組必須作為一個(gè)單位備份。有關(guān)文件和文件組備份的更多信息，請(qǐng)參見 BACKUP。
備份和 CREATE INDEX 操作不相互防礙。如果正在進(jìn)行備份，則在完整日志記錄模式中創(chuàng)建索引，而這可能需要額外的日志空間。
若要顯示有關(guān)對(duì)象索引的報(bào)表，請(qǐng)執(zhí)行 sp_helpindex。
可以為臨時(shí)表創(chuàng)建索引。在除去表或終止會(huì)話時(shí)，所有索引和觸發(fā)器都將被除去。
索引中的可變類型列
索引鍵允許的最大大小為 900 字節(jié)，不過 SQL Server 2000 允許在可能包含大量可變類型列的列上創(chuàng)建索引，而這些列的最大大小超過 900 字節(jié)。
在創(chuàng)建索引時(shí)，SQL Server 檢查下列條件：
- 所有參與索引定義的固定數(shù)據(jù)列的總長(zhǎng)度必須小于或等于 900 字節(jié)。當(dāng)所要?jiǎng)?chuàng)建的索引只由固定數(shù)據(jù)列構(gòu)成時(shí)，固定數(shù)據(jù)列的總計(jì)大小必須小于或等于 900 字節(jié)。否則將不能創(chuàng)建索引，且 SQL Server 將返回錯(cuò)誤。
- 如果索引定義由固定類型列和可變類型列組成，且固定數(shù)據(jù)列滿足前面的條件（小于或等于 900 字節(jié)），則 SQL Server 仍要檢查可變類型列的總大小。如果可變類型列的最大大小與固定數(shù)據(jù)列大小的和大于 900 字節(jié)，則 SQL Server 將創(chuàng)建索引，不過將給用戶返回警告消息以提醒用戶：如果隨后在可變類型列上的插入或更新操作導(dǎo)致總大小超過 900 字節(jié)，則操作將失敗且用戶將收到運(yùn)行時(shí)錯(cuò)誤。同樣，如果索引定義只由可變類型列組成，且這些列的最大總大小大于 900 字節(jié)，則 SQL Server 將創(chuàng)建索引，不過將返回警告消息。
有關(guān)更多信息，請(qǐng)參見索引鍵的最大值。
在計(jì)算列和視圖上創(chuàng)建索引時(shí)的考慮
在 SQL Server 2000 中，還可以在計(jì)算列和視圖上創(chuàng)建索引。在視圖上創(chuàng)建唯一聚集索引可以提高查詢性能，因?yàn)橐晥D存儲(chǔ)在數(shù)據(jù)庫(kù)中的方式與具有聚集索引的表的存儲(chǔ)方式相同。
UNIQUE 或 PRIMARY KEY 只要滿足所有索引條件，就可以包含計(jì)算列。具體說來就是，計(jì)算列必須具有確定性、必須精確，且不能包含 text、ntext 或 image 列。有關(guān)確定性的更多信息，請(qǐng)參見確定性函數(shù)和非確定性函數(shù)。
在計(jì)算列或視圖上創(chuàng)建索引可能導(dǎo)致前面產(chǎn)生的 INSERT 或 UPDATE 操作失敗。當(dāng)計(jì)算列導(dǎo)致算術(shù)錯(cuò)誤時(shí)可能產(chǎn)生這樣的失敗。例如，雖然下表中的計(jì)算列 c 將導(dǎo)致算術(shù)錯(cuò)誤，但是 INSERT 語句仍有效：
CREATE TABLE t1 (a int, b int, c AS a/b)
GO
INSERT INTO t1 VALUES ('1', '0')
GO相反，如果創(chuàng)建表之后在計(jì)算列 c 上創(chuàng)建索引，則上述 INSERT 語句將失敗。
CREATE TABLE t1 (a int, b int, c AS a/b)
GO
CREATE UNIQUE CLUSTERED INDEX Idx1 ON t1.c
GO
INSERT INTO t1 VALUES ('1', '0')
GO在通過數(shù)字或 float 表達(dá)式定義的視圖上使用索引所得到的查詢結(jié)果，可能不同于不在視圖上使用索引的類似查詢所得到的結(jié)果。這種差異可能是由對(duì)基礎(chǔ)表進(jìn)行 INSERT、DELETE 或 UPDATE 操作時(shí)的舍入錯(cuò)誤引起的。
若要防止 SQL Server 使用索引視圖，請(qǐng)?jiān)诓樵冎邪?OPTION (EXPAND VIEWS) 提示。此外，任何所列選項(xiàng)設(shè)置不正確均會(huì)阻止優(yōu)化程序使用視圖上的索引。有關(guān) OPTION (EXPAND VIEWS) 提示的更多信息，請(qǐng)參見 SELECT。
對(duì)索引視圖的限制
定義索引視圖的 SELECT 語句不得包含 TOP、DISTINCT、COMPUTE、HAVING 和 UNION 關(guān)鍵字。也不能包含子查詢。
SELECT 列表中不得包含星號(hào) (*)、'table.*' 通配符列表、DISTINCT、COUNT(*)、COUNT(<expression>)、基表中的計(jì)算列和標(biāo)量聚合。
非聚合 SELECT 列表中不能包含表達(dá)式。聚合 SELECT 列表（包含 GROUP BY 的查詢）中可能包含 SUM 和 COUNT_BIG(<expression>)；它一定包含 COUNT_BIG(*)。不允許有其它聚合函數(shù)（MIN、MAX、STDEV，...）。
使用 AVG 的復(fù)雜聚合無法參與索引視圖的 SELECT 列表。不過，如果查詢使用這樣的聚合，則優(yōu)化程序?qū)⒛苁褂迷撍饕晥D，用 SUM 和 COUNT_BIG 的簡(jiǎn)單聚合組合代替 AVG。
若某列是從取值為 float 數(shù)據(jù)類型或使用 float 表達(dá)式進(jìn)行取值的表達(dá)式得到的，則不能作為索引視圖或表中計(jì)算列的索引鍵。這樣的列被視為是不精確的。使用 COLUMNPROPERTY 函數(shù)決定特定計(jì)算列或視圖中的列是否精確。
索引視圖受限于以下的附加限制：
- 索引的創(chuàng)建者必須擁有表。所有表、視圖和索引必須在同一數(shù)據(jù)庫(kù)中創(chuàng)建。
- 定義索引視圖的 SELECT 語句不得包含視圖、行集函數(shù)、行內(nèi)函數(shù)或派生表。同一物理表在該語句中只能出現(xiàn)一次。
- 在任何聯(lián)接表中，均不允許進(jìn)行 OUTER JOIN 操作。
- 搜索條件中不允許使用子查詢或者 CONTAINS 或 FREETEXT 謂詞。
- 如果視圖定義包含 GROUP BY 子句，則視圖的 SELECT 列表中必須包含所有分組依據(jù)列及 COUNT_BIG(*) 表達(dá)式。此外，CREATE UNIQUE CLUSTERED INDEX 子句中必須只包含這些列。
可以創(chuàng)建索引的視圖的定義主體必須具有確定性且必須精確，這類似于計(jì)算列上的索引要求。請(qǐng)參見在計(jì)算列上創(chuàng)建索引。
權(quán)限
CREATE INDEX 的權(quán)限默認(rèn)授予 sysadmin 固定服務(wù)器角色、db_ddladmin 和 db_owner 固定數(shù)據(jù)庫(kù)角色和表所有者且不能轉(zhuǎn)讓。
示例
A. 使用簡(jiǎn)單索引
下面的示例為 authors 表的 au_id 列創(chuàng)建索引。
SET NOCOUNT OFF
USE pubs
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'au_id_ind')
DROP INDEX authors.au_id_ind
GO
USE pubs
CREATE INDEX au_id_ind
ON authors (au_id)
GOB. 使用唯一聚集索引
下面的示例為 emp_pay 表的 employeeID 列創(chuàng)建索引，并且強(qiáng)制唯一性。因?yàn)橹付?CLUSTERED 子句，所以該索引將對(duì)磁盤上的數(shù)據(jù)進(jìn)行物理排序。
SET NOCOUNT ON
USE pubs
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'emp_pay')
DROP TABLE emp_pay
GO
USE pubs
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'employeeID_ind')
DROP INDEX emp_pay.employeeID_ind
GO
USE pubs
GO
CREATE TABLE emp_pay
(
employeeID int NOT NULL,
base_pay money NOT NULL,
commission decimal(2, 2) NOT NULL
)
INSERT emp_pay
VALUES (1, 500, .10)
INSERT emp_pay
VALUES (2, 1000, .05)
INSERT emp_pay
VALUES (3, 800, .07)
INSERT emp_pay
VALUES (5, 1500, .03)
INSERT emp_pay
VALUES (9, 750, .06)
GO
SET NOCOUNT OFF
CREATE UNIQUE CLUSTERED INDEX employeeID_ind
ON emp_pay (employeeID)
GOC. 使用簡(jiǎn)單組合索引
下面的示例為 order_emp 表的 orderID 列和 employeeID 列創(chuàng)建索引。
SET NOCOUNT ON
USE pubs
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'order_emp')
DROP TABLE order_emp
GO
USE pubs
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'emp_order_ind')
DROP INDEX order_emp.emp_order_ind
GO
USE pubs
GO
CREATE TABLE order_emp
(
orderID int IDENTITY(1000, 1),
employeeID int NOT NULL,
orderdate datetime NOT NULL DEFAULT GETDATE(),
orderamount money NOT NULL
)
INSERT order_emp (employeeID, orderdate, orderamount)
VALUES (5, '4/12/98', 315.19)
INSERT order_emp (employeeID, orderdate, orderamount)
VALUES (5, '5/30/98', 1929.04)
INSERT order_emp (employeeID, orderdate, orderamount)
VALUES (1, '1/03/98', 2039.82)
INSERT order_emp (employeeID, orderdate, orderamount)
VALUES (1, '1/22/98', 445.29)
INSERT order_emp (employeeID, orderdate, orderamount)
VALUES (4, '4/05/98', 689.39)
INSERT order_emp (employeeID, orderdate, orderamount)
VALUES (7, '3/21/98', 1598.23)
INSERT order_emp (employeeID, orderdate, orderamount)
VALUES (7, '3/21/98', 445.77)
INSERT order_emp (employeeID, orderdate, orderamount)
VALUES (7, '3/22/98', 2178.98)
GO
SET NOCOUNT OFF
CREATE INDEX emp_order_ind
ON order_emp (orderID, employeeID)D. 使用 FILLFACTOR 選項(xiàng)
下面的示例使用 FILLFACTOR 子句，將其設(shè)置為 100。FILLFACTOR 為 100 將完全填滿每一頁(yè)，只有確定表中的索引值永遠(yuǎn)不會(huì)更改時(shí)，該選項(xiàng)才有用。
SET NOCOUNT OFF
USE pubs
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'zip_ind')
DROP INDEX authors.zip_ind
GO
USE pubs
GO
CREATE NONCLUSTERED INDEX zip_ind
ON authors (zip)
WITH FILLFACTOR = 100E. 使用 IGNORE_DUP_KEY
下面的示例為 emp_pay 表創(chuàng)建唯一聚集索引。如果輸入了重復(fù)的鍵，將忽略該 INSERT 或 UPDATE 語句。
SET NOCOUNT ON
USE pubs
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'emp_pay')
DROP TABLE emp_pay
GO
USE pubs
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'employeeID_ind')
DROP INDEX emp_pay.employeeID_ind
GO
USE pubs
GO
CREATE TABLE emp_pay
(
employeeID int NOT NULL,
base_pay money NOT NULL,
commission decimal(2, 2) NOT NULL
)
INSERT emp_pay
VALUES (1, 500, .10)
INSERT emp_pay
VALUES (2, 1000, .05)
INSERT emp_pay
VALUES (3, 800, .07)
INSERT emp_pay
VALUES (5, 1500, .03)
INSERT emp_pay
VALUES (9, 750, .06)
GO
SET NOCOUNT OFF
GO
CREATE UNIQUE CLUSTERED INDEX employeeID_ind
ON emp_pay(employeeID)
WITH IGNORE_DUP_KEYF. 使用 PAD_INDEX 創(chuàng)建索引
下面的示例為 authors 表中的作者標(biāo)識(shí)號(hào)創(chuàng)建索引。沒有 PAD_INDEX 子句，SQL Server 將創(chuàng)建填充 10% 的葉級(jí)頁(yè)，但是葉級(jí)之上的頁(yè)幾乎被完全填滿。使用 PAD_INDEX 時(shí)，中間級(jí)頁(yè)也填滿 10%。
說明 如果沒有指定 PAD_INDEX，唯一聚集索引的索引頁(yè)上至少會(huì)出現(xiàn)兩項(xiàng)。
SET NOCOUNT OFF
USE pubs
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'au_id_ind')
DROP INDEX authors.au_id_ind
GO
USE pubs
CREATE INDEX au_id_ind
ON authors (au_id)
WITH PAD_INDEX, FILLFACTOR = 10G. 為視圖創(chuàng)建索引
下面的示例將創(chuàng)建一個(gè)視圖，并為該視圖創(chuàng)建索引。然后，引入兩個(gè)使用該索引視圖的查詢。
USE Northwind
相關(guān)文章
GO
--Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS ON
GO
--Create view.
CREATE VIEW V1
WITH SCHEMABINDING
AS
SELECT SUM(UnitPrice*Quantity*(1.00-Discount)) AS Revenue, OrderDate, ProductID, COUNT_BIG(*) AS COUNT
FROM dbo.[Order Details] od, dbo.Orders o
WHERE od.OrderID=o.OrderID
GROUP BY OrderDate, ProductID
GO
--Create index on the view.
CREATE UNIQUE CLUSTERED INDEX IV1 ON V1 (OrderDate, ProductID)
GO
--This query will use the above indexed view.
SELECT SUM(UnitPrice*Quantity*(1.00-Discount)) AS Rev, OrderDate, ProductID
FROM dbo.[Order Details] od, dbo.Orders o
WHERE od.OrderID=o.OrderID AND ProductID in (2, 4, 25, 13, 7, 89, 22, 34)
AND OrderDate >= '05/01/1998'
GROUP BY OrderDate, ProductID
ORDER BY Rev DESC
--This query will use the above indexed view.
SELECT OrderDate, SUM(UnitPrice*Quantity*(1.00-Discount)) AS Rev
FROM dbo.[Order Details] od, dbo.Orders o
WHERE od.OrderID=o.OrderID AND DATEPART(mm,OrderDate)= 3
AND DATEPART(yy,OrderDate) = 1998
GROUP BY OrderDate
ORDER BY OrderDate ASC
本頁(yè)查看次數(shù): - 所有參與索引定義的固定數(shù)據(jù)列的總長(zhǎng)度必須小于或等于 900 字節(jié)。當(dāng)所要?jiǎng)?chuàng)建的索引只由固定數(shù)據(jù)列構(gòu)成時(shí)，固定數(shù)據(jù)列的總計(jì)大小必須小于或等于 900 字節(jié)。否則將不能創(chuàng)建索引，且 SQL Server 將返回錯(cuò)誤。