CREATE INDEX (Transact-SQL)
在指定的資料表上或指定之資料表的檢視表上建立關聯式索引。可以在資料表中有資料之前建立索引。指定限定的資料庫名稱，就可以在另一個資料庫的資料表或檢視表上建立關聯式索引。
[!附註]
如需有關如何建立 XML 索引的詳細資訊，請參閱＜CREATE XML INDEX (Transact-SQL)＞。如需有關如何建立空間索引的詳細資訊，請參閱＜CREATE SPATIAL INDEX (Transact-SQL)＞。
.gif "主題連結圖示")
語法
Create Relational Index
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON <object> (column [ ASC | DESC ] [ ,...n ] )
[ INCLUDE (column_name [ ,...n ] ) ]
[ WHERE <filter_predicate> ]
[ WITH ( <relational_index_option> [ ,...n ] ) ]
[ ON { partition_scheme_name (column_name)
| filegroup_name
| default
}
]
[ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]
[ ; ]
<object> ::=
{
[ database_name. [ schema_name ] . | schema_name. ]
table_or_view_name
}
<relational_index_option> ::=
{
PAD_INDEX = { ON | OFF }
| FILLFACTOR =fillfactor
| SORT_IN_TEMPDB = { ON | OFF }
| IGNORE_DUP_KEY = { ON | OFF }
| STATISTICS_NORECOMPUTE = { ON | OFF }
| DROP_EXISTING = { ON | OFF }
| ONLINE = { ON | OFF }
| ALLOW_ROW_LOCKS = { ON | OFF }
| ALLOW_PAGE_LOCKS = { ON | OFF }
| MAXDOP =max_degree_of_parallelism
| DATA_COMPRESSION = { NONE | ROW | PAGE}
[ ON PARTITIONS ( { <partition_number_expression> | <range> }
[ , ...n ] ) ]
}
<filter_predicate> ::=
{ <conjunct> [ AND <filter_predicate> ] }
<conjunct> ::=
{ <disjunct> | <comparison> }
<disjunct> ::=
{ column_name IN (constant ,...n ) }
<comparison> ::=
{ column_name <comparison_op> constant }
<comparison_op> ::=
{ IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !< }
<range> ::=
<partition_number_expression> TO <partition_number_expression>
Backward Compatible Relational IndexImportant The backward compatible relational index syntax structure will be removed in a future version of SQL Server. Avoid using this syntax structure in new development work, and plan to modify applications that currently use the feature. Use the syntax structure specified in <relational_index_option> instead.
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON <object> (column_name [ ASC | DESC ] [ ,...n ] )
[ WITH <backward_compatible_index_option> [ ,...n ] ]
[ ON { filegroup_name | "default" } ]
<object> ::=
{
[ database_name. [ owner_name ] . | owner_name. ]
table_or_view_name
}
<backward_compatible_index_option> ::=
{
PAD_INDEX
| FILLFACTOR = fillfactor
| SORT_IN_TEMPDB
| IGNORE_DUP_KEY
| STATISTICS_NORECOMPUTE
| DROP_EXISTING
}
引數
UNIQUE
在資料表或檢視表上建立唯一索引。在唯一索引中，任兩個資料列都不能有相同的索引鍵值。檢視表中的叢集索引必須是唯一的。不論 IGNORE_DUP_KEY 是否設為 ON，Database Engine 都不允許在已包含重複值的資料行上建立唯一索引。如果嘗試執行這項作業，Database Engine 會顯示錯誤訊息。必須先移除重複值，才能在資料行上建立唯一索引。唯一索引中使用的資料行應設為 NOT NULL，因為當建立唯一索引時，多個 Null 值會被視為重複值。
CLUSTERED
建立一個索引，在該索引中，索引鍵值的邏輯順序會決定資料表中對應資料列的實體順序。叢集索引的層級 (底部或分葉) 包含資料表的實際資料列。資料表或檢視表一次只允許一個叢集索引。如需詳細資訊，請參閱＜叢集索引結構＞。含有唯一叢集索引的檢視表稱為索引檢視表。在檢視表上建立唯一叢集索引，可將檢視表實際具體化。必須先在檢視表上建立唯一叢集索引，才能在相同檢視表上定義任何其他索引。如需詳細資訊，請參閱＜設計索引檢視＞。
先建立叢集索引，再建立任何非叢集索引。當建立叢集索引時，會重建資料表上現有的非叢集索引。
如果未指定 CLUSTERED，就會建立非叢集索引。
[!附註]
依定義，叢集索引和資料頁的分葉層級是相同的，因此，建立叢集索引及使用 ON partition_scheme_name 或 ON filegroup_name 子句，實際上就會將資料表從建立資料表的檔案群組移至新的資料分割配置或檔案群組。在特定檔案群組上建立資料表或索引之前，請先確認檔案群組是可用的，而且它們有足夠的空間可供索引使用。如需詳細資訊，請參閱＜決定索引的磁碟空間需求＞。
NONCLUSTERED
建立索引來指定資料表的邏輯排序。如果是非叢集索引，資料列的實體順序和它們的索引順序無關。如需詳細資訊，請參閱＜非叢集索引結構＞。不論索引的建立方式為何，每一份資料表最多只能有 999 個非叢集索引：以隱含的方式使用 PRIMARY KEY 和 UNIQUE 條件約束或以明確的方式使用 CREATE INDEX。
如果是索引檢視表，只能在已定義唯一叢集索引的檢視表上建立非叢集索引。
預設值是 NONCLUSTERED。
index_name
這是索引的名稱。在資料表或檢視表內，索引名稱必須是唯一的，但在資料庫內就不一定要是唯一的。索引名稱必須遵照識別碼的規則。column
這是做為索引根據的資料行。您可以指定兩個或兩個以上的資料行名稱，在指定之資料行的合計值上建立複合索引。在括號內的 table_or_view_name 後面，依排序優先權順序列出要併入複合索引中的資料行。單一複合索引鍵中最多只能結合 16 個資料行。複合索引鍵中的所有資料行都必須在相同的資料表或檢視表中。結合索引值的允許大小上限是 900 個位元組。如需有關複合索引中之變數類型資料行的詳細資訊，請參閱「備註」一節。
不能將屬於大型物件 (LOB) 資料類型 ntext、text、varchar(max)、 nvarchar(max)、varbinary(max)、xml 或 image 的資料行指定為索引的索引鍵資料行。另外，即使 CREATE INDEX 陳述式中未參考 ntext、text 或 image 資料行，檢視表定義也不能包含這些資料行。
如果 CLR 使用者定義型別支援二進位排序，您可以在該型別的資料行上建立索引。只要方法標示為具決定性且不執行資料存取作業，您也可以在定義為使用者定義型別資料行方法引動過程的計算資料行上建立索引。如需有關建立 CLR 使用者定義型別資料行索引的詳細資訊，請參閱＜CLR 使用者定義型別＞。
[ ASC | DESC ]
決定特定索引資料行的遞增或遞減排序方向。預設值是 ASC。INCLUDE **(**column [ ,...n ] )
指定要加入至非叢集索引分葉層級的非索引鍵資料行。非叢集索引可以是唯一或非唯一的。資料行名稱在 INCLUDE 清單中不能重複，且不能同時做為索引鍵資料行和非索引鍵資料行。如果資料表上有定義叢集索引，非叢集索引一定會包含叢集索引資料行。如需詳細資訊，請參閱＜具有內含資料行的索引＞。
允許所有資料類型，但不包括 text、ntext 和 image。如果任一個指定的非索引鍵資料行屬於 varchar(max)、nvarchar(max) 或 varbinary(max) 資料類型，則必須採用離線方式 (ONLINE = OFF) 來建立或重建索引。
具決定性之精確或非精確的計算資料行都可以當做內含資料行。只要計算資料行資料類型可當做內含資料行，衍生自 image、ntext、text、varchar(max)、nvarchar(max)、varbinary(max) 和 xml 資料類型的計算資料行都可以包含在非索引鍵資料行中。如需詳細資訊，請參閱＜在計算資料行上建立索引＞。
如需有關建立 XML 索引的詳細資訊，請參閱＜CREATE XML INDEX (Transact-SQL)＞。
WHERE <filter_predicate>
藉由指定要包含在已篩選之索引中的資料列來建立該索引。已篩選的索引必須是資料表上的非叢集索引。針對已篩選之索引中的資料列建立已篩選的統計資料。篩選述詞會使用簡單比較邏輯，而且無法參考計算資料行、UDT 資料行、空間資料類型資料行或 hierarchyID 資料類型資料行。比較運算子不允許使用 NULL 常值的比較。請改用 IS NULL 和 IS NOT NULL 運算子。只能使用 AND 運算子來結合比較和 IN 清單。
以下是 Production.BillOfMaterials 資料表之篩選述詞的一些範例：
WHERE StartDate > '20000101' AND EndDate <= '20000630'
WHERE ComponentID IN (533, 324, 753)
WHERE StartDate IN ('20000404', '20000905') AND EndDate IS NOT NULL
已篩選的索引不適用於 XML 索引和全文檢索索引。如果是 UNIQUE 索引，只有選取的資料列必須有唯一的索引值。已篩選的索引不允許 IGNORE_DUP_KEY 選項。
ON partition_scheme_name**(column_name)**
指定資料分割配置來定義要做為資料分割索引之資料分割對應目標的檔案群組。執行 CREATE PARTITION SCHEME 或 ALTER PARTITION SCHEME，使資料庫內一定要有資料分割結構描述。column_name 指定資料行，而資料分割索引則會針對該資料行來分割。這個資料行必須符合 partition_scheme_name 所要使用之資料分割函數引數的資料類型、長度和有效位數。column_name 不限定為索引定義中的資料行。可以指定基底資料表中的任何資料行，但在分割 UNIQUE 索引時則必須從做為唯一索引鍵的資料行選擇 column_name。這項限制可讓 Database Engine 只在單一資料分割內驗證索引鍵值的唯一性。[!附註]
當您分割一個非唯一的叢集索引時，如果尚未指定資料分割資料行，依預設，Database Engine 會將它加入至叢集索引鍵清單。當您分割一個非唯一的非叢集索引時，如果尚未指定資料分割資料行，Database Engine 會將它新增為索引的非索引鍵 (內含) 資料行。
如果未指定 partition_scheme_name 或 filegroup，且已分割資料表，則會利用相同的資料分割資料行，將索引放在與基礎資料表相同的資料分割配置中。
[!附註]
您無法在 XML 索引上指定資料分割配置。如果基底資料表已分割，XML 索引會使用與資料表相同的資料分割配置。如需有關建立 XML 索引的詳細資訊，請參閱＜CREATE XML INDEX (Transact-SQL)＞。
如需有關資料分割索引的詳細資訊，請參閱＜資料分割索引的特殊指導方針＞。
ON filegroup_name
在指定的檔案群組上建立指定的索引。如果未指定位置，且資料表或檢視表未分割，則索引會使用與基礎資料表或檢視表相同的檔案群組。此檔案群組必須已存在。ON "default"
在預設的檔案群組上建立指定的索引。在這個內容中，default 這個字不是關鍵字。它是預設檔案群組的識別碼，必須加以分隔，如 ON "default" 或 ON [default]。如果指定了 "default"，目前工作階段的 QUOTED_IDENTIFIER 選項就必須是 ON。這是預設值。如需詳細資訊，請參閱＜SET QUOTED_IDENTIFIER (Transact-SQL)＞。
[ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]
指定在建立叢集索引時，資料表之 FILESTREAM 資料的位置。FILESTREAM_ON 子句允許將 FILESTREAM 資料移到不同的 FILESTREAM 檔案群組或資料分割配置。filestream_filegroup_name 是 FILESTREAM 檔案群組的名稱。此檔案群組必須有一個針對此檔案群組定義的檔案 (其方式是使用 CREATE DATABASE 或 ALTER DATABASE 陳述式)，否則會引發錯誤。
如果分割此資料表，則必須包含 FILESTREAM_ON 子句，而且必須指定 FILESTREAM 檔案群組的資料分割配置，此配置會使用與資料表之資料分割配置相同的資料分割函數和資料分割資料行。否則，就會引發錯誤。
如果此資料表未分割，FILESTREAM 資料行將無法分割。此資料表的 FILESTREAM 資料必須儲存在 FILESTREAM_ON 子句中指定的單一檔案群組內。
如果正在建立叢集索引，而且此資料表不包含 FILESTREAM 資料行，則可以在 CREATE INDEX 陳述式內指定 FILESTREAM_ON NULL。
如需 FILESTREAM 主題的清單，請參閱＜設計和實作 FILESTREAM 儲存體＞。
<object>::=
這是要建立索引的完整或非完整物件。
database_name
這是資料庫的名稱。schema_name
這是資料表或檢視所屬的結構描述名稱。table_or_view_name
這是要建立索引之資料表或檢視表的名稱。必須利用 SCHEMABINDING 定義檢視表，才能在該檢視表上建立索引。必須先在檢視表上建立唯一叢集索引，才能建立任何非叢集索引。如需有關索引檢視表的詳細資訊，請參閱「備註」一節。
<relational_index_option>::=
指定當您建立索引時所需使用的選項。
PAD_INDEX = { ON | OFF }
指定索引填補。預設值是 OFF。ON
fillfactor 指定的可用空間百分比會套用到索引的中繼層級頁面上。OFF 或未指定 fillfactor
中繼層級頁面會幾乎填滿整個容量，但會考量中繼頁面上的索引鍵集，而保留至少可供索引所能擁有之大小上限的一個資料列使用的足夠空間。
只有在指定 FILLFACTOR 時，才能使用 PAD_INDEX 選項，因為 PAD_INDEX 會使用 FILLFACTOR 所指定的百分比。如果 FILLFACTOR 所指定的百分比不夠，無法允許一個資料列，Database Engine 會在內部覆寫該百分比以允許最小值。不論 fillfactor 的值設得多低，中繼索引頁面上的資料列數絕對不能少於兩個。
在與舊版本相容的語法中，WITH PAD_INDEX 相當於 WITH PAD_INDEX = ON。
FILLFACTOR **=**fillfactor
指定百分比來指出在建立或重建索引期間，Database Engine 應該使各索引頁面之分葉層級填滿的程度。fillfactor 必須是 1 至 100 之間的整數值。預設值是 0。如果 fillfactor 是 100 或 0，Database Engine 會利用已填滿容量的分葉頁面來建立索引。[!附註]
填滿因數值 0 和 100 在各方面都是一樣的。
只有在建立或重建索引時才會套用 FILLFACTOR 設定。Database Engine 不會動態保留頁面中空白空間的指定百分比。若要檢視填滿因數設定，請使用 sys.indexes 目錄檢視。
.gif "重要注意事項")
重要事項利用小於 100 的 FILLFACTOR 來建立叢集索引，會影響資料所佔用的儲存空間數量，因為 Database Engine 在建立叢集索引時會轉散發資料。
如需詳細資訊，請參閱＜填滿因數＞。
SORT_IN_TEMPDB = { ON | OFF }
指定是否要將暫時排序結果儲存在 tempdb 中。預設值是 OFF。ON
用來建立索引的中繼排序結果儲存在 tempdb 中。如果 tempdb 位於使用者資料庫以外的另一組磁碟上，這種儲存方式可以減少建立索引所需的時間。不過，這會增加建立索引時所使用的磁碟空間量。OFF
中繼排序結果會儲存在用來儲存索引的相同資料庫中。
除了在建立索引時使用者資料庫中所需的空間以外，tempdb 還需要大約相同數量的其他空間來容納中繼排序結果。如需詳細資訊，請參閱＜建立 tempdb 與索引＞。
在與舊版本相容的語法中，WITH SORT_IN_TEMPDB 相當於 WITH SORT_IN_TEMPDB = ON。
IGNORE_DUP_KEY = { ON | OFF }
指定當插入作業嘗試將重複的索引鍵值插入唯一索引時所產生的錯誤回應。IGNORE_DUP_KEY 選項只適用於在建立或重建索引之後所發生的插入作業。執行 CREATE INDEX、ALTER INDEX 或 UPDATE 時，這個選項沒有任何作用。預設值是 OFF。ON
當重複的索引鍵值插入唯一索引時，就會出現警告訊息。只有違反唯一性條件約束的資料列才會失敗。OFF
當重複的索引鍵值插入唯一索引時，就會出現錯誤訊息。整個 INSERT 作業將會回復。
若為針對檢視表所建立的索引、非唯一索引、XML 索引、空間索引和篩選索引，IGNORE_DUP_KEY 不得設為 ON。
若要檢視 IGNORE_DUP_KEY，請使用 sys.indexes。
在與舊版本相容的語法中，WITH IGNORE_DUP_KEY 相當於 WITH IGNORE_DUP_KEY = ON。
STATISTICS_NORECOMPUTE = { ON | OFF}
指定是否要重新計算散發統計資料。預設值是 OFF。ON
不會自動重新計算過期的統計資料。OFF
啟用自動統計資料更新。
若要還原自動統計資料更新，請將 STATISTICS_NORECOMPUTE 設為 OFF，或執行不含 NORECOMPUTE 子句的 UPDATE STATISTICS。
重要事項停用散發統計資料的自動重新計算，可能會使查詢最佳化工具無法取得與資料表有關之查詢的最佳執行計畫。
在與舊版本相容的語法中，WITH STATISTICS_NORECOMPUTE 相當於 WITH STATISTICS_NORECOMPUTE = ON。
DROP_EXISTING = { ON | OFF }
指定要卸除及重建具名之預先存在的叢集索引或非叢集索引。預設值是 OFF。ON
卸除及重建現有的索引。所指定的索引名稱必須與目前現有的索引相同；不過，索引定義可以修改。例如，您可以指定不同的資料行、排序次序、資料分割配置或索引選項。OFF
如果所指定的索引名稱已存在，畫面上會出現錯誤。
您無法利用 DROP_EXISTING 來變更索引類型。
在與舊版本相容的語法中，WITH DROP_EXISTING 相當於 WITH DROP_EXISTING = ON。
ONLINE = { ON | OFF }
指定在索引作業期間，查詢和資料修改是否能夠使用基礎資料表和相關聯的索引。預設值是 OFF。[!附註]
只有 SQL Server Enterprise、Developer 和 Evaluation 版本上才可使用線上索引作業。
ON
索引作業持續期間不會保留長期資料表鎖定。在索引作業的主要階段期間，來源資料表上只保留意圖共用 (IS) 鎖定。這可使基礎資料表和索引的查詢或更新能夠進行。在作業開始時，共用 (S) 鎖定會在來源物件上保留一段很短的時間。在作業結束時，如果正在建立非叢集索引，則有一段短時間會在來源上取得 S (共用) 鎖定；或者，當以線上方式建立或卸除叢集索引時，以及正在重建叢集索引或非叢集索引時，則會取得 SCH-M (結構描述修改) 鎖定。建立本機暫存資料表的索引時，ONLINE 不能設為 ON。OFF
在索引作業期間，套用資料表鎖定。建立、重建或卸除叢集索引的離線索引作業，或重建或卸除非叢集索引的離線索引作業，會取得資料表的結構描述修改 (Sch-M) 鎖定。這可防止所有使用者在作業持續期間存取基礎資料表。建立非叢集索引的離線索引作業會取得資料表的共用 (S) 鎖定。這可防止對基礎資料表進行更新，但可允許讀取作業，如 SELECT 陳述式。
如需詳細資訊，請參閱＜線上索引作業如何運作＞。如需有關鎖定的詳細資訊，請參閱＜鎖定模式＞。
您可以採用線上方式建立索引，包括全域暫存資料表上的索引，但以下是例外狀況：
XML 索引。
本機暫存資料表上的索引。
檢視表上的初始唯一叢集索引。
停用的叢集索引。
叢集索引 - 如果基礎資料表包含下列 LOB 資料類型：image、ntext、text、varchar(max)、nvarchar(max)、varbinary(max) 和 xml。
以 LOB 資料類型資料行定義的非叢集索引。
[!附註]
如果資料表包含 LOB 資料類型，但在索引定義中，這些資料行都不是當做索引鍵資料行或非索引鍵 (內含) 資料行，則可以線上建立非唯一的非叢集索引。
如需詳細資訊，請參閱＜線上執行索引作業＞。
ALLOW_ROW_LOCKS = { ON | OFF }
指定是否允許資料列鎖定。預設值是 ON。ON
當存取索引時，允許資料列鎖定。Database Engine 會決定使用資料列鎖定的時機。OFF
不使用資料列鎖定。
ALLOW_PAGE_LOCKS = { ON | OFF }
指定是否允許頁面鎖定。預設值是 ON。ON
當存取索引時，允許頁面鎖定。Database Engine 會決定使用頁面鎖定的時機。OFF
不使用頁面鎖定。
MAXDOP = max_degree_of_parallelism
在索引作業期間，覆寫 max degree of parallelism 組態選項。請利用 MAXDOP 來限制執行平行計畫所用的處理器數目。最大值是 64 個處理器。max_degree_of_parallelism 可以是：
1
抑制產生平行計畫。>1
根據目前的系統工作負載，將平行索引作業所使用的處理器數目上限，限制為所指定的數目或更少的數目。0 (預設值)
根據目前的系統工作負載來使用實際數目的處理器或比實際數目更少的處理器。
如需詳細資訊，請參閱＜設定平行索引作業＞。
[!附註]
只有 SQL Server Enterprise、Developer 和 Evaluation 版本才可使用平行索引作業。
DATA_COMPRESSION
針對指定的索引、資料分割編號或資料分割範圍指定資料壓縮選項。選項如下：NONE
不壓縮索引或指定的資料分割。ROW
使用資料列壓縮來壓縮索引或指定的資料分割。PAGE
使用頁面壓縮來壓縮索引或指定的資料分割。
如需有關壓縮的詳細資訊，請參閱＜建立壓縮資料表及索引＞。
ON PARTITIONS ( { <partition_number_expression> | <range> } [ ,...n ] )
指定套用 DATA_COMPRESSION 設定的資料分割。如果未分割此索引，ON PARTITIONS 引數將會產生錯誤。如果未提供 ON PARTITIONS 子句，DATA_COMPRESSION 選項會套用到資料分割索引的所有資料分割。可以使用以下方式來指定 <partition_number_expression>：
提供資料分割的編號，例如：ON PARTITIONS (2)。
為數個個別資料分割提供以逗號分隔的資料分割編號，例如：ON PARTITIONS (1, 5)。
同時提供範圍和個別資料分割，例如：ON PARTITIONS (2, 4, 6 TO 8)。
<range> 可以指定為以 TO 一字分隔的資料分割編號，例如：ON PARTITIONS (6 TO 8)。
若要為不同的資料分割設定不同類型的資料壓縮，請指定 DATA_COMPRESSION 選項一次以上，例如：
REBUILD WITH ( DATA_COMPRESSION = NONE ON PARTITIONS (1), DATA_COMPRESSION = ROW ON PARTITIONS (2, 4, 6 TO 8), DATA_COMPRESSION = PAGE ON PARTITIONS (3, 5) )
備註
CREATE INDEX 陳述式的最佳化方式與其他任何查詢一樣。若要儲存在 I/O 作業上，查詢處理器可以選擇掃描另一個索引，而不是執行資料表掃描。在某些情況下，排序作業可以省略。在執行 SQL Server 2005 Enterprise Edition 或 SQL Server 2008 的多重處理器電腦上，CREATE INDEX 可以利用更多的處理器來執行與建立索引相關聯的掃描和排序作業，執行方式與其他查詢的執行方式相同。如需詳細資訊，請參閱＜設定平行索引作業＞。
如果資料庫復原模式設為大量記錄或簡式，建立索引作業可以利用最小的方式來記錄。如需詳細資訊，請參閱＜選擇索引作業的復原模式＞。
索引可以在暫存資料表上建立。當資料表卸除或工作階段結束時，就會卸除索引。
索引支援擴充屬性。如需詳細資訊，請參閱＜使用資料庫物件的擴充屬性＞。
叢集索引
若要在資料表 (堆積) 上建立叢集索引，或要卸除及重新建立現有的叢集索引，則資料庫中必須有可用的其他工作空間，才能容納資料排序和原始資料表或現有叢集索引資料的暫存複本。如需詳細資訊，請參閱＜決定索引的磁碟空間需求＞。如需有關叢集索引的詳細資訊，請參閱＜建立叢集索引＞。
唯一索引
如果唯一索引存在，每當插入作業新增資料時，Database Engine 都會確認是否有重複的值。可能產生重複索引鍵值的插入作業會回復，且 Database Engine 會顯示錯誤訊息。即使插入作業變更多個資料列但只造成一個重複的值，一樣會發生這種情況。如果嘗試輸入資料，而該資料有唯一索引，且該資料的 IGNORE_DUP_KEY 子句設為 ON，則只有違反 UNIQUE 索引規則的資料列會失敗。如需有關唯一索引的詳細資訊，請參閱＜建立唯一索引＞。
資料分割索引
資料分割索引與資料分割資料表的建立和維護方式類似，不過，跟一般索引一樣，資料分割索引會當做個別資料庫物件來處理。未進行資料分割的資料表上可以有資料分割索引；已進行資料分割的資料表上也可以有非資料分割索引。
如果您要在資料分割資料表上建立索引，且不指定要從中放置索引的檔案群組，則索引的資料分割方式與基礎資料表相同。這是因為依預設，索引和它們的基礎資料表會放在相同的檔案群組上，且索引是供相同資料分割配置中使用相同資料分割資料行的資料分割資料表所使用。
當分割一個非唯一的叢集索引時，如果尚未指定資料分割資料行，依預設，Database Engine 會將它們加入至叢集索引鍵清單。
您可以利用在資料表上建立索引的相同方式，在資料分割資料表上建立索引檢視表。如需有關資料分割索引的詳細資訊，請參閱＜資料分割資料表與索引＞。
索引檢視
在檢視表上建立唯一叢集索引可以提升查詢效能，因為檢視表儲存在資料庫中的方式與含有叢集索引之資料表的儲存方式一樣。查詢最佳化工具可以利用索引檢視表來加快查詢執行的速度。不必在查詢中參考此檢視表，最佳化工具仍會考慮以該檢視表做為替代方式。
以下是建立索引檢視表所需要的步驟，這些步驟對於能否順利完成檢視表的實作是很重要的：
確認檢視表中所要參考之所有現有資料表的 SET 選項是正確的。
先確認工作階段之 SET 選項的設定是正確的，再建立任何新的資料表和檢視表。
確認檢視表定義具決定性。
利用 WITH SCHEMABINDING 選項來建立檢視表。
在檢視表上建立唯一的叢集索引。
需要索引檢視表的 SET 選項
如果在查詢執行時有不同的使用中 SET 選項，則評估相同的運算式會在 Database Engine 中產生不同的結果。例如，將 SET 選項 CONCAT_NULL_YIELDS_NULL 設為 ON 之後，運算式 'abc' + NULL 會傳回 NULL 值。不過，將 CONCAT_NULL_YIEDS_NULL 設為 OFF 之後，相同的運算式則會產生 'abc'。
若要確定檢視表可以正確地維護並傳回一致的結果，索引檢視表需要數個 SET 選項的固定值。發生下列狀況時，必須將下表中的 SET 選項設為必要值資料行內所顯示的值：
建立索引檢視表。
有在任何參與索引檢視表的資料表上執行的任何插入、更新或刪除作業。這包括大量複製、複寫及分散式查詢等作業。
查詢最佳化工具會利用索引檢視表來產生查詢計畫。
SET 選項
必要值
預設伺服器值
預設值
OLE DB 和 ODBC 值
預設值
DB-Library 值
ANSI_NULLS
ON
ON
ON
OFF
ANSI_PADDING
ON
ON
ON
OFF
ANSI_WARNINGS*
ON
ON
ON
OFF
ARITHABORT
ON
ON
OFF
OFF
CONCAT_NULL_YIELDS_NULL
ON
ON
ON
OFF
NUMERIC_ROUNDABORT
OFF
OFF
OFF
OFF
QUOTED_IDENTIFIER
ON
ON
ON
OFF
* 當資料庫的相容性層級設定為 90 或以上時，將 ANSI_WARNINGS 設定為 ON 會將 ARITHABORT 隱含設定為 ON。如果資料庫的相容性層級設定為 80 或更低，ARITHABORT 選項就必須明確地設定為 ON。
如果您要使用 OLE DB 或 ODBC 伺服器連接，唯一必須修改的值是 ARITHABORT 設定。必須在伺服器層級利用 sp_configure 來正確地設定所有 DB-Library 值，或者，必須從應用程式利用 SET 命令來正確地設定所有 DB-Library 值。如需有關 SET 選項的詳細資訊，請參閱＜使用 SQL Server 中的選項＞。
| 重要事項 |
|---|
強烈建議您在伺服器之任何資料庫的計算資料行上建立第一個索引檢視表或索引之後，立即在伺服器範圍將 ARITHABORT 使用者選項設為 ON。 |
具決定性的函數
索引檢視表的定義必須具決定性。如果選取清單及 WHERE 和 GROUP BY 子句中的所有運算式都具決定性，則檢視表也具決定性。每當利用一組特定的輸入值來評估具決定性的運算式時，具決定性的運算式一律傳回相同的結果。只有具決定性的函數可以參與具決定性的運算式。例如，DATEADD 函數具決定性，因為它會針對它的三個參數之任何一組給定的引數值一律傳回相同的結果。GETDATE 不具決定性，因為它一律被相同的引數叫用，但是，每當它被執行時，它所傳回的值都會變更。如需詳細資訊，請參閱＜決定性與非決定性函數＞。
即使運算式具決定性，如果它包含浮點運算式，確切的結果仍取決於處理器架構或微碼的版本而定。為了確保資料完整性，這類運算式只能參與索引檢視表的非索引鍵資料行。未含浮點運算式之具決定性的運算式稱為精確運算式。只有精確之具決定性的運算式可以參與索引鍵資料行和索引檢視表的 WHERE 或 GROUP BY 子句。
您可以利用 COLUMNPROPERTY 函數的 IsDeterministic 屬性來判斷檢視表資料行是否具決定性。您可以利用 COLUMNPROPERTY 函數的 IsPrecise 屬性來判斷含有結構描述繫結之檢視表中的具決定性資料行是否為精確資料行。如果是 TRUE，COLUMNPROPERTY 會傳回 1；如果是 FALSE，則傳回 0；如果輸入無效，則傳回 NULL。這表示這個資料行不具決定性或不是精確資料行。
其他需求
除了 SET 選項和具決定性函數的需求以外，下列需求也必須符合：
執行 CREATE INDEX 的使用者必須是檢視表的擁有者。
如果檢視表定義包含 GROUP BY 子句，唯一叢集索引的索引鍵則只能參考 GROUP BY 子句中指定的資料行。
基底資料表在資料表建立時必須設定正確的 SET 選項，否則含有結構描述繫結的檢視表便無法參考基底資料表。
在檢視表定義中，兩部分名稱 schema**.**tablename 必須參考資料表。
必須利用 WITH SCHEMABINDING 選項來建立使用者定義函數。
使用者定義函數必須由兩部分名稱 schema**.**function 來參考。
必須利用 WITH SCHEMABINDING 選項來建立檢視表。
檢視表必須只參考相同資料庫中的基底資料表，而不是參考其他檢視表。
檢視表定義不得包含下列項目：
COUNT(*)
ROWSET 函數
衍生資料表
自我聯結
DISTINCT
STDEV、VARIANCE、AVG
float*、text、ntext 或 image 資料行
子查詢
全文檢索述詞 (CONTAIN、FREETEXT)
可為 Null 之運算式中的 SUM
CLR 使用者定義彙總函數
TOP
MIN、MAX
UNION
*索引檢視表可以包含 float 資料行；不過，這類資料行不能併入叢集索引鍵中。
如果有 GROUP BY，VIEW 定義必須包含 COUNT_BIG(*)，且不能包含 HAVING。GROUP BY 限制只適用於索引檢視表定義。即使索引檢視表不符合這些 GROUP BY 限制，查詢還是可以在它的執行計畫中使用索引檢視表。
可以在資料分割資料表上建立索引檢視表，且索引檢視表本身也可以分割。如需有關資料分割的詳細資訊，請參閱前面的「資料分割索引」一節。
若要防止 Database Engine 使用索引檢視表，請在查詢上併入 OPTION (EXPAND VIEWS) 提示。另外，如果列出的選項中有任何選項設定不正確，最佳化工具便無法使用檢視表上的索引。如需有關 OPTION (EXPAND VIEWS) 提示的詳細資訊，請參閱＜SELECT (Transact-SQL)＞。
資料庫的相容性層級不能小於 80。含有索引檢視表的資料庫不能改成相容性層級小於 80 的資料庫。
已篩選的索引
已篩選的索引是最佳化的非叢集索引，適用於從資料表選取小型資料列百分比的查詢使用。它會使用篩選述詞，針對資料表中的部分資料建立索引。設計良好的已篩選索引可以提升查詢效能、降低儲存成本，並減少維護成本。
需要已篩選之索引的 SET 選項
每當發生下列任何一個狀況時，都需要 Required Value 資料行中的 SET 選項：
建立已篩選的索引。
INSERT、UPDATE、DELETE 或 MERGE 作業修改已篩選之索引中的資料。
查詢最佳化工具在查詢執行計畫中使用已篩選的索引。
SET 選項
必要值
ANSI_NULLS
ON
ANSI_PADDING
ON
ANSI_WARNINGS*
ON
ARITHABORT
ON
CONCAT_NULL_YIELDS_NULL
ON
NUMERIC_ROUNDABORT
OFF
QUOTED_IDENTIFIER
ON
* 當資料庫的相容性層級設定為 90 或以上時，將 ANSI_WARNINGS 設定為 ON 會將 ARITHABORT 隱含設定為 ON。如果資料庫的相容性層級設定為 80 或更低，ARITHABORT 選項就必須明確地設定為 ON。
當 SET 選項不正確時，可能會發生下列狀況：
不會建立已篩選的索引。
Database Engine 會產生錯誤，並回復可變更索引中資料的 INSERT、UPDATE、DELETE 或 MERGE 陳述式。
查詢最佳化工具不會針對任何 Transact-SQL 陳述式考量執行計畫中的索引。
如需有關已篩選之索引的詳細資訊，請參閱＜篩選索引設計指導方針＞。
空間索引
如需有關空間索引的詳細資訊，請參閱＜CREATE SPATIAL INDEX (Transact-SQL)＞和＜使用空間索引 (Database Engine)＞。
XML 索引
如需有關 XML 索引的詳細資訊，請參閱＜CREATE XML INDEX (Transact-SQL)＞和＜XML 資料類型資料行中的索引＞。
索引鍵大小
索引鍵的大小上限是 900 個位元組。如果資料行中現有的資料在索引建立時並未超過 900 個位元組，則可以在 varchar 資料行上建立超過 900 個位元組的索引；但是，後續在資料行進行插入或更新動作時，如果造成總計大小超過 900 個位元組，則會失敗。如需詳細資訊，請參閱＜索引鍵的大小上限＞。叢集索引的索引鍵所包含的 varchar 資料行不能在 ROW_OVERFLOW_DATA 配置單位中有現有的資料。如果在 varchar 資料行上建立叢集索引，且現有的資料在 IN_ROW_DATA 配置單位中，則後續在可能發送資料非資料列的資料行上進行的插入或更新動作會失敗。如需有關配置單位的詳細資訊，請參閱＜資料表與索引組織＞。
非叢集索引可將非索引鍵資料行併入索引的分葉層級中。在計算索引鍵大小時，Database Engine 不會考量這些資料行。如需詳細資訊，請參閱＜具有內含資料行的索引＞。
計算資料行
索引可以在計算資料行上建立。此外，計算資料行可以有 PERSISTED 屬性。這表示 Database Engine 會將計算值儲存在資料表中，並在更新計算資料行所根據的任何其他資料行時更新這些計算值。當 Database Engine 在資料行上建立某索引，且查詢中參考該索引時，它會使用這些保存值。
若要為計算資料行建立索引，則計算資料行必須具決定性且精確。不過，您可以利用 PERSISTED 屬性，擴充可建立索引的計算資料行類型，使其包括：
根據 Transact-SQL 和 CLR 函數及使用者標示為具決定性的 CLR 使用者定義型別方法所產生的計算資料行。
根據具決定性 (如 Database Engine 所定義) 但不精確的運算式所產生的計算資料行。
保存的計算資料行需要設定前一節「需要索引檢視表的 SET 選項」中所示的下列 SET 選項。
UNIQUE 或 PRIMARY KEY 條件約束只要符合索引作業的所有條件就可以包含計算資料行。更詳細的說法是，計算資料行必須具決定性且精確，或是具決定性且一直保存。如需有關決定性的詳細資訊，請參閱＜決定性與非決定性函數＞。
只要計算資料行資料類型可當做索引鍵資料行或非索引鍵資料行，衍生自 image、ntext、text、varchar(max)、nvarchar(max)、varbinary(max) 和 xml 資料類型的計算資料行都可以當做索引鍵資料行或內含非索引鍵資料行來建立索引。例如，您無法在計算的 xml 資料行上建立主要 XML 索引。如果索引鍵大小超過 900 個位元組，畫面上會顯示警告訊息。
在計算資料行上建立索引，可能會使先前有效的插入或更新作業失敗。當計算資料行導致算術錯誤時，就可能發生這類失敗。例如在下表中，雖然計算資料行 c 導致算術錯誤，但 INSERT 陳述式仍可運作。
CREATE TABLE t1 (a int, b int, c AS a/b);
INSERT INTO t1 VALUES (1, 0);
但是，如果在建立資料表之後，您在計算資料行 c 上建立索引，相同的 INSERT 陳述式在這種情況下則會失敗。
CREATE TABLE t1 (a int, b int, c AS a/b);
CREATE UNIQUE CLUSTERED INDEX Idx1 ON t1(c);
INSERT INTO t1 VALUES (1, 0);
如需詳細資訊，請參閱＜在計算資料行上建立索引＞。
將資料行併入索引中
您可以將非索引鍵資料行 (稱為內含資料行) 加入至非叢集索引的分葉層級，以處理查詢來提升查詢效能。換句話說，查詢中參考的所有資料行都會併入索引中當做索引鍵資料行或非索引鍵資料行。這可讓查詢最佳化工具從索引掃描中尋找所有的必要資訊；但不存取資料表或叢集索引。如需詳細資訊，請參閱＜具有內含資料行的索引＞。
指定索引選項
SQL Server 2005 導入新的索引選項，並修改選項的指定方式。在與舊版本相容的語法中，option_name 相當於 WITH ( <option_name> = ON )。當您設定索引選項時，適用下列規則：
只能利用 WITH (option_name= ON | OFF**)** 來指定新的索引選項。
不能在相同的陳述式中同時利用與舊版本相容的語法和新語法來指定選項。例如，指定 WITH (DROP_EXISTING, ONLINE = ON**)** 會使陳述式失敗。
當您建立 XML 索引時，您必須利用 WITH (option_name= ON | OFF**)** 來指定選項。
DROP_EXISTING 子句
您可以利用 DROP_EXISTING 子句來重建索引、加入或卸除資料行、修改選項、修改資料行排序次序，或變更資料分割配置或檔案群組。
如果索引強制執行 PRIMARY KEY 或 UNIQUE 條件約束，且索引定義完全沒有變更，則會卸除索引並重新建立索引以保留現有的條件約束。不過，如果索引定義變更了，陳述式就會失敗。若要變更 PRIMARY KEY 或 UNIQUE 條件約束的定義，請卸除該條件約束，然後利用新的定義來新增條件約束。
當您在一份也有非叢集索引的資料表上利用一組相同或不同的索引鍵來重新建立叢集索引時，DROP_EXISTING 可以增強效能。DROP_EXISTING 會取代舊叢集索引上之 DROP INDEX 陳述式的執行，然後再針對新叢集索引執行 CREATE INDEX 陳述式。非叢集索引只重建一次，之後，只有在索引定義變更時才會再重建。當索引定義的索引名稱、索引鍵資料行和資料分割資料行、唯一性屬性及排序次序與原始索引相同時，DROP_EXISTING 子句不會重建非叢集索引。
不論非叢集索引是否重建，它們一律會保留在它們的原始檔案群組或資料分割配置中，且會使用原始的資料分割函數。如果將叢集索引重建至不同的檔案群組或資料分割配置中，則不會移動非叢集索引來符合叢集索引的新位置。因此，即使非叢集索引先前與叢集索引對齊，它們也可能不再與叢集索引對齊。如需有關資料分割索引對齊的詳細資訊，請參閱＜資料分割索引的特殊指導方針＞。
除非索引陳述式指定非叢集索引且 ONLINE 選項設為 OFF，否則，如果您以相同的順序使用相同的索引鍵資料行，且相同的索引鍵資料行含有相同的遞增或遞減順序，則 DROP_EXISTING 子句不會再次排序資料。如果叢集索引已停用，則執行 CREATE INDEX WITH DROP_EXISTING 作業時必須將 ONLINE 設為 OFF。如果非叢集索引已停用，且與停用的叢集索引無關，則執行 CREATE INDEX WITH DROP_EXISTING 作業時可以將 ONLINE 設為 OFF，也可以將它設為 ON。
當卸除或重新建立含有 128 個 (含) 以上之範圍的索引時，Database Engine 會延遲實際的頁面取消配置及其相關聯的鎖定，直到認可交易之後。如需詳細資訊，請參閱＜卸除和重建大型物件＞。
ONLINE 選項
以線上方式執行索引作業時，適用下列方針：
當線上索引作業進行中時，不能變更、截斷或卸除基礎資料表。
在索引作業進行期間，需要其他暫存磁碟空間。如需詳細資訊，請參閱＜決定索引的磁碟空間需求＞。
線上作業可在下列索引上執行：資料分割索引，以及包含保存的計算資料行或內含資料行的索引。
如需詳細資訊，請參閱＜線上執行索引作業＞。
資料列和頁面鎖定選項
如果 ALLOW_ROW_LOCKS = ON 且 ALLOW_PAGE_LOCK = ON，當您在存取索引時，允許使用資料列、頁面和資料表層級的鎖定。Database Engine 會選擇適當的鎖定，且可以將鎖定從資料列或頁面鎖定擴大到資料表鎖定。如需詳細資訊，請參閱＜鎖定擴大 (Database Engine)＞。
如果 ALLOW_ROW_LOCKS = OFF 且 ALLOW_PAGE_LOCK = OFF，當您存取索引時，只允許使用資料表層級的鎖定。
如需有關設定索引鎖定資料粒度的詳細資訊，請參閱＜自訂索引的鎖定＞。
檢視索引資訊
若要傳回有關索引的資訊，您可以使用目錄檢視、系統函數和系統預存程序。如需詳細資訊，請參閱＜檢視索引資訊＞。
資料壓縮
＜建立壓縮資料表及索引＞主題中有描述資料壓縮。以下是考量的幾個要點：
壓縮可讓更多的資料列儲存在頁面上，但是不會變更最大資料列大小。
索引的非分葉頁面不會進行頁面壓縮，但是可以進行資料列壓縮。
每一個非叢集索引都有個別的壓縮設定，而且不會繼承基礎資料表的壓縮設定。
在堆積上建立叢集索引時，此叢集索引會繼承堆積的壓縮狀態，除非指定了替代的壓縮狀態。
下列限制適用於資料分割索引：
您無法在資料表具有非對齊索引時變更單一資料分割的壓縮設定。
ALTER INDEX <index> ...REBUILD PARTITION ... 語法會重建此索引的指定資料分割。
ALTER INDEX <index> ...REBUILD WITH ... 語法會重建此索引的所有資料分割。
若要評估變更壓縮狀態如何影響資料表、索引或資料分割，請使用 sp_estimate_data_compression_savings 預存程序。
權限
需要資料表或檢視的 ALTER 權限。使用者必須是系統管理員 (sysadmin) 固定伺服器角色的成員，或是 db_ddladmin 和 db_owner 固定資料庫角色的成員。
範例
A. 建立簡單的非叢集索引
下列範例會在 Purchasing.ProductVendor 資料表的 VendorID 資料行上建立非叢集索引。
USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
WHERE name = N'IX_ProductVendor_VendorID')
DROP INDEX IX_ProductVendor_VendorID ON Purchasing.ProductVendor;
GO
CREATE INDEX IX_ProductVendor_VendorID
ON Purchasing.ProductVendor (VendorID);
GO
B. 建立簡單的非叢集複合索引
下列範例會在 Sales.SalesPerson 資料表的 SalesQuota 和 SalesYTD 資料行上建立非叢集複合索引。
USE AdventureWorks
GO
IF EXISTS (SELECT name FROM sys.indexes
WHERE name = N'IX_SalesPerson_SalesQuota_SalesYTD')
DROP INDEX IX_SalesPerson_SalesQuota_SalesYTD ON Sales.SalesPerson ;
GO
CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD
ON Sales.SalesPerson (SalesQuota, SalesYTD);
GO
C. 建立唯一的非叢集索引
下列範例會在 Production.UnitMeasure 資料表的 Name 資料行上建立唯一非叢集索引。索引會強制將資料上的唯一性插入 Name 資料行中。
USE AdventureWorks;
GO
IF EXISTS (SELECT name from sys.indexes
WHERE name = N'AK_UnitMeasure_Name')
DROP INDEX AK_UnitMeasure_Name ON Production.UnitMeasure;
GO
CREATE UNIQUE INDEX AK_UnitMeasure_Name
ON Production.UnitMeasure(Name);
GO
下列查詢會嘗試插入一個含有其值與現有資料列相同的資料列來測試唯一性條件約束。
--Verify the existing value.
SELECT Name FROM Production.UnitMeasure WHERE Name = N'Ounces';
GO
INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name, ModifiedDate)
VALUES ('OC', 'Ounces', GetDate());
產生的錯誤訊息如下：
Server: Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'UnitMeasure' with unique index 'AK_UnitMeasure_Name'. The statement has been terminated.
D. 使用 IGNORE_DUP_KEY 選項
下列範例分別利用兩種不同的選項設定 (先將選項設為 ON，再將選項設為 OFF) 將多個資料列插入暫存資料表中，示範 IGNORE_DUP_KEY 選項的效果。單一資料列會插入 #Test 資料表中，該資料表則會在第二個多重資料列 INSERT 陳述式執行時刻意造成重複的值。資料表中的資料列計數會傳回所插入的資料列數目。
USE AdventureWorks;
GO
CREATE TABLE #Test (C1 nvarchar(10), C2 nvarchar(50), C3 datetime);
GO
CREATE UNIQUE INDEX AK_Index ON #Test (C2)
WITH (IGNORE_DUP_KEY = ON);
GO
INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE());
INSERT INTO #Test SELECT * FROM Production.UnitMeasure;
GO
SELECT COUNT(*)AS [Number of rows] FROM #Test;
GO
DROP TABLE #Test;
GO
以下是第二個 INSERT 陳述式的結果。
Server: Msg 3604, Level 16, State 1, Line 5 Duplicate key was ignored.
Number of rows
--------------
38
請注意，從 Production.UnitMeasure 資料表插入之未違反唯一性條件約束的資料列已順利插入。發出警告且忽略重複的資料列，但不回復整個交易。
重新執行相同的陳述式，但 IGNORE_DUP_KEY 設為 OFF。
USE AdventureWorks;
GO
CREATE TABLE #Test (C1 nvarchar(10), C2 nvarchar(50), C3 datetime);
GO
CREATE UNIQUE INDEX AK_Index ON #Test (C2)
WITH (IGNORE_DUP_KEY = OFF);
GO
INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE());
INSERT INTO #Test SELECT * FROM Production.UnitMeasure;
GO
SELECT COUNT(*)AS [Number of rows] FROM #Test;
GO
DROP TABLE #Test;
GO
以下是第二個 INSERT 陳述式的結果。
Server: Msg 2601, Level 14, State 1, Line 5
Cannot insert duplicate key row in object '#Test' with unique index
'AK_Index'. The statement has been terminated.
Number of rows
--------------
1
請注意，Production.UnitMeasure 資料表中即使只有一個資料列違反 UNIQUE 索引條件約束，資料表中的所有資料列就無法插入資料表中。
E. 利用 DROP_EXISTING 來卸除和重新建立索引
下列範例會利用 DROP_EXISTING 選項，在 Production.WorkOrder 資料表的 ProductID 資料行上卸除及重新建立現有的索引。也會設定 FILLFACTOR 和 PAD_INDEX 選項。
USE AdventureWorks;
GO
CREATE NONCLUSTERED INDEX IX_WorkOrder_ProductID
ON Production.WorkOrder(ProductID)
WITH (FILLFACTOR = 80,
PAD_INDEX = ON,
DROP_EXISTING = ON);
GO
F. 在檢視表上建立索引
下列範例在該檢視上建立檢視和索引。內含使用索引檢視表的兩項查詢。
USE AdventureWorks;
GO
--Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
--Create view with schemabinding.
IF OBJECT_ID ('Sales.vOrders', 'view') IS NOT NULL
DROP VIEW Sales.vOrders ;
GO
CREATE VIEW Sales.vOrders
WITH SCHEMABINDING
AS
SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Revenue,
OrderDate, ProductID, COUNT_BIG(*) AS COUNT
FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o
WHERE od.SalesOrderID = o.SalesOrderID
GROUP BY OrderDate, ProductID;
GO
--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX IDX_V1
ON Sales.vOrders (OrderDate, ProductID);
GO
--This query can use the indexed view even though the view is
--not specified in the FROM clause.
SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev,
OrderDate, ProductID
FROM Sales.SalesOrderDetail AS od
JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID=o.SalesOrderID
AND ProductID BETWEEN 700 and 800
AND OrderDate >= CONVERT(datetime,'05/01/2002',101)
GROUP BY OrderDate, ProductID
ORDER BY Rev DESC;
GO
--This query can use the above indexed view.
SELECT OrderDate, SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev
FROM Sales.SalesOrderDetail AS od
JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID=o.SalesOrderID
AND DATEPART(mm,OrderDate)= 3
AND DATEPART(yy,OrderDate) = 2002
GROUP BY OrderDate
ORDER BY OrderDate ASC;
GO
G. 利用內含 (非索引鍵) 資料行建立索引
下列範例會利用一個索引鍵資料行 (PostalCode) 和四個非索引鍵資料行 (AddressLine1、AddressLine2、City、StateProvinceID) 來建立非叢集索引。其後有一個由索引處理的查詢。若要顯示查詢最佳化工具所選取的索引，請先在 SQL Server Management Studio 的 [查詢] 功能表上選取 [顯示實際執行計畫]，再執行查詢。
USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
WHERE name = N'IX_Address_PostalCode')
DROP INDEX IX_Address_PostalCode ON Person.Address;
GO
CREATE NONCLUSTERED INDEX IX_Address_PostalCode
ON Person.Address (PostalCode)
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);
GO
SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN N'98000' and N'99999';
GO
H. 建立資料分割索引
下列範例會在 TransactionsPS1 這個現有的資料分割配置上建立非叢集資料分割索引。
USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
WHERE name = N'IX_TransactionHistory_ReferenceOrderID'
AND object_id = OBJECT_ID(N'Production.TransactionHistory'))
DROP INDEX IX_TransactionHistory_ReferenceOrderID
ON Production.TransactionHistory;
GO
CREATE NONCLUSTERED INDEX IX_TransactionHistory_ReferenceOrderID
ON Production.TransactionHistory (ReferenceOrderID)
ON TransactionsPS1 (TransactionDate);
GO
I. 建立已篩選的索引
下列範例會在 Production.BillOfMaterials 資料表上建立已篩選的索引。篩選述詞可以包含在已篩選之索引中不是索引鍵資料行的資料行。此範例中的述詞只會選取 EndDate 不是 NULL 的資料列。
USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
WHERE name = N'FIBillOfMaterialsWithEndDate'
AND object_id = OBJECT_ID(N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithEndDate
ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED INDEX "FIBillOfMaterialsWithEndDate"
ON Production.BillOfMaterials (ComponentID, StartDate)
WHERE EndDate IS NOT NULL;
GO
J. 建立壓縮的索引
下列範例會使用資料列壓縮，在非資料分割資料表上建立索引。
CREATE NONCLUSTERED INDEX IX_INDEX_1
ON T1 (C2)
WITH ( DATA_COMPRESSION = ROW ) ;
GO
下列範例會在索引的所有資料分割上使用資料列壓縮，以便在資料分割資料表上建立索引。
CREATE CLUSTERED INDEX IX_PartTab2Col1
ON PartitionTable1 (Col1)
WITH ( DATA_COMPRESSION = ROW ) ;
GO
下列範例會在資料分割資料表上建立索引，其方式是在索引的資料分割 1 上使用頁面壓縮，並在索引的資料分割 2 到 4 上使用資料列壓縮。
CREATE CLUSTERED INDEX IX_PartTab2Col1
ON PartitionTable1 (Col1)
WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1),
DATA_COMPRESSION = ROW ON PARTITIONS (2 TO 4 ) ) ;
GO
變更記錄
更新的內容 |
|---|
已更新 IGNORE_DUP_KEY 定義，以釐清選項何時有效。 |
已釐清 filter_predicate、conjunct、disjunct 和 comparison 的語法元素。 |
請參閱