SQL建立索引

此文轉自：
http://www.cnblogs.com/abcdwxc/archive/2007/12/11/990274.html
-----------------------

為給定表或視圖建立索引。
只有表或視圖的所有者才能為表建立索引。表或視圖的所有者可以隨時建立索引，無論表中是否有資料。可以通過指定限定的資料庫名稱，為另一個資料庫中的表或視圖建立索引。

文法
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 
}

參數
UNIQUE

為表或視圖建立唯一索引（不允許存在索引值相同的兩行）。視圖上的叢集索引必須是 UNIQUE 索引。

在建立索引時，如果資料已存在，Microsoft? SQL Server? 會檢查是否有重複值，並在每次使用 INSERT 或 UPDATE 語句添加資料時進行這種檢查。如果存在重複的索引值，將取消 CREATE INDEX 語句，並返回錯誤資訊，給出第一個重複值。當建立 UNIQUE 索引時，有多個 NULL 值被看作副本。

如果存在唯一索引，那麼會產生重複索引值的 UPDATE 或 INSERT 語句將復原，SQL Server 將顯示錯誤資訊。即使 UPDATE 或 INSERT 語句更改了許多行但只產生了一個重複值，也會出現這種情況。如果在有唯一索引並且指定了 IGNORE_DUP_KEY 子句情況下輸入資料，則只有違反 UNIQUE 索引的行才會失敗。在處理 UPDATE 語句時，IGNORE_DUP_KEY 不起作用。

SQL Server 不允許為已經包含重複值的列建立唯一索引，無論是否設定了 IGNORE_DUP_KEY。如果嘗試這樣做，SQL Server 會顯示錯誤資訊；重複值必須先刪除，才能為這些列建立唯一索引。

CLUSTERED

建立一個對象，其中行的物理排序與索引排序相同，並且叢集索引的最低一級（葉級）包含實際的資料行。一個表或視圖只允許同時有一個叢集索引。

具有叢集索引的視圖稱為索引檢視表。必須先為視圖建立唯一叢集索引，然後才能為該視圖定義其它索引。

在建立任何非叢集索引之前建立叢集索引。建立叢集索引時重建表上現有的非叢集索引。

如果沒有指定 CLUSTERED，則建立非叢集索引。

 

說明  因為按照定義，叢集索引的葉級與其資料頁相同，所以建立叢集索引時使用 ON filegroup 子句實際上會將表從建立該表時所用的檔案移到新的檔案組中。在特定的檔案組上建立表或索引之前，應確認哪些檔案組可用並且有足夠的空間供索引使用。檔案組的大小必須至少是整個表所需空間的 1.2 倍，這一點很重要。

NONCLUSTERED

建立一個指定表的邏輯排序的對象。對於非叢集索引，行的物理排序獨立於索引排序。非叢集索引的葉級包含索引行。每個索引行均包含非聚集索引值和一個或多個行定位器（指向包含該值的行）。如果表沒有叢集索引，行定位器就是行的磁碟地址。如果表有叢集索引，行定位器就是該行的叢集索引鍵。

每個表最多可以有 249 個非叢集索引（無論這些非叢集索引的建立方式如何：是使用 PRIMARY KEY 和 UNIQUE 約束隱式建立，還是使用 CREATE INDEX 顯式建立）。每個索引均可以提供對資料的不同排序次序的訪問。

對於索引檢視表，只能為已經定義了叢集索引的視圖建立非叢集索引。因此，索引檢視表中非叢集索引的行定位器一定是行的聚集鍵。

index_name

是索引名。索引名在表或視圖中必須唯一，但在資料庫中不必唯一。索引名必須遵循標識符規則。

table

包含要建立索引的列的表。可以選擇指定資料庫和表所有者。

view

要建立索引的視圖的名稱。必須使用 SCHEMABINDING 定義視圖才能在視圖上建立索引。視圖定義也必須具有確定性。如果挑選清單中的所有運算式、WHERE 和 GROUP BY 子句都具有確定性，則視圖也具有確定性。而且，所有鍵列必須是精確的。只有視圖的非鍵列可能包含浮點運算式（使用 float 資料類型的運算式），而且 float 運算式不能在視圖定義的其它任何位置使用。

若要在確定性視圖中尋找列，請使用 COLUMNPROPERTY 函數（IsDeterministic 屬性）。該函數的 IsPrecise 屬性可用來確定鍵列是否精確。

必須先為視圖建立唯一的叢集索引，才能為該視圖建立非叢集索引。

在 SQL Server 企業版或開發版中，查詢最佳化工具可使用索引檢視表加快查詢的執行速度。要使最佳化程式考慮將該視圖作為替換，並不需要在查詢中引用該視圖。

在建立索引檢視表或對參與索引檢視表的表中的行進行操作時，有 7 個 SET 選項必須指派特定的值。SET 選項 ARITHABORT、CONCAT_NULL_YIELDS_NULL、QUOTED_IDENTIFIER、ANSI_NULLS、 ANSI_PADDING 和 ANSI_WARNING 必須為 ON。SET 選項 NUMERIC_ROUNDABORT 必須為 OFF。

如果與上述設定有所不同，對索引檢視表所引用的任何錶執行的資料修改語句 (INSERT、UPDATE、DELETE) 都將失敗，SQL Server 會顯示一條錯誤資訊，列出所有違反設定要求的 SET 選項。此外，對於涉及索引檢視表的 SELECT 語句，如果任何 SET 選項的值不是所需的值，則 SQL Server 在處理該 SELECT 語句時不考慮索引檢視表替換。在受上述 SET 選項影響的情況中，這將確保查詢結果的正確性。

如果應用程式使用 DB-Library 串連，則必須為伺服器上的所有 7 個 SET 選項指派所需的值。（預設情況下，OLE DB 和 ODBC 串連已經正確設定了除 ARITHABORT 外所有需要的 SET 選項。）

如果並非所有上述 SET 選項均有所需的值，則某些操作（例如 BCP、複製或分散式查詢）可能無法對參與索引檢視表的表執行更新。在大多數情況下，將 ARITHABORT 設定為 ON（通過伺服器配置選項中的 user options）可以避免這一問題。

強烈建議在伺服器的任一資料庫中建立計算資料行上的第一個索引檢視表或索引後，儘早在伺服器範圍內將 ARITHABORT 使用者選項設定為 ON。

有關索引檢視表注意事項和限制的更多資訊，請參見注釋部分。

column

應用索引的列。指定兩個或多個列名，可為指定列的組合值建立複合式索引。在 table 後的圓括弧中列出複合式索引中要包括的列（按排序優先順序排列）。

 

說明  由 ntext、text 或 image 資料類型組成的列不能指定為索引列。另外，視圖不能包括任何 text、ntext 或 image 列，即使在 CREATE INDEX 語句中沒有引用這些列。

當兩列或多列作為一個單位搜尋最好，或者許多查詢只引用索引中指定的列時，應使用複合式索引。最多可以有 16 個列組合到一個複合式索引中。複合式索引中的所有列必須在同一個表中。複合式索引值允許的最大大小為 900 位元組。也就是說，組成複合式索引的固定大小列的總長度不得超過 900 位元組。有關複合式索引中可變類型列的更多資訊，請參見注釋部分。

[ASC | DESC]

確定具體某個索引列的升序或降序排序方向。預設設定為 ASC。

n

表示可以為特定索引指定多個 columns 的預留位置。

PAD_INDEX

指定索引中間級中每個頁（節點）上保持開放的空間。PAD_INDEX 選項只有在指定了 FILLFACTOR 時才有用，因為 PAD_INDEX 使用由 FILLFACTOR 所指定的百分比。預設情況下，給定中間級頁上的鍵集，SQL Server 將確保每個索引頁上的可用空間至少可以容納一個索引允許的最大行。如果為 FILLFACTOR 指定的百分比不夠大，無法容納一行，SQL Server 將在內部使用允許的最小值替代該百分比。

 

說明  中間級索引頁上的行數永遠都不會小於兩行，無論 FILLFACTOR 的值有多小。

FILLFACTOR = fillfactor

指定在 SQL Server 建立索引的過程中，各索引頁葉級的填滿程度。如果某個索引頁填滿，SQL Server 就必須花時間拆分該索引頁，以便為新行騰出空間，這需要很大的開銷。對於更新頻繁的表，選擇合適的 FILLFACTOR 值將比選擇不合適的 FILLFACTOR 值獲得更好的更新效能。FILLFACTOR 的原始值將在 sysindexes 中與索引一起儲存。

如果指定了 FILLFACTOR，SQL Server 會向上舍入每頁要放置的行數。例如，發出 CREATE CLUSTERED INDEX ...FILLFACTOR = 33 將建立一個 FILLFACTOR 為 33% 的叢集索引。假設 SQL Server 計算出每頁空間的 33% 為 5.2 行。SQL Server 將其向上舍入，這樣，每頁就放置 6 行。

 

說明  顯式的 FILLFACTOR 設定只是在索引首次建立時應用。SQL Server 並不會動態保持頁上可用空間的指定百分比。

使用者指定的 FILLFACTOR 值可以從 1 到 100。如果沒有指定值，預設值為 0。如果 FILLFACTOR 設定為 0，則只填滿葉級頁。可以通過執行 sp_configure 更改預設的 FILLFACTOR 設定。

只有不會出現 INSERT 或 UPDATE 語句時（例如對唯讀表），才可以使用 FILLFACTOR 100。如果 FILLFACTOR 為 100，SQL Server 將建立葉級頁 100% 填滿的索引。如果在建立 FILLFACTOR 為 100% 的索引之後執行 INSERT 或 UPDATE，會對每次 INSERT 操作以及有可能每次 UPDATE 操作進行頁面分割。

如果 FILLFACTOR 值較小（0 除外），就會使 SQL Server 建立葉級頁不完全填充的新索引。例如，如果已知某個表包含的資料只是該表最終要包含的資料的一小部分，那麼為該表建立索引時，FILLFACTOR 為 10 會是合理的選擇。FILLFACTOR 值較小還會使索引佔用較多的儲存空間。

下表說明如何在已指定 FILLFACTOR 的情況下索引頁預留空間頁。

FILLFACTOR 中間級頁 葉級頁
0 一個可用項 100% 填滿
1% -99 一個可用項 <= FILLFACTOR% 填滿
100% 一個可用項 100% 填滿

一個可用項是指頁上可以容納另一個索引項目的空間。

 

重要  用某個 FILLFACTOR 值建立叢集索引會影響資料佔用儲存空間的數量，因為 SQL Server 在建立叢集索引時會重新分配資料。

IGNORE_DUP_KEY

控制當嘗試向屬於唯一叢集索引的列插入重複的索引值時所發生的情況。如果為索引指定了 IGNORE_DUP_KEY，並且執行了建立重複鍵的 INSERT 語句，SQL Server 將發出警告訊息並忽略重複的行。

如果沒有為索引指定 IGNORE_DUP_KEY，SQL Server 會發出一條警告訊息，並復原整個 INSERT 語句。

下表顯示何時可使用 IGNORE_DUP_KEY。

索引類型 選項
聚集 不允許
唯一聚集 允許使用 IGNORE_DUP_KEY
非聚集 不允許
唯一非聚集 允許使用 IGNORE_DUP_KEY

DROP_EXISTING

指定應除去並重建已命名的先前存在的叢集索引或非叢集索引。指定的索引名必須與現有的索引名相同。因為非叢集索引包含聚集鍵，所以在除去叢集索引時，必須重建非叢集索引。如果重建叢集索引，則必須重建非叢集索引，以便使用新的鍵集。

為已經具有非叢集索引的表重建叢集索引時（使用相同或不同的鍵集）， DROP_EXISTING 子句可以提高效能。DROP_EXISTING 子句代替了先對舊的叢集索引執行 DROP INDEX 語句，然後再對新的叢集索引執行 CREATE INDEX 語句的過程。非叢集索引只需重建一次，而且還只是在鍵不同的情況下才需要。

如果鍵沒有改變（提供的索引名和列與原索引相同），則 DROP_EXISTING 子句不會重新對資料進行排序。在必須壓縮索引時，這樣做會很有用。

無法使用 DROP_EXISTING 子句將叢集索引轉換成非叢集索引；不過，可以將唯一叢集索引更改為非唯一索引，反之亦然。

 

說明  當執行帶 DROP_EXISTING 子句的 CREATE INDEX 語句時，SQL Server 假定索引是一致的（即索引沒有損壞）。指定索引中的行應按 CREATE INDEX 語句中引用的指定鍵排序。

STATISTICS_NORECOMPUTE

指定到期的索引統計不會自動重新計算。若要恢複自動更新統計，可執行沒有 NORECOMPUTE 子句的 UPDATE STATISTICS。

 

重要  如果禁用分布統計的自動重新計算，可能會妨礙 SQL Server 查詢最佳化工具為涉及該表的查詢選取最佳執行計畫。

SORT_IN_TEMPDB

指定用於產生索引的中間排序結果將儲存在 tempdb 資料庫中。如果 tempdb 與使用者資料庫不在同一磁碟集，則此選項可能減少建立索引所需的時間，但會增加建立索引時使用的磁碟空間。

有關更多資訊，請參見 tempdb 和索引建立。

ON filegroup

在給定的 filegroup 上建立指定的索引。該檔案組必須已經通過執行 CREATE DATABASE 或 ALTER DATABASE 建立。

注釋
為表或索引分配空間時，每次遞增一個擴充盤區（8 個 8 KB 的頁）。每填滿一個擴充盤區，就會再分配一個。如果表非常小或是空表，其索引將使用單頁分配，直到向索引添加了 8 頁後，再轉而進行擴充盤區分配。若要獲得有關索引已指派和佔用的空間數量的報表，請使用 sp_spaceused。

建立叢集索引要求資料庫中的可用空間大約為資料大小的 1.2 倍。該空間不包括現有表佔用的空間；將對資料進行複製以建立叢集索引，舊的無索引資料將在索引建立完成後刪除。使用 DROP_EXISTING 子句時，叢集索引所需的空間數量與現有索引的空間要求相同。所需的額外空間可能還受指定的 FILLFACTOR 的影響。

在 SQL Server 2000 中建立索引時，可以使用 SORT_IN_TEMPDB 選項指示資料庫引擎在 tempdb 中儲存中間索引排序結果。如果 tempdb 在不同於使用者資料庫所在的磁碟集上，則此選項可能減少建立索引所需的時間，但會增加用於建立索引的磁碟空間。除在使用者資料庫中建立索引所需的空間外， tempdb 還必須有大約相同的額外空間來儲存中間排序結果。有關更多資訊，請參見 tempdb 和索引建立。

CREATE INDEX 語句同其它查詢一樣最佳化。SQL Server 查詢處理器可以選擇掃描另一個索引，而不是執行表掃描，以節省 I/O 操作。在某些情況下，可以不必排序。

在運行 SQL Server 企業管理器和程式員版的多處理器電腦上，CREATE INDEX 自動使用多個處理器執行掃描和排序，與其它查詢的操作方式相同。執行一條 CREATE INDEX 語句所使用的處理器數由配置選項 max degree of parallelism 和當前的工作負載決定。如果 SQL Server 檢測到系統正忙，則在開始執行語句之前，CREATE INDEX 操作的並發程度會自動降低。

自上一次檔案組備份以來受 CREATE INDEX 語句影響的全部檔案組必須作為一個單位備份。有關檔案和檔案組備份的更多資訊，請參見 BACKUP。

備份和 CREATE INDEX 操作不相互防礙。如果進行中備份，則在完整記錄模式中建立索引，而這可能需要額外的日誌空間。

若要顯示有關對象索引的報表，請執行 sp_helpindex。

可以為暫存資料表建立索引。在除去表或終止會話時，所有索引和觸發器都將被除去。

索引中的可變類型列
索引鍵允許的最大大小為 900 位元組，不過 SQL Server 2000 允許在可能包含大量可變類型列的列上建立索引，而這些列的最大大小超過 900 位元組。

在建立索引時，SQL Server 檢查下列條件：

所有參與索引定義的固定資料列的總長度必須小於或等於 900 位元組。當所要建立的索引只由固定資料列構成時，固定資料列的總計大小必須小於或等於 900 位元組。否則將不能建立索引，且 SQL Server 將返回錯誤。

如果索引定義由固定類型列和可變類型列組成，且固定資料列滿足前面的條件（小於或等於 900 位元組），則 SQL Server 仍要檢查可變類型列的總大小。如果可變類型列的最大大小與固定資料列大小的和大於 900 位元組，則 SQL Server 將建立索引，不過將給使用者返回警告訊息以提醒使用者：如果隨後在可變類型列上的插入或更新操作導致總大小超過 900 位元組，則操作將失敗且使用者將收到執行階段錯誤。同樣，如果索引定義只由可變類型列組成，且這些列的最大總大小大於 900 位元組，則 SQL Server 將建立索引，不過將返回警告訊息。
有關更多資訊，請參見索引鍵的最大值。

在計算資料行和視圖上建立索引時的考慮
在 SQL Server 2000 中，還可以在計算資料行和視圖上建立索引。在視圖上建立唯一叢集索引可以提高查詢效能，因為視圖儲存在資料庫中的方式與具有叢集索引的表的儲存方式相同。

UNIQUE 或 PRIMARY KEY 只要滿足所有索引條件，就可以包含計算資料行。具體說來就是，計算資料行必須具有確定性、必須精確，且不能包含 text、ntext 或 image 列。有關確定性更多資訊，請參見確定性函數和非確定性函數。

在計算資料行或視圖上建立索引可能導致前面產生的 INSERT 或 UPDATE 操作失敗。當計算資料行導致算術錯誤時可能產生這樣的失敗。例如，雖然下表中的計算資料行 c 將導致算術錯誤，但是 INSERT 語句仍有效：

CREATE TABLE t1 (a int, b int, c AS a/b)
GO
INSERT INTO t1 VALUES ('1', '0')
GO

相反，如果建立表之後在計算資料行 c 上建立索引，則上述 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

在通過數字或 float 運算式定義的視圖上使用索引所得到的查詢結果，可能不同於不在視圖上使用索引的類似查詢所得到的結果。這種差異可能是由對基礎資料表進行 INSERT、DELETE 或 UPDATE 操作時的舍入錯誤引起的。

若要防止 SQL Server 使用索引檢視表，請在查詢中包含 OPTION (EXPAND VIEWS) 提示。此外，任何所列選項設定不正確均會阻止最佳化程式使用視圖上的索引。有關 OPTION (EXPAND VIEWS) 提示的更多資訊，請參見 SELECT。

對索引檢視表的限制
定義索引檢視表的 SELECT 語句不得包含 TOP、DISTINCT、COMPUTE、HAVING 和 UNION 關鍵字。也不能包含子查詢。

SELECT 列表中不得包含星號 (*)、'table.*' 萬用字元列表、DISTINCT、COUNT(*)、COUNT(<expression>)、基表中的計算資料行和標量彙總。

非彙總 SELECT 列表中不能包含運算式。彙總 SELECT 列表（包含 GROUP BY 的查詢）中可能包含 SUM 和 COUNT_BIG(<expression>)；它一定包含 COUNT_BIG(*)。不允許有其它彙總函式（MIN、MAX、STDEV，...）。

使用 AVG 的複雜彙總無法參與索引檢視表的 SELECT 列表。不過，如果查詢使用這樣的彙總，則最佳化程式將能使用該索引檢視表，用 SUM 和 COUNT_BIG 的簡單彙總組合代替 AVG。

若某列是從取值為 float 資料類型或使用 float 運算式進行取值的運算式得到的，則不能作為索引檢視表或表中計算資料行的索引鍵。這樣的列被視為是不精確的。使用 COLUMNPROPERTY 函數決定特定計算資料行或視圖中的列是否精確。

索引檢視表受限於以下的附加限制：

索引的建立者必須擁有表。所有表、視圖和索引必須在同一資料庫中建立。

定義索引檢視表的 SELECT 語句不得包含視圖、行集合函式、行內函數或派生表。同一物理表在該語句中只能出現一次。

在任何聯結表中，均不允許進行 OUTER JOIN 操作。

搜尋條件中不允許使用子查詢或者 CONTAINS 或 FREETEXT 謂詞。

如果視圖定義包含 GROUP BY 子句，則視圖的 SELECT 列表中必須包含所有分組依據列及 COUNT_BIG(*) 運算式。此外，CREATE UNIQUE CLUSTERED INDEX 子句中必須只包含這些列。
可以建立索引的視圖的定義主體必須具有確定性且必須精確，這類似於計算資料行上的索引要求。請參見在計算資料行上建立索引。

許可權
CREATE INDEX 的許可權預設授予 sysadmin 固定伺服器角色、db_ddladmin 和 db_owner 固定資料庫角色和表所有者且不能轉讓。

樣本
A. 使用簡單索引
下面的樣本為 authors 表的 au_id 列建立索引。

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)
GO

B. 使用唯一叢集索引
下面的樣本為 emp_pay 表的 employeeID 列建立索引，並且強制唯一性。因為指定了 CLUSTERED 子句，所以該索引將對磁碟上的資料進行物理排序。

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)
GO

C. 使用簡單複合式索引
下面的樣本為 order_emp 表的 orderID 列和 employeeID 列建立索引。

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 選項
下面的樣本使用 FILLFACTOR 子句，將其設定為 100。FILLFACTOR 為 100 將完全填滿每一頁，只有確定表中的索引值永遠不會更改時，該選項才有用。

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 = 100

E. 使用 IGNORE_DUP_KEY
下面的樣本為 emp_pay 表建立唯一叢集索引。如果輸入了重複的鍵，將忽略該 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_KEY

F. 使用 PAD_INDEX 建立索引
下面的樣本為 authors 表中的作者標識號建立索引。沒有 PAD_INDEX 子句，SQL Server 將建立填充 10% 的葉級頁，但是葉級之上的頁幾乎被完全填滿。使用 PAD_INDEX 時，中間級頁也填滿 10%。

 

說明  如果沒有指定 PAD_INDEX，唯一叢集索引的索引頁上至少會出現兩項。

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 = 10

G. 為視圖建立索引
下面的樣本將建立一個視圖，並為該視圖建立索引。然後，引入兩個使用該索引檢視表的查詢。

USE Northwind
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