共用方式為

CREATE INDEX (Transact-SQL)

  • 發行項

 

在指定的資料表上或指定之資料表的檢視表上建立關聯式索引。可以在資料表中有資料之前建立索引。指定限定的資料庫名稱，就可以在另一個資料庫的資料表或檢視表上建立關聯式索引。

System_CAPS_note注意事項

Azure SQL Database 第 12 版之前的資料表必須具備叢集索引，才會在資料表上允許插入作業。

如需有關如何建立 XML 索引的詳細資訊，請參閱＜CREATE XML INDEX (Transact-SQL)＞。如需有關如何建立空間索引的詳細資訊，請參閱＜CREATE SPATIAL INDEX (Transact-SQL)＞。如需有關如何建立 xVelocity 記憶體最佳化的資料行存放區索引之詳細資訊，請參閱＜CREATE COLUMNSTORE INDEX (Transact-SQL)＞。

適用於：SQL Server (SQL Server 2008 至目前版本)、Azure SQL Database、SQL Database V12(立即取得)。

主題連結圖示Transact-SQL 語法慣例

語法

          -- SQL Server Syntax (All options except filegroup and filestream apply to SQL Database Update.)

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 }
  | STATISTICS_INCREMENTAL = { 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 <conjunct> ]

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

          -- Windows Azure SQL Database Syntax 

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 ] ) ]
[ ; ]

<object> ::=
{
    [ database_name. [ schema_name ] . | schema_name. ] 
    table_or_view_name
}

<relational_index_option> ::=
{
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | DROP_EXISTING = { ON | OFF }
  | ONLINE = { ON | OFF }
  | DATA_COMPRESSION = { NONE | ROW | PAGE} 
}

<filter_predicate> ::= 
    <conjunct> [ AND <conjunct> ]

<conjunct> ::=
    <disjunct> | <comparison>

<disjunct> ::=column_name IN (constant ,…)

<comparison> ::=column_name <comparison_op> constant<comparison_op> ::=
    { IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !< }

引數

  • UNIQUE
    在資料表或檢視表上建立唯一索引。在唯一索引中，任兩個資料列都不能有相同的索引鍵值。檢視表中的叢集索引必須是唯一的。

    不論 IGNORE_DUP_KEY 是否設為 ON，Database Engine 都不允許在已包含重複值的資料行上建立唯一索引。如果嘗試執行這項作業，Database Engine 會顯示錯誤訊息。必須先移除重複值，才能在資料行上建立唯一索引。唯一索引中使用的資料行應設為 NOT NULL，因為當建立唯一索引時，多個 Null 值會被視為重複值。

  • CLUSTERED
    建立一個索引，在該索引中，索引鍵值的邏輯順序會決定資料表中對應資料列的實體順序。叢集索引的層級 (底部或分葉) 包含資料表的實際資料列。資料表或檢視表一次只允許一個叢集索引。

    含有唯一叢集索引的檢視表稱為索引檢視表。在檢視表上建立唯一叢集索引，可將檢視表實際具體化。必須先在檢視表上建立唯一叢集索引，才能在相同檢視表上定義任何其他索引。如需詳細資訊，請參閱＜建立索引檢視表＞。

    先建立叢集索引，再建立任何非叢集索引。當建立叢集索引時，會重建資料表上現有的非叢集索引。

    如果未指定 CLUSTERED，就會建立非叢集索引。

    System_CAPS_note注意事項

    依定義，叢集索引和資料頁的分葉層級是相同的，因此，建立叢集索引及有效地使用 ON partition_scheme_name 或 ON filegroup_name 子句，就可以將資料表從建立資料表的檔案群組移至新的分割區配置或檔案群組。在特定檔案群組上建立資料表或索引之前，請先確認檔案群組是可用的，而且它們有足夠的空間可供索引使用。

    在某些情況下，建立叢集索引可啟用先前停用的索引。如需詳細資訊，請參閱＜啟用索引與條件約束＞和＜停用索引和條件約束＞。

  • NONCLUSTERED
    建立索引來指定資料表的邏輯排序。如果是非叢集索引，資料列的實體順序和它們的索引順序無關。

    不論索引的建立方式為何，每一份資料表最多只能有 999 個非叢集索引：以隱含的方式使用 PRIMARY KEY 和 UNIQUE 條件約束或以明確的方式使用 CREATE INDEX。

    如果是索引檢視表，只能在已定義唯一叢集索引的檢視表上建立非叢集索引。

    預設值是 NONCLUSTERED。

  • index_name
    這是索引的名稱。在資料表或檢視表內，索引名稱必須是唯一的，但在資料庫內就不一定要是唯一的。索引名稱必須遵照識別碼的規則。

  • column
    這是做為索引根據的資料行。您可以指定兩個或兩個以上的資料行名稱，在指定之資料行的合計值上建立複合索引。在括號內的 table_or_view_name 後面，依排序優先權順序列出要併入複合索引中的資料行。

    單一複合索引鍵中最多只能結合 16 個資料行。複合索引鍵中的所有資料行都必須在相同的資料表或檢視表中。結合索引值的允許大小上限是 900 個位元組。有了 SQL Database V12，叢集和非叢集索引中使用者定義的資料行數目限制會擴大為 32 個資料行。非叢集索引大小限制會擴大為 1,700 個位元組。

    不能將屬於大型物件 (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 運算子。

    下面是一些 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)**

    適用於：SQL Server 2008 至 SQL Server 2014、SQL Database V12。

    指定分割區配置來定義要做為分割區索引之分割區對應目標的檔案群組。分割區配置必須存在於資料庫中，其方式是執行 CREATE PARTITION SCHEME 或 ALTER PARTITION SCHEME。column_name 會指定分割區索引將進行分割的資料行。此資料行必須符合 partition_scheme_name 所使用之分割區函數引數的資料類型、長度與有效位數。column_name 不限定為索引定義中的資料行。可以指定基底資料表中的任何資料行，但在分割 UNIQUE 索引時則必須從做為唯一索引鍵的資料行選擇 column_name。這項限制可讓 Database Engine 只在單一分割區內驗證索引鍵值的唯一性。

    System_CAPS_note注意事項

    當您分割一個非唯一的叢集索引時，如果尚未指定分割區資料行，依預設，Database Engine 會將它加入至叢集索引鍵清單。當您分割一個非唯一的非叢集索引時，如果尚未指定分割區資料行，Database Engine 會將它新增為索引的非索引鍵 (內含) 資料行。

    如果未指定 partition_scheme_name 或 filegroup，且已分割資料表，則會利用相同的分割區資料行，將索引放在與基礎資料表相同的分割區配置中。

    System_CAPS_note注意事項

    您無法在 XML 索引上指定分割區配置。如果基底資料表已分割，XML 索引會使用與資料表相同的分割區配置。

    如需有關分割區索引的詳細資訊，請參閱＜分割區資料表與索引＞。

  • ON filegroup_name

    適用於：SQL Server 2008 至 SQL Server 2014。

    在指定的檔案群組上建立指定的索引。如果未指定位置，且資料表或檢視表未分割，則索引會使用與基礎資料表或檢視表相同的檔案群組。此檔案群組必須已存在。

  • 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" } ]

    適用於：SQL Server 2008 至 SQL Server 2014。

    指定在建立叢集索引時，資料表之 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 (SQL Server)＞。

<object>::=

這是要建立索引的完整或非完整物件。

  • database_name
    這是資料庫的名稱。

  • schema_name
    這是資料表或檢視表所屬的結構描述名稱。

  • table_or_view_name
    這是要建立索引之資料表或檢視表的名稱。

    必須利用 SCHEMABINDING 定義檢視表，才能在該檢視表上建立索引。必須先在檢視表上建立唯一叢集索引，才能建立任何非叢集索引。如需有關索引檢視表的詳細資訊，請參閱「備註」一節。

    當 Azure SQL Database 為目前的資料庫或者 database_name 為 tempdb 而且 以 # 開頭時，schema_name 支援三部分名稱格式 .object_name[database_name].database_nameobject_name。

<relational_index_option>::=

指定當您建立索引時所需使用的選項。

  • PAD_INDEX = { ON | OFF }

    適用於：SQL Server 2008 至 SQL Server 2014、SQL Database V12。

    指定索引填補。預設值為 OFF。

    • ON
      fillfactor 指定的可用空間百分比會套用到索引的中繼層級頁面上。

    • OFF 或未指定 fillfactor
      中繼層級頁面會幾乎填滿整個容量，但會考量中繼頁面上的索引鍵集，而保留至少可供索引所能擁有之大小上限的一個資料列使用的足夠空間。

    只有在指定 FILLFACTOR 時，才能使用 PAD_INDEX 選項，因為 PAD_INDEX 會使用 FILLFACTOR 所指定的百分比。如果 FILLFACTOR 所指定的百分比不夠，無法允許一個資料列，Database Engine 會在內部覆寫該百分比以允許最小值。不論 fillfactor 的值設得多低，中繼索引頁面上的資料列數絕對不能少於兩個。

    在與舊版本相容的語法中，WITH PAD_INDEX 相當於 WITH PAD_INDEX = ON。

  • FILLFACTOR **=**fillfactor

    適用於：SQL Server 2008 至 SQL Server 2014、SQL Database V12。

    指定用以指出建立或重建索引時，Database Engine 填滿各索引頁面分葉層級之程度的百分比。fillfactor 必須是 1 到 100 之間的整數值。如果 fillfactor 是 100，Database Engine 會利用已填滿容量的分葉頁面來建立索引。

    只有在建立或重建索引時才會套用 FILLFACTOR 設定。Database Engine 不會動態保留頁面中空白空間的指定百分比。若要檢視填滿因數設定，請使用 sys.indexes 目錄檢視。

    System_CAPS_important重要事項

    利用小於 100 的 FILLFACTOR 來建立叢集索引，會影響資料所佔用的儲存空間數量，因為 Database Engine 在建立叢集索引時會轉散發資料。

    如需詳細資訊，請參閱＜指定索引的填滿因素＞。

  • SORT_IN_TEMPDB = { ON | OFF }

    適用於：SQL Server 2008 至 SQL Server 2014、SQL Database V12。

    指定是否要將暫時排序結果儲存在 tempdb 中。預設值為 OFF。

    • ON
      用來建立索引的中繼排序結果會儲存在 tempdb 中。如果 tempdb 位於使用者資料庫以外的另一組磁碟上，這種儲存方式可以減少建立索引所需的時間。不過，這會增加建立索引時所使用的磁碟空間量。

    • OFF
      中繼排序結果會儲存在與用來儲存索引相同的資料庫中。

    除了在建立索引時使用者資料庫中所需的空間以外，tempdb 還需要大約相同數量的其他空間來容納中繼排序結果。如需詳細資訊，請參閱＜索引的 SORT_IN_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。

    System_CAPS_important重要事項

    停用散發統計資料的自動重新計算，可防止查詢最佳化工具取得與資料表有關之查詢的最佳執行計畫。

    在與舊版本相容的語法中，WITH STATISTICS_NORECOMPUTE 相當於 WITH STATISTICS_NORECOMPUTE = ON。

  • STATISTICS_INCREMENTAL = { ON | OFF }
    ON 時，所建立的統計資料是依據每個分割區區的統計資料。OFF 時，會卸除統計資料樹狀結構，而 SQL Server 會重新計算統計資料。預設值是 OFF。

    如果不支援每個分割區區的統計資料，則會忽略該選項，並產生警告。針對下列統計資料類型，不支援累加統計資料：

    • 建立統計資料時，所使用的索引未與基底資料表進行分割區對齊。

    • 在 AlwaysOn 可讀取次要資料庫上建立的統計資料。

    • 在唯讀資料庫上建立的統計資料。

    • 在篩選的索引上建立的統計資料。

    • 在檢視上建立的統計資料。

    • 在內部資料表上建立的統計資料。

    • 使用空間索引或 XML 索引建立的統計資料。

  • DROP_EXISTING = { ON | OFF }
    指定要卸除及重建具名之預先存在的叢集索引或非叢集索引。預設值為 OFF。

    • ON
      卸除及重建現有的索引。所指定的索引名稱必須與目前現有的索引相同；不過，索引定義可以修改。例如，您可以指定不同的資料行、排序次序、分割區配置或索引選項。

    • OFF
      如果所指定的索引名稱已存在，畫面上會出現錯誤。

    您無法利用 DROP_EXISTING 來變更索引類型。

    在與舊版本相容的語法中，WITH DROP_EXISTING 相當於 WITH DROP_EXISTING = ON。

  • ONLINE = { ON | OFF }
    指定在索引作業期間，查詢和資料修改是否能夠使用基礎資料表和相關聯的索引。預設值為 OFF。

    System_CAPS_note注意事項

    並非所有 MicrosoftSQL Server 版本都可使用線上索引作業。如需 SQL Server 版本所支援的功能清單，請參閱＜SQL Server 2014 各版本所支援的功能＞。

    • ON
      索引作業持續期間不會保留長期資料表鎖定。在索引作業的主要階段期間，來源資料表上只保留意圖共用 (IS) 鎖定。這可使基礎資料表和索引的查詢或更新能夠進行。在作業開始時，共用 (S) 鎖定會在來源物件上保留一段很短的時間。在作業結束時，如果正在建立非叢集索引，則有一段短時間會在來源上取得 S (共用) 鎖定；或者，當以線上方式建立或卸除叢集索引時，以及正在重建叢集索引或非叢集索引時，則會取得 SCH-M (結構描述修改) 鎖定。建立本機暫存資料表的索引時，ONLINE 不可設為 ON。

    • OFF
      在索引作業期間會套用資料表鎖定。建立、重建或卸除叢集索引的離線索引作業，或重建或卸除非叢集索引的離線索引作業，會取得資料表的結構描述修改 (Sch-M) 鎖定。這可防止所有使用者在作業持續期間存取基礎資料表。建立非叢集索引的離線索引作業會取得資料表的共用 (S) 鎖定。這可避免對基礎資料表進行更新，但仍可執行讀取作業，如 SELECT 陳述式。

    如需詳細資訊，請參閱＜線上索引作業如何運作＞。

    您可以採用線上方式建立索引，包括全域暫存資料表上的索引，但以下是例外狀況：

    • XML 索引

    • 本機暫存資料表上的索引。

    • 檢視表上的初始唯一叢集索引。

    • 停用的叢集索引。

    • 叢集索引 (如果基礎資料表包含 LOB 資料類型 image、ntext、text 以及空間類型的話)。

    • varchar (max)和varbinary (max)資料行不得為索引的一部分。在SQL Server (開頭為SQL Server 2012) 以及在SQL Database V12中，當資料表包含varchar (max)或varbinary (max)資料行，使用線上選項可建立或重建包含其他資料行的叢集索引。基底資料表包含varchar (max)SQL Database或varbinary (max)資料行時，不允許線上選項。

    如需詳細資訊，請參閱＜線上執行索引作業＞。

  • ALLOW_ROW_LOCKS = { ON | OFF }

    適用於：SQL Server 2008 至 SQL Server 2014、SQL Database V12。

    指定是否允許資料列鎖定。預設值是 ON。

    • ON
      當存取索引時，允許資料列鎖定。Database Engine 會決定使用資料列鎖定的時機。

    • OFF
      不使用資料列鎖定。

  • ALLOW_PAGE_LOCKS = { ON | OFF }

    適用於：SQL Server 2008 至 SQL Server 2014、SQL Database V12。

    指定是否允許頁面鎖定。預設值是 ON。

    • ON
      當存取索引時，允許頁面鎖定。Database Engine 會決定使用頁面鎖定的時機。

    • OFF
      不使用頁面鎖定。

  • MAXDOP = max_degree_of_parallelism

    適用於：SQL Server 2008 至 SQL Server 2014，SQL Database V12 (僅限 P2 和 P3 效能層級)。

    針對索引作業持續時間覆寫 設定 max degree of parallelism 伺服器組態選項 組態選項。請利用 MAXDOP 來限制執行平行計畫所用的處理器數目。最大值是 64 個處理器。

    max_degree_of_parallelism 可以是：

    • 1
      隱藏平行計畫的產生。

    • >1
      根據目前的系統工作負載，將平行索引作業所使用的處理器數目上限，限制為所指定的數目或更少的數目。

    • 0 (預設值)
      根據目前的系統工作負載，使用實際數目或比實際數目更少的處理器。

    如需詳細資訊，請參閱＜設定平行索引作業＞。

    System_CAPS_note注意事項

    並非 MicrosoftSQL Server 的每個版本都無法使用平行索引作業。如需 SQL Server 版本所支援的功能清單，請參閱＜SQL Server 2014 各版本所支援的功能＞。

  • DATA_COMPRESSION
    針對指定的索引、分割區編號或分割區範圍指定資料壓縮選項。選項如下：

    • NONE
      不壓縮索引或指定的分割區。

    • ROW
      使用資料列壓縮來壓縮索引或指定的分割區。

    • PAGE
      使用頁面壓縮來壓縮索引或指定的分割區。

    如需有關壓縮的詳細資訊，請參閱＜資料壓縮＞。

  • ON PARTITIONS ( { <partition_number_expression> | <range> } [ ,...n ] )

    適用於：SQL Server 2008 至 SQL Server 2014、SQL Database V12。

    指定套用 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 作業上，查詢處理器可以選擇掃描另一個索引，而不是執行資料表掃描。在某些情況下，排序作業可以省略。在多處理器電腦上，CREATE INDEX 可以利用更多的處理器來執行與建立索引相關聯的掃描和排序作業，執行方式與其他查詢的執行方式相同。如需詳細資訊，請參閱＜設定平行索引作業＞。

如果資料庫復原模式設為大量記錄或簡單模式，建立索引作業就可以利用最低限度記錄。

索引可以在暫存資料表上建立。當資料表卸除或工作階段結束時，就會卸除索引。

索引支援擴充屬性。

叢集索引

若要在資料表 (堆積) 上建立叢集索引，或要卸除及重新建立現有的叢集索引，則資料庫中必須有可用的其他工作空間，才能容納資料排序和原始資料表或現有叢集索引資料的暫存複本。如需有關叢集索引的詳細資訊，請參閱＜建立叢集索引＞。

唯一索引

如果唯一索引存在，每當插入作業新增資料時，Database Engine 都會確認是否有重複的值。可能產生重複索引鍵值的插入作業會回復，且 Database Engine 會顯示錯誤訊息。即使插入作業變更多個資料列但只造成一個重複的值，一樣會發生這種情況。如果嘗試輸入資料，而該資料有唯一索引，且該資料的 IGNORE_DUP_KEY 子句設為 ON，則只有違反 UNIQUE 索引規則的資料列會失敗。

分割區索引

分割區索引與分割區資料表的建立和維護方式類似，不過，跟一般索引一樣，分割區索引會當做個別資料庫物件來處理。未進行分割的資料表上可以有分割區索引；已進行分割的資料表上也可以有非分割區索引。

如果您要在分割區資料表上建立索引，且不指定要從中放置索引的檔案群組，則索引的分割區方式與基礎資料表相同。這是因為索引及其基礎資料表預設會置於同一個檔案群組內，且索引會供相同分割區配置中，使用相同分割區資料行的分割區資料表使用。當索引使用與資料表皆使用相同的分割區配置及分割區資料行時，索引將會以資料表*「為準」*(Aligned)。

警告

您可以對包含超過 1,000 個分割區的資料表，建立及重建不以資料表為準的索引，但不予支援。此做法可能會導致在作業期間效能降低或耗用過多記憶體。建議當分割區數超過 1,000 時，一律使用以資料表為準的索引。

分割區不是唯一的叢集索引時若未指定分割區資料行，Database Engine 預設會將其加入叢集索引鍵清單。

您可以對分割區資料表建立索引檢視表，其方法與建立資料表索引相同。如需有關分割區索引的詳細資訊，請參閱＜分割區資料表與索引＞。

SQL Server 2014 並不會在建立或重建分割區索引之後掃描資料表中所有的資料列建立統計資料。反之，查詢最佳化工具會使用預設的採樣演算法來產生統計資料。如果要在掃描資料表中所有資料列時取得分割區索引的統計資料，請使用 CREATE STATISTICS 或 UPDATE STATISTICS 搭配 FULLSCAN 子句。

篩選的索引

已篩選的索引是最佳化的非叢集索引，適用於從資料表選取小型資料列百分比的查詢使用。它會使用篩選述詞，針對資料表中的部分資料建立索引。設計良好的已篩選索引可以提升查詢效能、降低儲存成本，並減少維護成本。

需要已篩選之索引的 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)＞和＜空間索引概觀＞。

XML 索引

如需有關 XML 索引的詳細資訊，請參閱＜CREATE XML INDEX (Transact-SQL)＞和＜XML 索引 (SQL Server)＞。

索引鍵大小

SQL Server 索引鍵的大小上限是 900 個位元組。在 SQL Database V12 上，非叢集索引大小限制會擴大 (從 900 個位元組) 為 1,700 個位元組。如果資料行中現有的資料未超出建立索引時的限制，則 varchar 資料行上超過位元組限制的索引可以建立；但是，後續在資料行進行插入或更新動作時，如果造成總計大小超過限制，則會失敗。叢集索引的索引鍵所包含的 varchar 資料行不能在 ROW_OVERFLOW_DATA 配置單位中有現有的資料。如果在 varchar 資料行上建立叢集索引，且現有的資料在 IN_ROW_DATA 配置單位中，則後續在可能發送資料非資料列的資料行上進行的插入或更新動作會失敗。

非叢集索引可將非索引鍵資料行併入索引的分葉層級中。在計算索引鍵大小時，Database Engine 不會考量這些資料行。如需詳細資訊，請參閱＜建立內含資料行的索引＞。

System_CAPS_note注意事項

分割資料表時，如果分割區索引鍵資料行原本不存在非唯一的叢集索引中，Database Engine 就會將它們加入至索引。在非唯一的叢集索引中，索引資料行 (不計算包含的資料行) 再加上任何加入之分割區資料行的組合大小不得超過 1800 個位元組。

計算資料行

索引可以在計算資料行上建立。此外，計算資料行可以有 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 導入新的索引選項，並修改選項的指定方式。在與舊版本相容的語法中，WITH 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 會選擇適當的鎖定，且可以將鎖定從資料列或頁面鎖定擴大到資料表鎖定。

如果 ALLOW_ROW_LOCKS = OFF 且 ALLOW_PAGE_LOCK = OFF，當您存取索引時，只允許使用資料表層級的鎖定。

檢視索引資訊

若要傳回有關索引的資訊，您可以使用目錄檢視、系統函數和系統預存程序。

資料壓縮

＜資料壓縮＞主題中有描述資料壓縮。以下是考量的幾個要點：

  • 壓縮可讓更多的資料列儲存在頁面上，但是不會變更最大資料列大小。

  • 索引的非分葉頁面不會進行頁面壓縮，但是可以進行資料列壓縮。

  • 每一個非叢集索引都有個別的壓縮設定，而且不會繼承基礎資料表的壓縮設定。

  • 在堆積上建立叢集索引時，此叢集索引會繼承堆積的壓縮狀態，除非指定了替代的壓縮狀態。

下列限制適用於分割區索引：

  • 您無法在資料表具有非對齊索引時變更單一分割區的壓縮設定。

  • ALTER INDEX <index> ...REBUILD PARTITION ... 語法會重建此索引的指定資料分割。

  • ALTER INDEX <index> ...REBUILD WITH ... 語法會重建此索引的所有資料分割。

若要評估變更壓縮狀態如何影響資料表、索引或分割區，請使用 sp_estimate_data_compression_savings 預存程序。

Permissions

需要資料表或檢視表的 ALTER 權限。使用者必須是系統管理員 (sysadmin) 固定伺服器角色的成員，或是 db_ddladmin 和 db_owner 固定資料庫角色的成員。

範例

A.建立簡單的非叢集索引

下列範例會在 VendorID 資料庫中 Purchasing.ProductVendor 資料表的 AdventureWorks2012 資料行上建立非叢集索引。

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

B.建立簡單的非叢集複合式索引

下列範例會在 SalesQuota 資料庫中 SalesYTD 資料表的 Sales.SalesPerson 和 AdventureWorks2012 資料行上建立非叢集複合索引。

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.建立唯一的非叢集索引

下列範例會在 Name 資料庫中 Production.UnitMeasure 資料表的 AdventureWorks2012 資料行上建立唯一非叢集索引。索引會強制將資料上的唯一性插入 Name 資料行中。

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

下列查詢會藉由嘗試插入與現有資料列具有相同值的資料列，以測試條件約束的唯一性。

--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 選項

下列範例分別利用兩種不同的選項設定 (先將選項設為 IGNORE_DUP_KEY，再將選項設為 ON) 將多個資料列插入暫存資料表中，示範 OFF 選項的效果。單一資料列會插入 #Test 資料表中，該資料表則會在第二個多重資料列 INSERT 陳述式執行時刻意造成重複的值。資料表中的資料列計數會傳回所插入的資料列數目。

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。

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 卸除及重新建立索引

下列範例會利用 ProductID 選項，在 Production.WorkOrder 資料庫中 AdventureWorks2012 資料表的 DROP_EXISTING 資料行上卸除及重新建立現有的索引。也會設定 FILLFACTOR 和 PAD_INDEX 選項。

CREATE NONCLUSTERED INDEX IX_WorkOrder_ProductID
    ON Production.WorkOrder(ProductID)
    WITH (FILLFACTOR = 80,
        PAD_INDEX = ON,
        DROP_EXISTING = ON);
GO

F.建立檢視表的索引

下列範例會在該檢視表上建立檢視表和索引。內含使用索引檢視的兩項查詢。

--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 功能表上選取 [顯示實際執行計畫]，再執行查詢。

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 資料庫中現有的分割區配置 AdventureWorks2012 上建立非叢集分割區索引。此範例假設您已安裝分割區索引範例。

適用於：SQL Server 2008 至 SQL Server 2014、SQL Database V12。

 
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.建立篩選的索引

下列範例會對 AdventureWorks2012 資料庫中的 Production.BillOfMaterials 資料表建立篩選的索引。篩選述詞可以包含在已篩選之索引中不是索引鍵資料行的資料行。此範例中的述詞只會選取 EndDate 不是 NULL 的資料列。

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;

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

請參閱

ALTER INDEX (Transact-SQL)
CREATE PARTITION FUNCTION (Transact-SQL)
CREATE PARTITION SCHEME (Transact-SQL)
CREATE SPATIAL INDEX (Transact-SQL)
CREATE STATISTICS (Transact-SQL)
CREATE TABLE (Transact-SQL)
CREATE XML INDEX (Transact-SQL)
資料類型 (Transact-SQL)
DBCC SHOW_STATISTICS (Transact-SQL)
DROP INDEX (Transact-SQL)
XML 索引 (SQL Server)
sys.indexes (Transact-SQL)
sys.index_columns (Transact-SQL)
sys.xml_indexes (Transact-SQL)
EVENTDATA (Transact-SQL)