CREATE INDEX (Transact-SQL)CREATE INDEX (Transact-SQL)

適用於: 是SQL Server 是Azure SQL Database 是Azure Synapse Analytics (SQL DW) 是平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

在資料表或檢視上建立關聯式索引。Creates a relational index on a table or view. 也稱為資料列存放區索引,因為它是叢集或非叢集的 B 型樹狀結構索引。Also called a rowstore index because it is either a clustered or nonclustered B-tree index. 您可以在資料表中含有資料之前,先建立資料列存放區索引。You can create a rowstore index before there is data in the table. 特別是在查詢會從特定資料行中選取,或需要以特定順序排序值時,使用資料列存放區索引來改善查詢效能。Use a rowstore index to improve query performance, especially when the queries select from specific columns or require values to be sorted in a particular order.

注意

SQL 資料倉儲SQL Data Warehouse 平行處理資料倉儲Parallel Data Warehouse 目前不支援 Unique 條件約束。and 平行處理資料倉儲Parallel Data Warehouse currently do not support Unique constraints. 參考 Unique 條件約束的任何範例僅適用於 SQL ServerSQL ServerSQL DatabaseSQL DatabaseAny examples referencing Unique Constraints are only applicable to SQL ServerSQL Server and SQL DatabaseSQL Database.

提示

如需索引設計指導方針的詳細資訊,請參閱 SQL Server 索引設計指南For information on index design guidelines, refer to the SQL Server Index Design Guide.

簡單範例:Simple examples:

-- Create a nonclustered index on a table or view
CREATE INDEX i1 ON t1 (col1);

-- Create a clustered index on a table and use a 3-part name for the table
CREATE CLUSTERED INDEX i1 ON d1.s1.t1 (col1);

-- Syntax for SQL Server and Azure SQL Database
-- Create a nonclustered index with a unique constraint
-- on 3 columns and specify the sort order for each column
CREATE UNIQUE INDEX i1 ON t1 (col1 DESC, col2 ASC, col3 DESC);

關鍵案例:Key scenario:

SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL DatabaseSQL Database 開始,使用資料行存放區索引上的非叢集索引來改善資料倉儲查詢效能。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) and SQL DatabaseSQL Database, use a nonclustered index on a columnstore index to improve data warehousing query performance. 如需詳細資訊,請參閱資料行存放區索引 - 資料倉儲For more information, see Columnstore Indexes - Data Warehouse.

針對其他索引類型,請參閱:For additional types of indexes, see:

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

SQL Server 和 Azure SQL Database 的語法Syntax for SQL Server and Azure SQL Database

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.table_or_view_name | schema_name.table_or_view_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 }
  | RESUMABLE = {ON | OF }
  | MAX_DURATION = <time> [MINUTES]
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | OPTIMIZE_FOR_SEQUENTIAL_KEY = { 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 Index

重要

SQL ServerSQL Server 的未來版本將會移除與舊版相容的關聯式索引語法結構。The backward compatible relational index syntax structure will be removed in a future version of SQL ServerSQL Server. 請避免在新的開發工作中使用此語法結構,並規劃修改目前使用此功能的應用程式。Avoid using this syntax structure in new development work, and plan to modify applications that currently use the feature. 改為使用 <relational_index_option> 中指定的語法結構。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
}

Azure SQL 資料倉儲和平行處理資料倉儲的語法Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse


CREATE CLUSTERED COLUMNSTORE INDEX INDEX index_name
    ON [ database_name . [ schema ] . | schema . ] table_name
    [ORDER (column[,...n])]
    [WITH ( DROP_EXISTING = { ON | OFF } )]
[;]


CREATE [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON [ database_name . [ schema ] . | schema . ] table_name
        ( { column [ ASC | DESC ] } [ ,...n ] )
    WITH ( DROP_EXISTING = { ON | OFF } )
[;]


引數Arguments

UNIQUEUNIQUE
在資料表或檢視上建立唯一索引。Creates a unique index on a table or view. 在唯一索引中,任兩個資料列都不能有相同的索引鍵值。A unique index is one in which no two rows are permitted to have the same index key value. 檢視表中的叢集索引必須是唯一的。A clustered index on a view must be unique.

不論 IGNORE_DUP_KEY 是否設為 ON,Database EngineDatabase Engine 都不允許在已包含重複值的資料行上建立唯一索引。The Database EngineDatabase Engine does not allow creating a unique index on columns that already include duplicate values, whether or not IGNORE_DUP_KEY is set to ON. 如果嘗試執行這項作業,Database EngineDatabase Engine 會顯示錯誤訊息。If this is tried, the Database EngineDatabase Engine displays an error message. 必須先移除重複值,才能在資料行上建立唯一索引。Duplicate values must be removed before a unique index can be created on the column or columns. 唯一索引中使用的資料行應設為 NOT NULL,因為當建立唯一索引時,多個 Null 值會被視為重複值。Columns that are used in a unique index should be set to NOT NULL, because multiple null values are considered duplicates when a unique index is created.

CLUSTEREDCLUSTERED
建立索引,在該索引中,索引鍵值的邏輯順序會決定資料表中對應資料列的實體順序。Creates an index in which the logical order of the key values determines the physical order of the corresponding rows in a table. 叢集索引的層級 (底部或分葉) 包含資料表的實際資料列。The bottom, or leaf, level of the clustered index contains the actual data rows of the table. 資料表或檢視表一次只允許一個叢集索引。A table or view is allowed one clustered index at a time.

含有唯一叢集索引的檢視表稱為索引檢視表。A view with a unique clustered index is called an indexed view. 在檢視表上建立唯一叢集索引,可將檢視表實際具體化。Creating a unique clustered index on a view physically materializes the view. 必須先在檢視表上建立唯一叢集索引,才能在相同檢視表上定義任何其他索引。A unique clustered index must be created on a view before any other indexes can be defined on the same view. 如需詳細資訊,請參閱 建立索引檢視表For more information, see Create Indexed Views.

先建立叢集索引,再建立任何非叢集索引。Create the clustered index before creating any nonclustered indexes. 當建立叢集索引時,會重建資料表上現有的非叢集索引。Existing nonclustered indexes on tables are rebuilt when a clustered index is created.

如果未指定 CLUSTERED,就會建立非叢集索引。If CLUSTERED is not specified, a nonclustered index is created.

注意

依定義,叢集索引和資料頁面的分葉層級都一樣,因此,建立叢集索引並有效地使用 ON partition_scheme_name 或 ON filegroup_name 子句,就可將資料表從建立資料表的檔案群組移至新的資料分割配置或檔案群組。Because the leaf level of a clustered index and the data pages are the same by definition, creating a clustered index and using the ON partition_scheme_name or ON filegroup_name clause effectively moves a table from the filegroup on which the table was created to the new partition scheme or filegroup. 在特定檔案群組上建立資料表或索引之前,請先確認檔案群組是可用的,而且它們有足夠的空間可供索引使用。Before creating tables or indexes on specific filegroups, verify which filegroups are available and that they have enough empty space for the index.

在某些情況下,建立叢集索引可啟用先前停用的索引。In some cases creating a clustered index can enable previously disabled indexes. 如需詳細資訊,請參閱啟用索引與條件約束停用索引與條件約束For more information, see Enable Indexes and Constraints and Disable Indexes and Constraints.

NONCLUSTEREDNONCLUSTERED
建立指定資料表邏輯順序的索引。Creates an index that specifies the logical ordering of a table. 如果是非叢集索引,資料列的實體順序和它們的索引順序無關。With a nonclustered index, the physical order of the data rows is independent of their indexed order.

不論索引的建立方式為何,每一份資料表最多只能有 999 個非叢集索引:以隱含的方式使用 PRIMARY KEY 和 UNIQUE 條件約束或以明確的方式使用 CREATE INDEX。Each table can have up to 999 nonclustered indexes, regardless of how the indexes are created: either implicitly with PRIMARY KEY and UNIQUE constraints, or explicitly with CREATE INDEX.

如果是索引檢視表,只能在已定義唯一叢集索引的檢視表上建立非叢集索引。For indexed views, nonclustered indexes can be created only on a view that has a unique clustered index already defined.

如果未指定,預設的索引類型為 NONCLUSTERED。If not otherwise specified, the default index type is NONCLUSTERED.

index_name index_name
這是索引的名稱。Is the name of the index. 在資料表或檢視表內,索引名稱必須是唯一的,但在資料庫內就不一定要是唯一的。Index names must be unique within a table or view, but do not have to be unique within a database. 索引名稱必須遵照識別碼的規則。Index names must follow the rules of identifiers.

column column
這是索引作為依據的資料行。Is the column or columns on which the index is based. 您可以指定兩個或兩個以上的資料行名稱,在指定之資料行的合計值上建立複合索引。Specify two or more column names to create a composite index on the combined values in the specified columns. table_or_view_name 後面的括號內,依排序優先權順序列出要併入複合式索引的資料行。List the columns to be included in the composite index, in sort-priority order, inside the parentheses after table_or_view_name.

單一複合式索引鍵中最多只能結合 32 個資料行。Up to 32 columns can be combined into a single composite index key. 複合索引鍵中的所有資料行都必須在相同的資料表或檢視表中。All the columns in a composite index key must be in the same table or view. 針對叢集索引,組合索引值的允許大小上限是 900 個位元組,非叢集索引則為 1,700 個位元組。The maximum allowable size of the combined index values is 900 bytes for a clustered index, or 1,700 for a nonclustered index. 針對 SQL DatabaseSQL DatabaseSQL Server 2016 (13.x)SQL Server 2016 (13.x) 之前的版本,限制為 16 個資料行與 900 個位元組。The limits are 16 columns and 900 bytes for versions before SQL DatabaseSQL Database and SQL Server 2016 (13.x)SQL Server 2016 (13.x).

屬於大型物件 (LOB) 資料類型 ntexttextvarchar(max)nvarchar(max)varbinary(max)xmlimage 的資料行無法指定為索引的索引鍵資料行。Columns that are of the large object (LOB) data types ntext, text, varchar(max), nvarchar(max), varbinary(max), xml, or image cannot be specified as key columns for an index. 此外,即使 CREATE INDEX 陳述式中未參考 ntexttextimage 資料行,檢視定義也不能包含這些資料行。Also, a view definition cannot include ntext, text, or image columns, even if they are not referenced in the CREATE INDEX statement.

如果 CLR 使用者定義型別支援二進位排序,您可以在該型別的資料行上建立索引。You can create indexes on CLR user-defined type columns if the type supports binary ordering. 只要方法標示為具決定性且不執行資料存取作業,您也可以在定義為使用者定義型別資料行方法引動過程的計算資料行上建立索引。You can also create indexes on computed columns that are defined as method invocations off a user-defined type column, as long as the methods are marked deterministic and do not perform data access operations. 如需編製 CLR 使用者定義類型資料行索引的詳細資訊,請參閱 CLR 使用者定義類型For more information about indexing CLR user-defined type columns, see CLR User-defined Types.

[ ASC | DESC ][ ASC | DESC ]
決定特定索引資料行的遞增或遞減排序方向。Determines the ascending or descending sort direction for the particular index column. 預設值是 ASC。The default is ASC.

INCLUDE ( column [ , ... n ] ) INCLUDE (column [ ,... n ] )
指定要新增至非叢集索引分葉層級的非索引鍵資料行。Specifies the non-key columns to be added to the leaf level of the nonclustered index. 非叢集索引可以是唯一或非唯一的。The nonclustered index can be unique or non-unique.

資料行名稱在 INCLUDE 清單中不能重複,且不能同時做為索引鍵資料行和非索引鍵資料行。Column names cannot be repeated in the INCLUDE list and cannot be used simultaneously as both key and non-key columns. 如果資料表上有定義叢集索引,非叢集索引一定會包含叢集索引資料行。Nonclustered indexes always contain the clustered index columns if a clustered index is defined on the table. 如需詳細資訊,請參閱 建立內含資料行的索引For more information, see Create Indexes with Included Columns.

允許所有的資料類型,除了 textntextimage以外。All data types are allowed except text, ntext, and image. 如果任一個指定的非索引鍵資料行屬於 varchar(max)nvarchar(max)varbinary(max) 資料類型,就必須離線 (ONLINE = OFF) 建立或重建索引。The index must be created or rebuilt offline (ONLINE = OFF) if any one of the specified non-key columns are varchar(max), nvarchar(max), or varbinary(max) data types.

具決定性之精確或非精確的計算資料行都可以當做內含資料行。Computed columns that are deterministic and either precise or imprecise can be included columns. imagentexttextvarchar(max)nvarchar(max)varbinary(max)xml 資料類型衍生的計算資料行,只要計算資料行資料類型可作為內含資料行,就可包含於非索引鍵的資料行中。Computed columns derived from image, ntext, text, varchar(max), nvarchar(max), varbinary(max), and xml data types can be included in non-key columns as long as the computed column data types is allowable as an included column. 如需詳細資訊,請參閱 計算資料行的索引For more information, see Indexes on Computed Columns.

如需建立 XML 索引的資訊,請參閱 CREATE XML INDEXFor information on creating an XML index, see CREATE XML INDEX.

WHERE <filter_predicate>WHERE <filter_predicate>
藉由指定哪些資料列要包含在索引中,來建立篩選的索引。Creates a filtered index by specifying which rows to include in the index. 已篩選的索引必須是資料表上的非叢集索引。The filtered index must be a nonclustered index on a table. 針對已篩選之索引中的資料列建立已篩選的統計資料。Creates filtered statistics for the data rows in the filtered index.

篩選述詞會使用簡單比較邏輯,而且無法參考計算資料行、UDT 資料行、空間資料類型資料行或 hierarchyID 資料類型資料行。The filter predicate uses simple comparison logic and cannot reference a computed column, a UDT column, a spatial data type column, or a hierarchyID data type column. 比較運算子不允許使用 NULL 常值的比較。Comparisons using NULL literals are not allowed with the comparison operators. 請改用 IS NULL 和 IS NOT NULL 運算子。Use the IS NULL and IS NOT NULL operators instead.

下面是一些 Production.BillOfMaterials 資料表之篩選述詞的範例:Here are some examples of filter predicates for the Production.BillOfMaterials table:

WHERE StartDate > '20000101' AND EndDate <= '20000630'

WHERE ComponentID IN (533, 324, 753)

WHERE StartDate IN ('20000404', '20000905') AND EndDate IS NOT NULL

已篩選的索引不適用於 XML 索引和全文檢索索引。Filtered indexes do not apply to XML indexes and full-text indexes. 如果是 UNIQUE 索引,只有選取的資料列必須有唯一的索引值。For UNIQUE indexes, only the selected rows must have unique index values. 已篩選的索引不允許 IGNORE_DUP_KEY 選項。Filtered indexes do not allow the IGNORE_DUP_KEY option.

ON partition_scheme_name ( column_name )ON partition_scheme_name ( column_name )

適用於SQL ServerSQL Server (SQL Server 2008SQL Server 2008 及更新版本) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 and later) and Azure SQL DatabaseAzure SQL Database

指定分割區配置來定義要做為分割區索引之分割區對應目標的檔案群組。Specifies the partition scheme that defines the filegroups onto which the partitions of a partitioned index will be mapped. 透過執行 CREATE PARTITION SCHEMEALTER PARTITION SCHEME,讓資料分割配置一定會存在於資料庫內。The partition scheme must exist within the database by executing either CREATE PARTITION SCHEME or ALTER PARTITION SCHEME. column_name 會指定資料分割索引將進行分割的資料行。column_name specifies the column against which a partitioned index will be partitioned. 此資料行必須符合 partition_scheme_name 所使用資料分割函數引數的資料類型、長度與有效位數。This column must match the data type, length, and precision of the argument of the partition function that partition_scheme_name is using. column_name 不限定為索引定義中的資料行。column_name is not restricted to the columns in the index definition. 可以指定基底資料表中的任何資料行,但有個例外是,在分割 UNIQUE 索引時,必須從用來作為唯一索引鍵使用的資料行中選擇 column_nameAny column in the base table can be specified, except when partitioning a UNIQUE index, column_name must be chosen from among those used as the unique key. 這項限制可讓 Database EngineDatabase Engine 只在單一分割區內驗證索引鍵值的唯一性。This restriction allows the Database EngineDatabase Engine to verify uniqueness of key values within a single partition only.

注意

當您分割一個非唯一的叢集索引時,如果尚未指定分割區資料行,依預設,Database EngineDatabase Engine 會將它加入至叢集索引鍵清單。When you partition a non-unique, clustered index, the Database EngineDatabase Engine by default adds the partitioning column to the list of clustered index keys, if it is not already specified. 當您分割一個非唯一的非叢集索引時,如果尚未指定分割區資料行,Database EngineDatabase Engine 會將它新增為索引的非索引鍵 (內含) 資料行。When partitioning a non-unique, nonclustered index, the Database EngineDatabase Engine adds the partitioning column as a non-key (included) column of the index, if it is not already specified.

如果未指定 partition_scheme_namefilegroup,且已分割資料表,則會使用相同的分割資料行,將索引放在與基礎資料表相同的資料分割配置中。If partition_scheme_name or filegroup is not specified and the table is partitioned, the index is placed in the same partition scheme, using the same partitioning column, as the underlying table.

注意

您無法在 XML 索引上指定分割區配置。You cannot specify a partitioning scheme on an XML index. 如果基底資料表已分割,XML 索引會使用與資料表相同的分割區配置。If the base table is partitioned, the XML index uses the same partition scheme as the table.

如需分割索引的詳細資訊,請參閱資料分割資料表與索引For more information about partitioning indexes, Partitioned Tables and Indexes.

ON filegroup_nameON filegroup_name

適用於SQL ServerSQL Server (SQL Server 2008SQL Server 2008 及更新版本)Applies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 and later)

在指定的檔案群組上建立指定的索引。Creates the specified index on the specified filegroup. 如果未指定位置,且資料表或檢視表未分割,則索引會使用與基礎資料表或檢視表相同的檔案群組。If no location is specified and the table or view is not partitioned, the index uses the same filegroup as the underlying table or view. 此檔案群組必須已存在。The filegroup must already exist.

ON " default "ON " default "

適用於SQL ServerSQL Server (SQL Server 2008SQL Server 2008 及更新版本) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 and later) and Azure SQL DatabaseAzure SQL Database

在與資料表或檢視相同的檔案群組或分割區結構描述上建立所指定索引。Creates the specified index on the same filegroup or partition scheme as the table or view.

在這個內容中,default 這個字不是關鍵字。The term default, in this context, is not a keyword. 它是預設檔案群組的識別碼,必須加以分隔,如 ON " default " 或 ON [ default ]It is an identifier for the default filegroup and must be delimited, as in ON " default " or ON [ default ]. 如果指定了 "default",目前工作階段的 QUOTED_IDENTIFIER 選項就必須是 ON。If "default" is specified, the QUOTED_IDENTIFIER option must be ON for the current session. 這是預設值。This is the default setting. 如需詳細資訊,請參閱 SET QUOTED_IDENTIFIERFor more information, see SET QUOTED_IDENTIFIER.

注意

"default" 並未指出 CREATE INDEX 內容中的資料庫預設檔案群組。"default" does not indicate the database default filegroup in the context of CREATE INDEX. 這不同於 CREATE TABLE,其中的 "default" 會將資料表定位在資料庫的預設檔案群組上。This differs from CREATE TABLE, where "default" locates the table on the database default filegroup.

[ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ][ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]

適用於SQL ServerSQL Server (SQL Server 2008SQL Server 2008 及更新版本)Applies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 and later)

指定在建立叢集索引時,資料表之 FILESTREAM 資料的位置。Specifies the placement of FILESTREAM data for the table when a clustered index is created. FILESTREAM_ON 子句允許將 FILESTREAM 資料移到不同的 FILESTREAM 檔案群組或分割區配置。The FILESTREAM_ON clause allows FILESTREAM data to be moved to a different FILESTREAM filegroup or partition scheme.

filestream_filegroup_name 是 FILESTREAM 檔案群組的名稱。filestream_filegroup_name is the name of a FILESTREAM filegroup. 此檔案群組必須有一個使用 CREATE DATABASEALTER DATABASE 陳述式針對此檔案群組定義的檔案,否則會引發錯誤。The filegroup must have one file defined for the filegroup by using a CREATE DATABASE or ALTER DATABASE statement; otherwise, an error is raised.

如果分割此資料表,則必須包含 FILESTREAM_ON 子句,而且必須指定 FILESTREAM 檔案群組的分割區配置,此配置會使用與資料表之分割區配置相同的分割區函數和分割區資料行。If the table is partitioned, the FILESTREAM_ON clause must be included and must specify a partition scheme of FILESTREAM filegroups that uses the same partition function and partition columns as the partition scheme for the table. 否則,就會引發錯誤。Otherwise, an error is raised.

如果此資料表未分割,FILESTREAM 資料行將無法分割。If the table is not partitioned, the FILESTREAM column cannot be partitioned. 此資料表的 FILESTREAM 資料必須儲存在 FILESTREAM_ON 子句中指定的單一檔案群組內。FILESTREAM data for the table must be stored in a single filegroup that is specified in the FILESTREAM_ON clause.

如果正在建立叢集索引,而且此資料表不包含 FILESTREAM 資料行,則可以在 CREATE INDEX 陳述式內指定 FILESTREAM_ON NULLFILESTREAM_ON NULL can be specified in a CREATE INDEX statement if a clustered index is being created and the table does not contain a FILESTREAM column.

如需詳細資訊,請參閱 FILESTREAM (SQL Server)For more information, see FILESTREAM (SQL Server).

<object>::=<object>::=

這是要建立索引的完整或非完整物件。Is the fully qualified or nonfully qualified object to be indexed.

database_name database_name
這是資料庫的名稱。Is the name of the database.

schema_name schema_name
這是資料表或檢視表所屬的結構描述名稱。Is the name of the schema to which the table or view belongs.

table_or_view_name table_or_view_name
這是要索引的資料表或檢視名稱。Is the name of the table or view to be indexed.

必須利用 SCHEMABINDING 定義檢視表,才能在該檢視表上建立索引。The view must be defined with SCHEMABINDING to create an index on it. 必須先在檢視表上建立唯一叢集索引,才能建立任何非叢集索引。A unique clustered index must be created on a view before any nonclustered index is created. 如需有關索引檢視表的詳細資訊,請參閱「備註」一節。For more information about indexed views, see the Remarks section.

SQL Server 2016 (13.x)SQL Server 2016 (13.x) 開始,此物件可以是與叢集資料行存放區索引一併儲存的資料表。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), the object can be a table stored with a clustered columnstore index.

database_name 是目前的資料庫或 database_nametempdb,且 object_name 的開頭為 # 時,Azure SQL DatabaseAzure SQL Database 支援三部分名稱格式 database_name.[schema_name].object_nameAzure SQL DatabaseAzure SQL Database supports the three-part name format database_name.[schema_name].object_name when the database_name is the current database or the database_name is tempdb and the object_name starts with #.

<relational_index_option>::= <relational_index_option>::=
指定當您建立索引時所需使用的選項。Specifies the options to use when you create the index.

PAD_INDEX = { ON | OFF }PAD_INDEX = { ON | OFF }

適用於SQL ServerSQL Server (SQL Server 2008SQL Server 2008 及更新版本) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 and later) and Azure SQL DatabaseAzure SQL Database

指定索引填補。Specifies index padding. 預設值為 OFF。The default is OFF.

ONON
fillfactor 指定的可用空間百分比會套用到索引的中繼層級頁面。The percentage of free space that is specified by fillfactor is applied to the intermediate-level pages of the index.

OFF 或未指定 fillfactorOFF or fillfactor is not specified
中繼層級頁面會幾乎填滿整個容量,但會考量中繼頁面上的索引鍵集,而保留至少可供索引所能擁有之大小上限的一個資料列使用的足夠空間。The intermediate-level pages are filled to near capacity, leaving sufficient space for at least one row of the maximum size the index can have, considering the set of keys on the intermediate pages.

只有在指定 FILLFACTOR 時,才能使用 PAD_INDEX 選項,因為 PAD_INDEX 會使用 FILLFACTOR 所指定的百分比。The PAD_INDEX option is useful only when FILLFACTOR is specified, because PAD_INDEX uses the percentage specified by FILLFACTOR. 如果 FILLFACTOR 所指定的百分比不夠,無法允許一個資料列,Database EngineDatabase Engine 會在內部覆寫該百分比以允許最小值。If the percentage specified for FILLFACTOR is not large enough to allow for one row, the Database EngineDatabase Engine internally overrides the percentage to allow for the minimum. 不論 fillfactor 的值設得多低,中繼索引頁面上的資料列數目絕對不能少於兩個。The number of rows on an intermediate index page is never less than two, regardless of how low the value of fillfactor.

在與舊版本相容的語法中,WITH PAD_INDEX 相當於 WITH PAD_INDEX = ON。In backward compatible syntax, WITH PAD_INDEX is equivalent to WITH PAD_INDEX = ON.

FILLFACTOR = fillfactorFILLFACTOR =fillfactor

適用於SQL ServerSQL Server (SQL Server 2008SQL Server 2008 及更新版本) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 and later) and Azure SQL DatabaseAzure SQL Database

指定用以指出建立或重建索引時,Database EngineDatabase Engine 填滿各索引頁面分葉層級之程度的百分比。Specifies a percentage that indicates how full the Database EngineDatabase Engine should make the leaf level of each index page during index creation or rebuild. fillfactor 必須是 1 到 100 之間的整數值。fillfactor must be an integer value from 1 to 100. 如果 fillfactor 是 100,Database EngineDatabase Engine 會利用已填滿容量的分葉頁面來建立索引。If fillfactor is 100, the Database EngineDatabase Engine creates indexes with leaf pages filled to capacity.

只有在建立或重建索引時才會套用 FILLFACTOR 設定。The FILLFACTOR setting applies only when the index is created or rebuilt. Database EngineDatabase Engine 不會動態保留頁面中空白空間的指定百分比。The Database EngineDatabase Engine does not dynamically keep the specified percentage of empty space in the pages. 若要檢視填滿因數設定,請使用 sys.indexes 目錄檢視表。To view the fill factor setting, use the sys.indexes catalog view.

重要

利用小於 100 的 FILLFACTOR 來建立叢集索引,會影響資料所佔用的儲存空間數量,因為 Database EngineDatabase Engine 在建立叢集索引時會轉散發資料。Creating a clustered index with a FILLFACTOR less than 100 affects the amount of storage space the data occupies because the Database EngineDatabase Engine redistributes the data when it creates the clustered index.

如需詳細資訊,請參閱 指定索引的填滿因素For more information, see Specify Fill Factor for an Index.

SORT_IN_TEMPDB = { ON | OFF }SORT_IN_TEMPDB = { ON | OFF }

適用於SQL ServerSQL Server (SQL Server 2008SQL Server 2008 及更新版本) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 and later) and Azure SQL DatabaseAzure SQL Database

指定是否要將暫時排序結果儲存在 tempdb 中。Specifies whether to store temporary sort results in tempdb. 預設值為 OFF (除了 Azure SQL Database 超大規模資料庫以外)。若是超大規模資料庫中的所有索引建置作業,除非使用可繼續的索引重建,否則 SORT_IN_TEMPDB 一律會是 ON。The default is OFF except for Azure SQL Database Hyperscale.For all index build operations in Hyperscale, SORT_IN_TEMPDB is always ON, regardless of the option specified unless resumable index rebuild is used.

ONON
用來建置索引的中繼排序結果會儲存在 tempdb 中。The intermediate sort results that are used to build the index are stored in tempdb. 如果 tempdb 位於與使用者資料庫所在磁碟不同的磁碟上,這可能會減少建立索引所需的時間。This may reduce the time required to create an index if tempdb is on a different set of disks than the user database. 不過,這會增加建立索引時所使用的磁碟空間量。However, this increases the amount of disk space that is used during the index build.

OFFOFF
中繼排序結果會儲存在與用來儲存索引相同的資料庫中。The intermediate sort results are stored in the same database as the index.

除了建立索引時使用者資料庫中所需的空間以外,tempdb 還需要大約相同數量的額外空間來容納中繼排序結果。In addition to the space required in the user database to create the index, tempdb must have about the same amount of additional space to hold the intermediate sort results. 如需詳細資訊,請參閱索引的 SORT_IN_TEMPDB 選項For more information, see SORT_IN_TEMPDB Option For Indexes.

在與舊版本相容的語法中,WITH SORT_IN_TEMPDB 相當於 WITH SORT_IN_TEMPDB = ON。In backward compatible syntax, WITH SORT_IN_TEMPDB is equivalent to WITH SORT_IN_TEMPDB = ON.

IGNORE_DUP_KEY = { ON | OFF }IGNORE_DUP_KEY = { ON | OFF }
指定當插入作業嘗試將重複的索引鍵值插入唯一索引時所產生的錯誤回應。Specifies the error response when an insert operation attempts to insert duplicate key values into a unique index. IGNORE_DUP_KEY 選項只適用於在建立或重建索引之後所發生的插入作業。The IGNORE_DUP_KEY option applies only to insert operations after the index is created or rebuilt. 執行 CREATE INDEXALTER INDEXUPDATE 時,這個選項沒有任何作用。The option has no effect when executing CREATE INDEX, ALTER INDEX, or UPDATE. 預設值為 OFF。The default is OFF.

ONON
當重複的索引鍵值插入唯一索引時,就會出現警告訊息。A warning message will occur when duplicate key values are inserted into a unique index. 只有違反唯一性條件約束的資料列才會失敗。Only the rows violating the uniqueness constraint will fail.

OFFOFF
當重複的索引鍵值插入唯一索引時,就會出現錯誤訊息。An error message will occur when duplicate key values are inserted into a unique index. 整個 INSERT 作業將會回復。The entire INSERT operation will be rolled back.

若為針對檢視表所建立的索引、非唯一索引、XML 索引、空間索引和篩選索引,IGNORE_DUP_KEY 不得設為 ON。IGNORE_DUP_KEY cannot be set to ON for indexes created on a view, non-unique indexes, XML indexes, spatial indexes, and filtered indexes.

若要檢視 IGNORE_DUP_KEY,請使用 sys.indexesTo view IGNORE_DUP_KEY, use sys.indexes.

在與舊版本相容的語法中,WITH IGNORE_DUP_KEY 相當於 WITH IGNORE_DUP_KEY = ON。In backward compatible syntax, WITH IGNORE_DUP_KEY is equivalent to WITH IGNORE_DUP_KEY = ON.

STATISTICS_NORECOMPUTE = { ON | OFF}STATISTICS_NORECOMPUTE = { ON | OFF}
指定是否要重新計算散發統計資料。Specifies whether distribution statistics are recomputed. 預設值為 OFF。The default is OFF.

ONON
不會自動重新計算過期的統計資料。Out-of-date statistics are not automatically recomputed.

OFFOFF
啟用自動統計資料更新。Automatic statistics updating are enabled.

若要還原自動統計資料更新,請將 STATISTICS_NORECOMPUTE 設為 OFF,或執行不含 NORECOMPUTE 子句的 UPDATE STATISTICS。To restore automatic statistics updating, set the STATISTICS_NORECOMPUTE to OFF, or execute UPDATE STATISTICS without the NORECOMPUTE clause.

重要

停用散發統計資料的自動重新計算,可防止查詢最佳化工具取得與資料表有關之查詢的最佳執行計畫。Disabling automatic recomputation of distribution statistics may prevent the query optimizer from picking optimal execution plans for queries involving the table.

在與舊版本相容的語法中,WITH STATISTICS_NORECOMPUTE 相當於 WITH STATISTICS_NORECOMPUTE = ON。In backward compatible syntax, WITH STATISTICS_NORECOMPUTE is equivalent to WITH STATISTICS_NORECOMPUTE = ON.

STATISTICS_INCREMENTAL = { ON | OFF }STATISTICS_INCREMENTAL = { ON | OFF }

適用於SQL ServerSQL Server (從 SQL Server 2014 (12.x)SQL Server 2014 (12.x) 開始) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x)) and Azure SQL DatabaseAzure SQL Database

若設定為 ON,所建立的統計資料會以每個資料分割統計資料為依據。When ON, the statistics created are per partition statistics. 若設定為 OFF,則會卸除統計資料樹狀結構,而 SQL ServerSQL Server 會重新計算統計資料。When OFF, the statistics tree is dropped and SQL ServerSQL Server re-computes the statistics. 預設值為 OFFThe default is OFF.

如果不支援每個分割區區的統計資料,則會忽略該選項,並產生警告。If per partition statistics are not supported the option is ignored and a warning is generated. 針對下列統計資料類型,不支援累加統計資料:Incremental stats are not supported for following statistics types:

  • 建立統計資料時,所使用的索引未與基底資料表進行分割區對齊。Statistics created with indexes that are not partition-aligned with the base table.
  • 在 AlwaysOn 可讀取次要資料庫上建立的統計資料。Statistics created on Always On readable secondary databases.
  • 在唯讀資料庫上建立的統計資料。Statistics created on read-only databases.
  • 在篩選的索引上建立的統計資料。Statistics created on filtered indexes.
  • 在檢視上建立的統計資料。Statistics created on views.
  • 在內部資料表上建立的統計資料。Statistics created on internal tables.
  • 使用空間索引或 XML 索引建立的統計資料。Statistics created with spatial indexes or XML indexes.

DROP_EXISTING = { ON | OFF }DROP_EXISTING = { ON | OFF }
這是一個選項,可用來卸除現有的叢集或非叢集索引,並使用已修改的資料行指定值來重建,並會維持相同的索引名稱。Is an option to drop and rebuild the existing clustered or nonclustered index with modified column specifications, and keep the same name for the index. 預設值為 OFF。The default is OFF.

ONON
指定要卸除並重建現有索引,此索引的名稱必須與 index_name 參數相同。Specifies to drop and rebuild the existing index, which must have the same name as the parameter index_name.

OFFOFF
指定不要卸除並重建現有索引。Specifies not to drop and rebuild the existing index. 如果指定的索引名稱已存在,SQL Server 就會顯示錯誤。SQL Server displays an error if the specified index name already exists.

利用 DROP_EXISTING,您可以:With DROP_EXISTING, you can change:

  • 將非叢集資料列存放區索引變更為叢集資料列存放區索引。A nonclustered rowstore index to a clustered rowstore index.

利用 DROP_EXISTING,您無法:With DROP_EXISTING, you cannot change:

  • 將叢集資料列存放區索引變更為非叢集資料列存放區索引。A clustered rowstore index to a nonclustered rowstore index.
  • 將叢集資料行存放區索引變更為任何類型的資料列存放區索引。A clustered columnstore index to any type of rowstore index.

在與舊版本相容的語法中,WITH DROP_EXISTING 相當於 WITH DROP_EXISTING = ON。In backward compatible syntax, WITH DROP_EXISTING is equivalent to WITH DROP_EXISTING = ON.

ONLINE = { ON | OFF }ONLINE = { ON | OFF }
指定在索引作業期間,查詢和資料修改是否能夠使用基礎資料表和相關聯的索引。Specifies whether underlying tables and associated indexes are available for queries and data modification during the index operation. 預設值為 OFF。The default is OFF.

重要

MicrosoftMicrosoftSQL ServerSQL Server 的所有版本都無法使用線上索引作業。Online index operations are not available in every edition of MicrosoftMicrosoftSQL ServerSQL Server. 如需 SQL ServerSQL Server 版本支援的功能清單,請參閱 SQL Server 2016 版本和支援的功能For a list of features that are supported by the editions of SQL ServerSQL Server, see Editions and Supported Features for SQL Server 2016.

ONON
索引作業持續期間不會保留長期資料表鎖定。Long-term table locks are not held for the duration of the index operation. 在索引作業的主要階段期間,來源資料表上只保留意圖共用 (IS) 鎖定。During the main phase of the index operation, only an Intent Share (IS) lock is held on the source table. 這可使基礎資料表和索引的查詢或更新能夠進行。This enables queries or updates to the underlying table and indexes to proceed. 在作業開始時,共用 (S) 鎖定會在來源物件上保留一段很短的時間。At the start of the operation, a Shared (S) lock is held on the source object for a very short period of time. 在作業結束時,如果正在建立非叢集索引,則有一段短時間會在來源上取得 S (共用) 鎖定;或者,當以線上方式建立或卸除叢集索引時,以及正在重建叢集索引或非叢集索引時,則會取得 SCH-M (結構描述修改) 鎖定。At the end of the operation, for a short period of time, an S (Shared) lock is acquired on the source if a nonclustered index is being created; or an SCH-M (Schema Modification) lock is acquired when a clustered index is created or dropped online and when a clustered or nonclustered index is being rebuilt. 建立本機暫存資料表的索引時,ONLINE 不可設為 ON。ONLINE cannot be set to ON when an index is being created on a local temporary table.

OFFOFF
在索引作業期間會套用資料表鎖定。Table locks are applied for the duration of the index operation. 建立、重建或卸除叢集索引的離線索引作業,或重建或卸除非叢集索引的離線索引作業,會取得資料表的結構描述修改 (Sch-M) 鎖定。An offline index operation that creates, rebuilds, or drops a clustered index, or rebuilds or drops a nonclustered index, acquires a Schema modification (Sch-M) lock on the table. 這可防止所有使用者在作業持續期間存取基礎資料表。This prevents all user access to the underlying table for the duration of the operation. 建立非叢集索引的離線索引作業會取得資料表的共用 (S) 鎖定。An offline index operation that creates a nonclustered index acquires a Shared (S) lock on the table. 這可避免對基礎資料表進行更新,但仍可執行讀取作業,如 SELECT 陳述式。This prevents updates to the underlying table but allows read operations, such as SELECT statements.

如需詳細資訊,請參閱 Perform Index Operations OnlineFor more information, see Perform Index Operations Online.

您可以在線上建立索引,其中包括全域暫存資料表的索引,但下列情況除外:Indexes, including indexes on global temp tables, can be created online except for the following cases:

  • XML 索引XML index
  • 本機暫存資料表上的索引Index on a local temp table
  • 在檢視上的初始唯一叢集索引Initial unique clustered index on a view
  • 停用的叢集索引Disabled clustered indexes
  • 資料行存放區索引Columnstore indexes
  • 叢集索引 (如果基礎資料表包含 LOB 資料類型 (imagentexttext) 及空間類型)Clustered index, if the underlying table contains LOB data types (image, ntext, text) and spatial data types
  • varchar(max)varbinary(max) 資料行不得為索引的一部分。varchar(max) and varbinary(max) columns cannot be part of an index. SQL ServerSQL Server (從 SQL Server 2012 (11.x)SQL Server 2012 (11.x) 開始) 和 Azure SQL DatabaseAzure SQL Database 中,當資料表包含 varchar(max)varbinary(max) 資料行時,可以使用 ONLINE 選項來建置或重建包含其他資料行的叢集索引。In SQL ServerSQL Server (Starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x)) and Azure SQL DatabaseAzure SQL Database, when a table contains varchar(max) or varbinary(max) columns, a clustered index containing other columns can be built or rebuilt using the ONLINE option. 當基底資料表包含 varchar(max)varbinary(max) 資料行時,Azure SQL DatabaseAzure SQL Database 不允許 ONLINE 選項Azure SQL DatabaseAzure SQL Database does not permit the ONLINE option when the base table contains varchar(max) or varbinary(max) columns

如需詳細資訊,請參閱線上索引作業如何運作For more information, see How Online Index Operations Work.

RESUMABLE = { ON | OFF}RESUMABLE = { ON | OFF}

適用於SQL ServerSQL Server (從 SQL Server 2019 (15.x)SQL Server 2019 (15.x) 開始) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x)) and Azure SQL DatabaseAzure SQL Database

指定線上索引作業是否為可繼續的作業。Specifies whether an online index operation is resumable.

ONON
索引作業為可繼續的作業。Index operation is resumable.

OFFOFF
索引作業不是可繼續的作業。Index operation is not resumable.

MAX_DURATION = time [MINUTES] 與 RESUMABLE = ON (需要 ONLINE = ON) 搭配使用MAX_DURATION = time [MINUTES] used with RESUMABLE = ON (requires ONLINE = ON)

適用於SQL ServerSQL Server (從 SQL Server 2019 (15.x)SQL Server 2019 (15.x) 開始) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x)) and Azure SQL DatabaseAzure SQL Database

指出可繼續的線上索引作業在暫停之前的執行時間 (以分鐘為單位指定的一個整數值)。Indicates time (an integer value specified in minutes) that a resumable online index operation is executed before being paused.

重要

如需有關可以在線上執行之索引作業的詳細資訊,請參閱線上索引作業的指導方針For more detailed information about index operations that can be performed online, see Guidelines for Online Index Operations.

注意

資料行存放區索引不支援可繼續的線上索引重建。Resumable online index rebuilds are not supported on columnstore indexes.

ALLOW_ROW_LOCKS = { ON | OFF }ALLOW_ROW_LOCKS = { ON | OFF }
適用於SQL ServerSQL Server (SQL Server 2008SQL Server 2008 及更新版本) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 and later) and Azure SQL DatabaseAzure SQL Database

指定是否允許資料列鎖定。Specifies whether row locks are allowed. 預設值是 ON。The default is ON.

ONON
當存取索引時,允許資料列鎖定。Row locks are allowed when accessing the index. Database EngineDatabase Engine 會決定使用資料列鎖定的時機。The Database EngineDatabase Engine determines when row locks are used.

OFFOFF
不使用資料列鎖定。Row locks are not used.

ALLOW_PAGE_LOCKS = { ON | OFF }ALLOW_PAGE_LOCKS = { ON | OFF }
適用於SQL ServerSQL Server (SQL Server 2008SQL Server 2008 及更新版本) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 and later) and Azure SQL DatabaseAzure SQL Database

指定是否允許頁面鎖定。Specifies whether page locks are allowed. 預設值是 ON。The default is ON.

ONON
當存取索引時,允許頁面鎖定。Page locks are allowed when accessing the index. Database EngineDatabase Engine 會決定使用頁面鎖定的時機。The Database EngineDatabase Engine determines when page locks are used.

OFFOFF
不使用頁面鎖定。Page locks are not used.

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }
適用於SQL ServerSQL Server (從 SQL Server 2019 (15.x)SQL Server 2019 (15.x) 開始)Applies to: SQL ServerSQL Server (Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x))

指定是否要最佳化最後一頁的插入競爭。Specifies whether or not to optimize for last-page insert contention. 預設值為 OFF。The default is OFF. 請參閱循序索引鍵一節以取得詳細資訊。See the Sequential Keys section for more information.

MAXDOP = max_degree_of_parallelism MAXDOP = max_degree_of_parallelism
適用於SQL ServerSQL Server (SQL Server 2008SQL Server 2008 及更新版本) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 and later) and Azure SQL DatabaseAzure SQL Database

在索引作業期間,覆寫 max degree of parallelism 設定選項。Overrides the max degree of parallelism configuration option for the duration of the index operation. 如需詳細資訊,請參閱 設定 max degree of parallelism 伺服器組態選項For more information, see Configure the max degree of parallelism Server Configuration Option. 請利用 MAXDOP 來限制執行平行計畫所用的處理器數目。Use MAXDOP to limit the number of processors used in a parallel plan execution. 最大值是 64 個處理器。The maximum is 64 processors.

max_degree_of_parallelism 可以是:max_degree_of_parallelism can be:

11
隱藏平行計畫的產生。Suppresses parallel plan generation.

>1>1
根據目前的系統工作負載,將平行索引作業所使用的處理器數目上限,限制為所指定的數目或更少的數目。Restricts the maximum number of processors used in a parallel index operation to the specified number or fewer based on the current system workload.

0 (預設值)0 (default)
根據目前的系統工作負載,使用實際數目或比實際數目更少的處理器。Uses the actual number of processors or fewer based on the current system workload.

如需詳細資訊,請參閱 設定平行索引作業For more information, see Configure Parallel Index Operations.

注意

MicrosoftMicrosoftSQL ServerSQL Server 的所有版本都無法使用平行索引作業。Parallel index operations are not available in every edition of MicrosoftMicrosoftSQL ServerSQL Server. 如需 SQL ServerSQL Server 版本支援的功能清單,請參閱 SQL Server 2016 的版本及支援功能SQL Server 2017 的版本及支援功能For a list of features that are supported by the editions of SQL ServerSQL Server, see Editions and Supported Features for SQL Server 2016 and Editions and Supported Features for SQL Server 2017.

DATA_COMPRESSIONDATA_COMPRESSION
針對指定的索引、分割區編號或分割區範圍指定資料壓縮選項。Specifies the data compression option for the specified index, partition number, or range of partitions. 選項如下:The options are as follows:

NONE
不壓縮索引或指定的分割區。Index or specified partitions are not compressed.

ROWROW
使用資料列壓縮來壓縮索引或指定的分割區。Index or specified partitions are compressed by using row compression.

PAGEPAGE
使用頁面壓縮來壓縮索引或指定的分割區。Index or specified partitions are compressed by using page compression.

如需與壓縮有關的詳細資訊,請參閱資料壓縮For more information about compression, see Data Compression.

ON PARTITIONS ( { <partition_number_expression> | <range> } [ , ...n ] ) ON PARTITIONS ( { <partition_number_expression> | <range> } [ ,...n ] )
適用於SQL ServerSQL Server (SQL Server 2008SQL Server 2008 及更新版本) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 and later) and Azure SQL DatabaseAzure SQL Database

指定套用 DATA_COMPRESSION 設定的分割區。Specifies the partitions to which the DATA_COMPRESSION setting applies. 如果未分割此索引,ON PARTITIONS 引數將會產生錯誤。If the index is not partitioned, the ON PARTITIONS argument will generate an error. 如果未提供 ON PARTITIONS 子句,DATA_COMPRESSION 選項會套用到分割區索引的所有分割區。If the ON PARTITIONS clause is not provided, the DATA_COMPRESSION option applies to all partitions of a partitioned index.

可以使用以下方式來指定 <partition_number_expression>:<partition_number_expression> can be specified in the following ways:

  • 提供分割區的編號,例如:ON PARTITIONS (2)。Provide the number for a partition, for example: ON PARTITIONS (2).
  • 為數個個別分割區提供以逗號分隔的分割區編號,例如:ON PARTITIONS (1, 5)。Provide the partition numbers for several individual partitions separated by commas, for example: ON PARTITIONS (1, 5).
  • 同時提供範圍和個別分割區,例如:ON PARTITIONS (2, 4, 6 TO 8)。Provide both ranges and individual partitions, for example: ON PARTITIONS (2, 4, 6 TO 8).

<range> 可以指定為以 TO 一字分隔的分割區編號,例如:ON PARTITIONS (6 TO 8)<range> can be specified as partition numbers separated by the word TO, for example: ON PARTITIONS (6 TO 8).

若要為不同的分割區設定不同類型的資料壓縮,請指定 DATA_COMPRESSION 選項一次以上,例如:To set different types of data compression for different partitions, specify the DATA_COMPRESSION option more than once, for example:

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

RemarksRemarks

CREATE INDEX 陳述式的最佳化方式與其他任何查詢一樣。The CREATE INDEX statement is optimized like any other query. 若要儲存在 I/O 作業上,查詢處理器可以選擇掃描另一個索引,而不是執行資料表掃描。To save on I/O operations, the query processor may choose to scan another index instead of performing a table scan. 在某些情況下,排序作業可以省略。The sort operation may be eliminated in some situations. 在多處理器電腦上,CREATE INDEX 可以利用更多的處理器來執行與建立索引相關聯的掃描和排序作業,執行方式與其他查詢的執行方式相同。On multiprocessor computers CREATE INDEX can use more processors to perform the scan and sort operations associated with creating the index, in the same way as other queries do. 如需詳細資訊,請參閱 設定平行索引作業For more information, see Configure Parallel Index Operations.

如果資料庫復原模式設為大量記錄或簡單模式,建立索引作業就可以利用最低限度記錄。The create index operation can be minimally logged if the database recovery model is set to either bulk-logged or simple.

索引可以在暫存資料表上建立。Indexes can be created on a temporary table. 當資料表卸除或工作階段結束時,就會卸除索引。When the table is dropped or the session ends, the indexes are dropped.

建立主索引鍵時,可以在資料表變數上建立叢集索引。A clustered index can be built on a table variable when a Primary Key is created. 當查詢完成或工作階段結束時,也會卸除索引。When the query completes or the session ends, the index is dropped.

索引支援擴充屬性。Indexes support extended properties.

叢集索引Clustered Indexes

若要在資料表 (堆積) 上建立叢集索引,或要卸除及重新建立現有的叢集索引,則資料庫中必須有可用的其他工作空間,才能容納資料排序和原始資料表或現有叢集索引資料的暫存複本。Creating a clustered index on a table (heap) or dropping and re-creating an existing clustered index requires additional workspace to be available in the database to accommodate data sorting and a temporary copy of the original table or existing clustered index data. 如需叢集索引的詳細資訊,請參閱建立叢集索引SQL Server 索引架構和設計指南For more information about clustered indexes, see Create Clustered Indexes and the SQL Server Index Architecture and Design Guide.

非叢集索引Nonclustered Indexes

SQL Server 2016 (13.x)SQL Server 2016 (13.x) 開始以及在 Azure SQL DatabaseAzure SQL Database 中,您可以在儲存為叢集資料行存放區索引的資料表上建立非叢集索引。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) and in Azure SQL DatabaseAzure SQL Database, you can create a nonclustered index on a table stored as a clustered columnstore index. 如果您先在儲存為堆積或叢集索引的資料表上建立非叢集索引,當您稍後將該資料表轉換為叢集資料行存放區索引時,索引將持續保留。If you first create a nonclustered index on a table stored as a heap or clustered index, the index will persist if you later convert the table to a clustered columnstore index. 當您重建叢集資料行存放區索引時,也不需卸除非叢集索引。It is also not necessary to drop the nonclustered index when you rebuild the clustered columnstore index.

限制事項:Limitations and Restrictions:

  • 當您在儲存為叢集資料行存放區索引的資料表上建立非叢集索引時,FILESTREAM_ON 選項無效。The FILESTREAM_ON option is not valid when you create a nonclustered index on a table stored as a clustered columnstore index.

唯一索引Unique Indexes

如果唯一索引存在,每當插入作業新增資料時,Database EngineDatabase Engine 都會確認是否有重複的值。When a unique index exists, the Database EngineDatabase Engine checks for duplicate values each time data is added by a insert operations. 可能產生重複索引鍵值的插入作業會回復,且 Database EngineDatabase Engine 會顯示錯誤訊息。Insert operations that would generate duplicate key values are rolled back, and the Database EngineDatabase Engine displays an error message. 即使插入作業變更多個資料列但只造成一個重複的值,一樣會發生這種情況。This is true even if the insert operation changes many rows but causes only one duplicate. 如果嘗試輸入資料,而該資料有唯一索引,且該資料的 IGNORE_DUP_KEY 子句設為 ON,則只有違反 UNIQUE 索引規則的資料列會失敗。If an attempt is made to enter data for which there is a unique index and the IGNORE_DUP_KEY clause is set to ON, only the rows violating the UNIQUE index fail.

分割區索引Partitioned Indexes

分割區索引與分割區資料表的建立和維護方式類似,不過,跟一般索引一樣,分割區索引會當做個別資料庫物件來處理。Partitioned indexes are created and maintained in a similar manner to partitioned tables, but like ordinary indexes, they are handled as separate database objects. 未進行分割的資料表上可以有分割區索引;已進行分割的資料表上也可以有非分割區索引。You can have a partitioned index on a table that is not partitioned, and you can have a nonpartitioned index on a table that is partitioned.

如果您要在分割區資料表上建立索引,且不指定要從中放置索引的檔案群組,則索引的分割區方式與基礎資料表相同。If you are creating an index on a partitioned table, and do not specify a filegroup on which to place the index, the index is partitioned in the same manner as the underlying table. 這是因為索引及其基礎資料表預設會置於同一個檔案群組內,且索引會供相同分割區配置中,使用相同分割區資料行的分割區資料表使用。This is because indexes, by default, are placed on the same filegroups as their underlying tables, and for a partitioned table in the same partition scheme that uses the same partitioning columns. 當索引使用與資料表相同的資料分割配置及分割資料行時,索引將會與資料表「對齊」 。When the index uses the same partition scheme and partitioning column as the table, the index is aligned with the table.

警告

您可以對包含超過 1,000 個分割區的資料表,建立及重建不以資料表為準的索引,但不予支援。Creating and rebuilding nonaligned indexes on a table with more than 1,000 partitions is possible, but is not supported. 此做法可能會導致在作業期間效能降低或耗用過多記憶體。Doing so may cause degraded performance or excessive memory consumption during these operations. 建議當分割區數超過 1,000 時,一律使用以資料表為準的索引。We recommend using only aligned indexes when the number of partitions exceed 1,000.

分割區不是唯一的叢集索引時若未指定分割區資料行,Database EngineDatabase Engine 預設會將其加入叢集索引鍵清單。When partitioning a non-unique, clustered index, the Database EngineDatabase Engine by default adds any partitioning columns to the list of clustered index keys, if not already specified.

您可以對分割區資料表建立索引檢視表,其方法與建立資料表索引相同。Indexed views can be created on partitioned tables in the same manner as indexes on tables. 如需資料分割索引的詳細資訊,請參閱資料分割資料表和索引SQL Server 索引架構和設計指南For more information about partitioned indexes, see Partitioned Tables and Indexes and the SQL Server Index Architecture and Design Guide.

SQL Server 2019 (15.x)SQL Server 2019 (15.x) 並不會在建立或重建分割區索引之後掃描資料表中所有的資料列建立統計資料。In SQL Server 2019 (15.x)SQL Server 2019 (15.x), statistics are not created by scanning all the rows in the table when a partitioned index is created or rebuilt. 反之,查詢最佳化工具會使用預設的採樣演算法來產生統計資料。Instead, the query optimizer uses the default sampling algorithm to generate statistics. 若要在掃描資料表中所有資料列時取得分割區索引的統計資料,使用子句 FULLSCAN 時請使用 CREATE STATISTICSUPDATE STATISTICSTo obtain statistics on partitioned indexes by scanning all the rows in the table, use CREATE STATISTICS or UPDATE STATISTICS with the FULLSCAN clause.

篩選的索引Filtered Indexes

已篩選的索引是最佳化的非叢集索引,適用於從資料表選取小型資料列百分比的查詢使用。A filtered index is an optimized nonclustered index, suited for queries that select a small percentage of rows from a table. 它會使用篩選述詞,針對資料表中的部分資料建立索引。It uses a filter predicate to index a portion of the data in the table. 設計良好的已篩選索引可以提升查詢效能、降低儲存成本,並減少維護成本。A well-designed filtered index can improve query performance, reduce storage costs, and reduce maintenance costs.

需要已篩選之索引的 SET 選項Required SET Options for Filtered Indexes

每當發生下列任何一個狀況時,都需要必要值資料行中的 SET 選項:The SET options in the Required Value column are required whenever any of the following conditions occur:

  • 建立已篩選的索引。Create a filtered index.

  • INSERT、UPDATE、DELETE 或 MERGE 作業修改已篩選之索引中的資料。INSERT, UPDATE, DELETE, or MERGE operation modifies the data in a filtered index.

  • 查詢最佳化工具會利用篩選索引來產生查詢計劃。The filtered index is used by the query optimizer to produce the query plan.

    Set 選項SET options 必要值Required value 預設伺服器值Default server value 預設Default

    OLE DB 與 ODBC 值OLE DB and ODBC value
    預設Default

    DB-Library 值DB-Library value
    ANSI_NULLSANSI_NULLS ONON ONON ONON OFFOFF
    ANSI_PADDINGANSI_PADDING ONON ONON ONON OFFOFF
    ANSI_WARNINGS*ANSI_WARNINGS* ONON ONON ONON OFFOFF
    ARITHABORTARITHABORT ONON ONON OFFOFF OFFOFF
    CONCAT_NULL_YIELDS_NULLCONCAT_NULL_YIELDS_NULL ONON ONON ONON OFFOFF
    NUMERIC_ROUNDABORTNUMERIC_ROUNDABORT OFFOFF OFFOFF OFFOFF OFFOFF
    QUOTED_IDENTIFIERQUOTED_IDENTIFIER ONON ONON ONON OFFOFF
    • 當資料庫的相容性層級設定為 90 或以上時,將 ANSI_WARNINGS 設定為 ON 也會將 ARITHABORT 隱含設定為 ON。Setting ANSI_WARNINGS to ON implicitly sets ARITHABORT to ON when the database compatibility level is set to 90 or higher. 如果資料庫的相容性層級設定為 80 或更低,ARITHABORT 選項就必須明確地設定為 ON。If the database compatibility level is set to 80 or earlier, the ARITHABORT option must explicitly be set to ON.

當 SET 選項不正確時,可能會發生下列狀況:If the SET options are incorrect, the following conditions can occur:

  • 不會建立已篩選的索引。The filtered index is not created.
  • Database EngineDatabase Engine 會產生錯誤,並回復可變更索引中資料的 INSERT、UPDATE、DELETE 或 MERGE 陳述式。The Database EngineDatabase Engine generates an error and rolls back INSERT, UPDATE, DELETE, or MERGE statements that change data in the index.
  • 查詢最佳化工具不會針對任何 Transact-SQL 陳述式考量執行計畫中的索引。Query optimizer does not consider the index in the execution plan for any Transact-SQL statements.

如需篩選索引的詳細資訊,請參閱建立篩選索引SQL Server 索引架構和設計指南For more information about Filtered Indexes, see Create Filtered Indexes and the SQL Server Index Architecture and Design Guide.

空間索引Spatial Indexes

如需空間索引的資訊,請參閱 CREATE SPATIAL INDEX空間索引概觀For information about spatial indexes, see CREATE SPATIAL INDEX and Spatial Indexes Overview.

XML 索引XML Indexes

如需 XML 索引的資訊,請參閱 CREATE XML INDEXXML 索引 (SQL Server)For information about XML indexes see, CREATE XML INDEX and XML Indexes (SQL Server).

索引鍵大小Index Key Size

叢集索引的索引鍵大小上限為 900 個位元組,而非叢集索引為 1,700 個位元組The maximum size for an index key is 900 bytes for a clustered index and 1,700 bytes for a nonclustered index. (在 SQL DatabaseSQL DatabaseSQL Server 2016 (13.x)SQL Server 2016 (13.x) 之前,限制一律為 900 個位元組)。如果資料行中現有資料未超出建立索引時的限制,則可在 varchar 資料行上建立超過位元組限制的索引;但是,如果後續在資料行進行插入或更新動作時造成總計大小超過限制,則動作會失敗。(Before SQL DatabaseSQL Database and SQL Server 2016 (13.x)SQL Server 2016 (13.x) the limit was always 900 bytes.) Indexes on varchar columns that exceed the byte limit can be created if the existing data in the columns do not exceed the limit at the time the index is created; however, subsequent insert or update actions on the columns that cause the total size to be greater than the limit will fail. 叢集索引的索引鍵所包含的 varchar 資料行不能在 ROW_OVERFLOW_DATA 配置單位中有現有的資料。The index key of a clustered index cannot contain varchar columns that have existing data in the ROW_OVERFLOW_DATA allocation unit. 如果在 varchar 資料行上建立叢集索引,且現有的資料在 IN_ROW_DATA 配置單位中,則後續在可能發送資料非資料列的資料行上進行的插入或更新動作會失敗。If a clustered index is created on a varchar column and the existing data is in the IN_ROW_DATA allocation unit, subsequent insert or update actions on the column that would push the data off-row will fail.

非叢集索引可將非索引鍵資料行併入索引的分葉層級中。Nonclustered indexes can include non-key columns in the leaf level of the index. 在計算索引鍵大小時,Database EngineDatabase Engine 不會考量這些資料行。These columns are not considered by the Database EngineDatabase Engine when calculating the index key size . 如需詳細資訊,請參閱建立內含資料行的索引SQL Server 索引架構和設計指南For more information, see Create Indexes with Included Columns and the SQL Server Index Architecture and Design Guide.

注意

分割資料表時,如果分割區索引鍵資料行原本不存在非唯一的叢集索引中,Database EngineDatabase Engine 就會將它們加入至索引。When tables are partitioned, if the partitioning key columns are not already present in a non-unique clustered index, they are added to the index by the Database EngineDatabase Engine. 在非唯一的叢集索引中,索引資料行 (不計算包含的資料行) 再加上任何加入之分割區資料行的組合大小不得超過 1800 個位元組。The combined size of the indexed columns (not counting included columns), plus any added partitioning columns cannot exceed 1800 bytes in a non-unique clustered index.

計算資料行Computed Columns

索引可以在計算資料行上建立。Indexes can be created on computed columns. 此外,計算資料行可以有 PERSISTED 屬性。In addition, computed columns can have the property PERSISTED. 這表示 Database EngineDatabase Engine 會將計算值儲存在資料表中,並在更新計算資料行所根據的任何其他資料行時更新這些計算值。This means that the Database EngineDatabase Engine stores the computed values in the table, and updates them when any other columns on which the computed column depends are updated. Database EngineDatabase Engine 在資料行上建立某索引,且查詢中參考該索引時,它會使用這些保存值。The Database EngineDatabase Engine uses these persisted values when it creates an index on the column, and when the index is referenced in a query.

若要為計算資料行建立索引,則計算資料行必須具決定性且精確。To index a computed column, the computed column must deterministic and precise. 不過,您可以利用 PERSISTED 屬性,擴充可建立索引的計算資料行類型,使其包括:However, using the PERSISTED property expands the type of indexable computed columns to include:

  • 根據 Transact-SQLTransact-SQL 和 CLR 函數及使用者標示為具決定性的 CLR 使用者定義型別方法所產生的計算資料行。Computed columns based on Transact-SQLTransact-SQL and CLR functions and CLR user-defined type methods that are marked deterministic by the user.
  • 根據具決定性 (如 Database EngineDatabase Engine 所定義) 但不精確的運算式所產生的計算資料行。Computed columns based on expressions that are deterministic as defined by the Database EngineDatabase Engine but imprecise.

保存的計算資料行需要設定前一節篩選索引的必要 SET 選項中所顯示的下列 SET 選項。Persisted computed columns require the following SET options to be set as shown in the previous section Required SET Options for Filtered Indexes.

UNIQUE 或 PRIMARY KEY 條件約束只要符合索引作業的所有條件就可以包含計算資料行。The UNIQUE or PRIMARY KEY constraint can contain a computed column as long as it satisfies all conditions for indexing. 更具體的說法是,計算資料行必須具決定性且精確,或是具決定性且一直保存。Specifically, the computed column must be deterministic and precise or deterministic and persisted. 如需確定性的詳細資訊,請參閱決定性與非決定性函數For more information about determinism, see Deterministic and Nondeterministic Functions.

imagentexttextvarchar(max)nvarchar(max)varbinary(max)xml 資料類型衍生的計算資料行,只要計算資料行資料類型可作為索引鍵資料行或非索引鍵資料行,就可編製索引以作為索引鍵或內含的非索引鍵資料行。Computed columns derived from image, ntext, text, varchar(max), nvarchar(max), varbinary(max), and xml data types can be indexed either as a key or included non-key column as long as the computed column data type is allowable as an index key column or non-key column. 例如,您無法在計算的 xml 資料行上建立主要 XML 索引。For example, you cannot create a primary XML index on a computed xml column. 如果索引鍵大小超過 900 個位元組,畫面上會顯示警告訊息。If the index key size exceeds 900 bytes, a warning message is displayed.

在計算資料行上建立索引,可能會使先前有效的插入或更新作業失敗。Creating an index on a computed column may cause the failure of an insert or update operation that previously worked. 當計算資料行導致算術錯誤時,就可能發生這類失敗。Such a failure may take place when the computed column results in arithmetic error. 例如在下表中,雖然計算資料行 c 導致算術錯誤,但 INSERT 陳述式仍可運作。For example, in the following table, although computed column c results in an arithmetic error, the INSERT statement works.

CREATE TABLE t1 (a INT, b INT, c AS a/b);
INSERT INTO t1 VALUES (1, 0);

但是,如果在建立資料表之後,您在計算資料行 c 上建立索引,相同的 INSERT 陳述式在這種情況下則會失敗。If, instead, after creating the table, you create an index on computed column c, the same INSERT statement will now fail.

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

如需詳細資訊,請參閱 計算資料行的索引For more information, see Indexes on Computed Columns.

將資料行併入索引中Included Columns in Indexes

您可以將非索引鍵資料行 (稱為內含資料行) 加入至非叢集索引的分葉層級,以處理查詢來提升查詢效能。Non-key columns, called included columns, can be added to the leaf level of a nonclustered index to improve query performance by covering the query. 換句話說,查詢中參考的所有資料行都會併入索引中當做索引鍵資料行或非索引鍵資料行。That is, all columns referenced in the query are included in the index as either key or non-key columns. 這可讓查詢最佳化工具從索引掃描中尋找所有的必要資訊;但不存取資料表或叢集索引。This allows the query optimizer to locate all the required information from an index scan; the table or clustered index data is not accessed. 如需詳細資訊,請參閱建立內含資料行的索引SQL Server 索引架構和設計指南For more information, see Create Indexes with Included Columns and the SQL Server Index Architecture and Design Guide.

指定索引選項Specifying Index Options

SQL Server 2005 (9.x)SQL Server 2005 (9.x) 導入新的索引選項,並修改選項的指定方式。introduced new index options and also modifies the way in which options are specified. 在與舊版本相容的語法中,WITH option_name 相當於 WITH ( <option_name> = ON )In backward compatible syntax, WITH option_name is equivalent to WITH ( <option_name> = ON ). 當您設定索引選項時,適用下列規則:When you set index options, the following rules apply:

  • 只能使用 WITH ( option_name = ON | OFF) 來指定新的索引選項。New index options can only be specified by using WITH (option_name = ON | OFF).
  • 不能在相同的陳述式中同時利用與舊版本相容的語法和新語法來指定選項。Options cannot be specified by using both the backward compatible and new syntax in the same statement. 例如,指定 WITH (DROP_EXISTING, ONLINE = ON) 會造成陳述式失敗。For example, specifying WITH (DROP_EXISTING, ONLINE = ON) causes the statement to fail.
  • 當您建立 XML 索引時,必須搭配 WITH ( option_name= ON | OFF) 來指定選項。When you create an XML index, the options must be specified by using WITH (option_name= ON | OFF).

DROP_EXISTING 子句DROP_EXISTING Clause

您可以利用 DROP_EXISTING 子句來重建索引、加入或卸除資料行、修改選項、修改資料行排序次序,或變更分割區配置或檔案群組。You can use the DROP_EXISTING clause to rebuild the index, add or drop columns, modify options, modify column sort order, or change the partition scheme or filegroup.

如果索引強制執行 PRIMARY KEY 或 UNIQUE 條件約束,且索引定義完全沒有變更,則會卸除索引並重新建立索引以保留現有的條件約束。If the index enforces a PRIMARY KEY or UNIQUE constraint and the index definition is not altered in any way, the index is dropped and re-created preserving the existing constraint. 不過,如果索引定義變更了,陳述式就會失敗。However, if the index definition is altered the statement fails. 若要變更 PRIMARY KEY 或 UNIQUE 條件約束的定義,請卸除該條件約束,然後利用新的定義來新增條件約束。To change the definition of a PRIMARY KEY or UNIQUE constraint, drop the constraint and add a constraint with the new definition.

當您在一份也有非叢集索引的資料表上利用一組相同或不同的索引鍵來重新建立叢集索引時,DROP_EXISTING 可以增強效能。DROP_EXISTING enhances performance when you re-create a clustered index, with either the same or different set of keys, on a table that also has nonclustered indexes. DROP_EXISTING 會取代舊叢集索引上之 DROP INDEX 陳述式的執行,然後再針對新叢集索引執行 CREATE INDEX 陳述式。DROP_EXISTING replaces the execution of a DROP INDEX statement on the old clustered index followed by the execution of a CREATE INDEX statement for the new clustered index. 非叢集索引只重建一次,之後,只有在索引定義變更時才會再重建。The nonclustered indexes are rebuilt once, and then only if the index definition has changed. 當索引定義的索引名稱、索引鍵資料行和分割區資料行、唯一性屬性及排序次序與原始索引相同時,DROP_EXISTING 子句不會重建非叢集索引。The DROP_EXISTING clause does not rebuild the nonclustered indexes when the index definition has the same index name, key and partition columns, uniqueness attribute, and sort order as the original index.

不論非叢集索引是否重建,它們一律會保留在它們的原始檔案群組或分割區配置中,且會使用原始的分割區函數。Whether the nonclustered indexes are rebuilt or not, they always remain in their original filegroups or partition schemes and use the original partition functions. 如果將叢集索引重建至不同的檔案群組或分割區配置中,則不會移動非叢集索引來符合叢集索引的新位置。If a clustered index is rebuilt to a different filegroup or partition scheme, the nonclustered indexes are not moved to coincide with the new location of the clustered index. 因此,即使非叢集索引先前與叢集索引對齊,它們也可能不再與叢集索引對齊。Therefore, even the nonclustered indexes previously aligned with the clustered index, they may no longer be aligned with it. 如需資料分割索引對齊的詳細資訊,請參閱資料分割資料表與索引For more information about partitioned index alignment, see Partitioned Tables and Indexes.

除非索引陳述式指定非叢集索引且 ONLINE 選項設為 OFF,否則,如果您以相同的順序使用相同的索引鍵資料行,且相同的索引鍵資料行含有相同的遞增或遞減順序,則 DROP_EXISTING 子句不會再次排序資料。The DROP_EXISTING clause will not sort the data again if the same index key columns are used in the same order and with the same ascending or descending order, unless the index statement specifies a nonclustered index and the ONLINE option is set to OFF. 如果叢集索引已停用,則執行 CREATE INDEX WITH DROP_EXISTING 作業時必須將 ONLINE 設為 OFF。If the clustered index is disabled, the CREATE INDEX WITH DROP_EXISTING operation must be performed with ONLINE set to OFF. 如果非叢集索引已停用,且與停用的叢集索引無關,則執行 CREATE INDEX WITH DROP_EXISTING 作業時可以將 ONLINE 設為 OFF,也可以將它設為 ON。If a nonclustered index is disabled and is not associated with a disabled clustered index, the CREATE INDEX WITH DROP_EXISTING operation can be performed with ONLINE set to OFF or ON.

注意

當卸除或重新建立含有 128 個 (含) 以上之範圍的索引時,Database EngineDatabase Engine 會延遲實際的頁面取消配置及其相關聯的鎖定,直到認可交易之後。When indexes with 128 extents or more are dropped or rebuilt, the Database EngineDatabase Engine defers the actual page deallocations, and their associated locks, until after the transaction commits.

ONLINE 選項ONLINE Option

以線上方式執行索引作業時,適用下列方針:The following guidelines apply for performing index operations online:

  • 當線上索引作業進行中時,不能變更、截斷或卸除基礎資料表。The underlying table cannot be altered, truncated, or dropped while an online index operation is in process.
  • 在索引作業進行期間,需要其他暫存磁碟空間。Additional temporary disk space is required during the index operation.
  • 線上作業可在下列索引上執行:分割區索引,以及包含保存的計算資料行或內含資料行的索引。Online operations can be performed on partitioned indexes and indexes that contain persisted computed columns, or included columns.

如需詳細資訊,請參閱 Perform Index Operations OnlineFor more information, see Perform Index Operations Online.

可繼續的索引作業Resumable index operations

適用於SQL ServerSQL Server (從 SQL Server 2019 (15.x)SQL Server 2019 (15.x) 開始) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x)) and Azure SQL DatabaseAzure SQL Database

可繼續索引作業適用下列方針:The following guidelines apply for resumable index operations:

  • 線上索引建立已使用 RESUMABLE = ON 選項指定為可繼續的作業。Online index create is specified as resumable using the RESUMABLE = ON option.
  • 指定索引的中繼資料中不會保存 RESUMABLE 選項,並且僅適用於目前 DDL 陳述式的持續時間。The RESUMABLE option is not persisted in the metadata for a given index and applies only to the duration of a current DDL statement. 因此,必須明確指定 RESUMABLE = ON 子句,才能啟用可繼續性。Therefore, the RESUMABLE = ON clause must be specified explicitly to enable resumability.
  • 只有 RESUMABLE = ON 選項支援 MAX_DURATION 選項。MAX_DURATION option is only supported for RESUMABLE = ON option.
  • RESUMABLE 選項的 MAX_DURATION 可指定建置索引時的時間間隔。MAX_DURATION for RESUMABLE option specifies the time interval for an index being built. 使用此時間之後,索引建置就會暫停或完成執行。Once this time is used the index build is either paused or it completes its execution. 使用者可決定何時可以繼續已暫停索引的建置。User decides when a build for a paused index can be resumed. MAX_DURATION 的時間是以分鐘計算,且必須大於 0 分鐘,並少於或等於一週 (7 * 24 * 60 = 10080 分鐘)。The time in minutes for MAX_DURATION must be greater than 0 minutes and less or equal one week (7 * 24 * 60 = 10080 minutes). 索引作業長時間暫停可能會影響特定資料表上的 DML 效能,以及影響資料庫磁碟容量,因為原始索引和新建立的索引都需要磁碟空間,且需要在 DML 作業期間更新。Having a long pause for an index operation may impact the DML performance on a specific table as well as the database disk capacity since both indexes the original one and the newly created one require disk space and need to be updated during DML operations. 如果省略 MAX_DURATION 選項,索引作業將會繼續執行直到完成或發生失敗為止。If MAX_DURATION option is omitted, the index operation will continue until its completion or until a failure occurs.
  • 若要立即暫停索引作業,您可以停止 (Ctrl-C) 進行中的命令,執行 ALTER INDEX PAUSE 命令,或執行 KILL <session_id> 命令。To pause immediately the index operation, you can stop (Ctrl-C) the ongoing command, execute the ALTER INDEX PAUSE command, or execute the KILL <session_id> command. 暫停命令之後,可以使用 ALTER INDEX 命令繼續執行該命令。Once the command is paused, it can be resumed using ALTER INDEX command.
  • 重新執行可繼續索引的原始 CREATE INDEX 陳述式,會自動繼續已暫停索引的建立作業。Re-executing the original CREATE INDEX statement for resumable index, automatically resumes a paused index create operation.
  • 可繼續的索引不支援 SORT_IN_TEMPDB = ON 選項。The SORT_IN_TEMPDB = ON option is not supported for resumable index.
  • RESUMABLE = ON 的 DDL 命令無法在明確交易內部執行 (不能是開始 TRAN...COMMIT 區塊的一部份)。The DDL command with RESUMABLE = ON cannot be executed inside an explicit transaction (cannot be part of begin TRAN ... COMMIT block).
  • 若要繼續/中止建立/重建索引,請使用 ALTER INDEX T-SQL 語法To resume/abort an index create/rebuild, use the ALTER INDEX T-SQL syntax

注意

DDL 命令會執行,直到完成、暫停或失敗為止。The DDL command runs until it completes, pauses or fails. 如果命令暫停,將會發出錯誤指出作業已暫停,而且沒有完成索引建立。In case the command pauses, an error will be issued indicating that the operation was paused and that the index creation did not complete. 您可以從 sys.index_resumable_operations 取得目前索引狀態的詳細資訊。More information about the current index status can be obtained from sys.index_resumable_operations. 和以前一樣,如果發生失敗,也會發出錯誤。As before in case of a failure an error will be issued as well.

若要指出索引建立是以可繼續的作業來執行,以及檢查其目前的執行狀態,請參閱 sys.index_resumable_operationsTo indicate that an index create is executed as resumable operation and to check its current execution state, see sys.index_resumable_operations.

資源Resources

下列資源是可繼續線上索引建立操作的必要項目:The following resources are required for resumable online index create operation:

  • 需要額外的空間以保留正在建立的索引,包括索引的暫停時間Additional space required to keep the index being built, including the time when index is being paused
  • 進行排序階段時的額外記錄檔輸送量。Additional log throughput during the sorting phase. 相較於一般線上索引建立,可繼續索引的整體記錄檔空間使用量較小,而且在此作業期間允許記錄截斷。The overall log space usage for resumable index is less compared to regular online index create and allows log truncation during this operation.
  • 可防止進行任何 DDL 修改的 DDL 狀態A DDL state preventing any DDL modification
  • 在作業暫停期間以及作業執行時,內建索引上都會封鎖準刪除清除。Ghost cleanup is blocked on the in-build index for the duration of the operation both while paused and while the operation is running.

目前的功能限制Current functional limitations

下列功能已針對可繼續索引建立操作停用:The following functionality is disabled for resumable index create operations:

  • 在可繼續的線上索引建立作業暫停之後,即無法變更 MAXDOP 的初始值After a resumable online index create operation is paused, the initial value of MAXDOP cannot be changed

  • 建立包含下列項目的索引:Create an index that contains:

    • 作為索引鍵資料行的計算或 TIMESTAMP 資料行Computed or TIMESTAMP column(s) as key columns
    • LOB 資料行作為可繼續索引建立的內含資料行LOB column as included column for resumable index create
    • 已篩選的索引Filtered index

資料列和頁面鎖定選項Row and Page Locks Options

如果 ALLOW_ROW_LOCKS = ONALLOW_PAGE_LOCK = ON,您存取索引時就允許資料列層級、頁面層級和資料表層級的鎖定。When ALLOW_ROW_LOCKS = ON and ALLOW_PAGE_LOCK = ON, row-, page-, and table-level locks are allowed when accessing the index. Database EngineDatabase Engine 會選擇適當的鎖定,且可以將鎖定從資料列或頁面鎖定擴大到資料表鎖定。The Database EngineDatabase Engine chooses the appropriate lock and can escalate the lock from a row or page lock to a table lock.

如果 ALLOW_ROW_LOCKS = OFFALLOW_PAGE_LOCK = OFF,您存取索引時,只允許資料表層級的鎖定。When ALLOW_ROW_LOCKS = OFF and ALLOW_PAGE_LOCK = OFF, only a table-level lock is allowed when accessing the index.

循序索引鍵Sequential Keys

適用於SQL ServerSQL Server (從 SQL Server 2019 (15.x)SQL Server 2019 (15.x) 開始)Applies to: SQL ServerSQL Server (Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x))

最後一頁的插入競爭是大量並行的執行緒在嘗試將資料列插入具備循序索引鍵的索引時,經常發生的一種效能問題。Last-page insert contention is a common performance problem that occurs when a large number of concurrent threads attempt to insert rows into an index with a sequential key. 當開頭的索引鍵資料行包含總是在增加 (或減少) 的值 (例如識別欄位或預設為目前日期/時間的日期) 時,便會將索引視為循序。An index is considered sequential when the leading key column contains values that are always increasing (or decreasing), such as an identity column or a date that defaults to the current date/time. 因為要插入的索引鍵是循序的,所有新的資料列都會在索引結構的結尾插入,也就是位於相同的頁面。Because the keys being inserted are sequential, all new rows will be inserted at the end of the index structure - in other words, on the same page. 這會導致頁面在記憶體中互相競爭,可觀察到的結果就是數個執行緒不明所以地在 PAGELATCH_EX 持續等待頁面。This leads to contention for the page in memory which can be observed as several threads waiting on PAGELATCH_EX for the page in question.

開啟 OPTIMIZE_FOR_SEQUENTIAL_KEY 索引選項,可在資料庫引擎中啟用最佳化,協助改善對索引進行高並行插入時的輸送量。Turning on the OPTIMIZE_FOR_SEQUENTIAL_KEY index option enables an optimization within the database engine that helps improve throughput for high-concurrency inserts into the index. 它適用於因具備循序索引鍵,而可能發生最後一頁插入競爭的索引,但它也可以協助在 B 型樹狀索引結構中其它區域內包含熱點的索引。It is intended for indexes that have a sequential key and thus are prone to last-page insert contention, but it may also help with indexes that have hot spots in other areas of the B-Tree index structure.

檢視索引資訊Viewing Index Information

若要傳回有關索引的資訊,您可以使用目錄檢視、系統函數和系統預存程序。To return information about indexes, you can use catalog views, system functions, and system stored procedures.

資料壓縮Data Compression

資料壓縮主題中會描述資料壓縮。Data compression is described in the topic Data Compression. 以下是考量的幾個要點:The following are key points to consider:

  • 壓縮可讓更多的資料列儲存在頁面上,但是不會變更最大資料列大小。Compression can allow more rows to be stored on a page, but does not change the maximum row size.
  • 索引的非分葉頁面不會進行頁面壓縮,但是可以進行資料列壓縮。Non-leaf pages of an index are not page compressed but can be row compressed.
  • 每一個非叢集索引都有個別的壓縮設定,而且不會繼承基礎資料表的壓縮設定。Each nonclustered index has an individual compression setting, and does not inherit the compression setting of the underlying table.
  • 在堆積上建立叢集索引時,此叢集索引會繼承堆積的壓縮狀態,除非指定了替代的壓縮狀態。When a clustered index is created on a heap, the clustered index inherits the compression state of the heap unless an alternative compression state is specified.

下列限制適用於分割區索引:The following restrictions apply to partitioned indexes:

  • 您無法在資料表具有非對齊索引時變更單一分割區的壓縮設定。You cannot change the compression setting of a single partition if the table has nonaligned indexes.
  • ALTER INDEX <index> ...REBUILD PARTITION ... 語法會重建此索引的指定分割區。The ALTER INDEX <index> ... REBUILD PARTITION ... syntax rebuilds the specified partition of the index.
  • ALTER INDEX <index> ...REBUILD WITH ... 語法會重建此索引的所有分割區。The ALTER INDEX <index> ... REBUILD WITH ... syntax rebuilds all partitions of the index.

若要評估變更壓縮狀態如何影響資料表、索引或分割區,請使用 sp_estimate_data_compression_savings 預存程序。To evaluate how changing the compression state will affect a table, an index, or a partition, use the sp_estimate_data_compression_savings stored procedure.

權限Permissions

必須具備資料表或檢視的 ALTER 權限。Requires ALTER permission on the table or view. 使用者必須是 sysadmin 固定伺服器角色的成員,或是 db_ddladmindb_owner 固定資料庫角色的成員。User must be a member of the sysadmin fixed server role or the db_ddladmin and db_owner fixed database roles.

限制事項Limitations and Restrictions

SQL 資料倉儲SQL Data Warehouse平行處理資料倉儲Parallel Data Warehouse 中,您無法建立:In SQL 資料倉儲SQL Data Warehouse and 平行處理資料倉儲Parallel Data Warehouse, you cannot create:

  • 當資料行存放區索引已存在時,無法在資料倉儲資料表上建立叢集或非叢集資料列存放區索引。A clustered or nonclustered rowstore index on a data warehouse table when a columnstore index already exists. 此行為與 SMP SQL ServerSQL Server 不同,後者可讓資料列存放區和資料行存放區索引共存於相同的資料表上。This behavior is different from SMP SQL ServerSQL Server which allows both rowstore and columnstore indexes to co-exist on the same table.
  • 您無法在檢視上建立索引。You cannot create an index on a view.

中繼資料Metadata

若要檢視現有索引的資訊,您可以查詢 sys.indexes 目錄檢視。To view information on existing indexes, you can query the sys.indexes catalog view.

版本資訊Version Notes

SQL DatabaseSQL Database 不支援 filegroup 和 filestream 選項。does not support filegroup and filestream options.

範例:所有版本。Examples: All versions. 使用 AdventureWorks 資料庫Uses the AdventureWorks database

A.A. 建立簡單的非叢集資料列存放區索引Create a simple nonclustered rowstore index

下列範例會在 Purchasing.ProductVendor 資料表的 VendorID 資料行上建立非叢集索引。The following examples create a nonclustered index on the VendorID column of the Purchasing.ProductVendor table.

CREATE INDEX IX_VendorID ON ProductVendor (VendorID);
CREATE INDEX IX_VendorID ON dbo.ProductVendor (VendorID DESC, Name ASC, Address DESC);
CREATE INDEX IX_VendorID ON Purchasing..ProductVendor (VendorID);

B.B. 建立簡單的非叢集資料列存放區複合式索引Create a simple nonclustered rowstore composite index

下列範例會在 Sales.SalesPerson 資料表的 SalesQuotaSalesYTD 資料行上建立非叢集複合式索引。The following example creates a nonclustered composite index on the SalesQuota and SalesYTD columns of the Sales.SalesPerson table.

CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD ON Sales.SalesPerson (SalesQuota, SalesYTD);

C.C. 在另一個資料庫的資料表上建立索引Create an index on a table in another database

下列範例會在 Purchasing 資料庫中 ProductVendor 資料表的 VendorID 資料行上建立叢集索引。The following example creates a clustered index on the VendorID column of the ProductVendor table in the Purchasing database.

CREATE CLUSTERED INDEX IX_ProductVendor_VendorID ON Purchasing..ProductVendor (VendorID);

D.D. 將資料行加入至索引Add a column to an index

下列範例會使用 dbo.FactFinance 資料表的兩個資料行建立索引 IX_FF。The following example creates index IX_FF with two columns from the dbo.FactFinance table. 下一個陳述式會使用多個資料行重建索引,並保留現有的名稱。The next statement rebuilds the index with one more column and keeps the existing name.

CREATE INDEX IX_FF ON dbo.FactFinance (FinanceKey ASC, DateKey ASC);

-- Rebuild and add the OrganizationKey
CREATE INDEX IX_FF ON dbo.FactFinance (FinanceKey, DateKey, OrganizationKey DESC)
  WITH (DROP_EXISTING = ON);

範例:SQL Server、Azure SQL DatabaseExamples: SQL Server, Azure SQL Database

E.E. 建立唯一的非叢集索引Create a unique nonclustered index

下列範例會在 AdventureWorks2012AdventureWorks2012 資料庫中 Name 資料表的 Production.UnitMeasure 資料行上建立唯一非叢集索引。The following example creates a unique nonclustered index on the Name column of the Production.UnitMeasure table in the AdventureWorks2012AdventureWorks2012 database. 索引會強制將資料上的唯一性插入 Name 資料行中。The index will enforce uniqueness on the data inserted into the Name column.

CREATE UNIQUE INDEX AK_UnitMeasure_Name
  ON Production.UnitMeasure(Name);

下列查詢會藉由嘗試插入與現有資料列具有相同值的資料列,以測試條件約束的唯一性。The following query tests the uniqueness constraint by attempting to insert a row with the same value as that in an existing row.

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

產生的錯誤訊息如下:The resulting error message is:

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.

F.F. 使用 IGNORE_DUP_KEY 選項Use the IGNORE_DUP_KEY option

下列範例分別利用兩種不同的選項設定 (先將選項設為 IGNORE_DUP_KEY,再將選項設為 ON) 將多個資料列插入暫存資料表中,示範 OFF 選項的效果。The following example demonstrates the effect of the IGNORE_DUP_KEY option by inserting multiple rows into a temporary table first with the option set to ON and again with the option set to OFF. 單一資料列會插入 #Test 資料表中,該資料表則會在第二個多重資料列 INSERT 陳述式執行時刻意造成重複的值。A single row is inserted into the #Test table that will intentionally cause a duplicate value when the second multiple-row INSERT statement is executed. 資料表中的資料列計數會傳回所插入的資料列數目。A count of rows in the table returns the number of rows inserted.

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 陳述式的結果。Here are the results of the second INSERT statement.

Server: Msg 3604, Level 16, State 1, Line 5 Duplicate key was ignored.

Number of rows
--------------
38

請注意,從 Production.UnitMeasure 資料表插入之未違反唯一性條件約束的資料列已順利插入。Notice that the rows inserted from the Production.UnitMeasure table that did not violate the uniqueness constraint were successfully inserted. 發出警告且忽略重複的資料列,但不回復整個交易。A warning was issued and the duplicate row ignored, but the entire transaction was not rolled back.

重新執行相同的陳述式,但將 IGNORE_DUP_KEY 設為 OFFThe same statements are executed again, but with IGNORE_DUP_KEY set to 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 陳述式的結果。Here are the results of the second INSERT statement.

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 索引條件約束,皆會導致資料表中所有的資料列無法插入資料表。Notice that none of the rows from the Production.UnitMeasure table were inserted into the table even though only one row in the table violated the UNIQUE index constraint.

G.G. 使用 DROP_EXISTING 卸除及重新建立索引Using DROP_EXISTING to drop and re-create an index

下列範例會利用 ProductID 選項,在 Production.WorkOrder 資料庫中 AdventureWorks2012AdventureWorks2012 資料表的 DROP_EXISTING 資料行上卸除及重新建立現有的索引。The following example drops and re-creates an existing index on the ProductID column of the Production.WorkOrder table in the AdventureWorks2012AdventureWorks2012 database by using the DROP_EXISTING option. 也會設定 FILLFACTORPAD_INDEX 選項。The options FILLFACTOR and PAD_INDEX are also set.

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

H.H. 在檢視表上建立索引Create an index on a view

下列範例會在該檢視表上建立檢視表和索引。The following example creates a view and an index on that view. 內含使用索引檢視的兩項查詢。Two queries are included that use the indexed view.

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

I.I. 使用內含的 (非索引鍵) 資料行建立索引Create an index with included (non-key) columns

下列範例會利用一個索引鍵資料行 (PostalCode) 和四個非索引鍵資料行 (AddressLine1AddressLine2CityStateProvinceID) 來建立非叢集索引。The following example creates a nonclustered index with one key column (PostalCode) and four non-key columns (AddressLine1, AddressLine2, City, StateProvinceID). 其後有一個由索引處理的查詢。A query that is covered by the index follows. 若要顯示查詢最佳化工具所選取的索引,請先在 SQL Server Management StudioSQL Server Management Studio 的 [查詢] 功能表上選取 [Display Actual Execution Plan] (顯示實際執行計畫),然後再執行查詢。To display the index that is selected by the query optimizer, on the Query menu in SQL Server Management StudioSQL Server Management Studio, select Display Actual Execution Plan before executing the query.

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

J.J. 建立資料分割索引Create a partitioned index

下列範例會在 AdventureWorks2012AdventureWorks2012 資料庫中現有的分割區配置 TransactionsPS1 上建立非叢集分割區索引。The following example creates a nonclustered partitioned index on TransactionsPS1, an existing partition scheme in the AdventureWorks2012AdventureWorks2012 database. 此範例假設您已安裝分割區索引範例。This example assumes the partitioned index sample has been installed.

適用於SQL ServerSQL Server (SQL Server 2008SQL Server 2008 及更新版本) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 and later) and Azure SQL DatabaseAzure SQL Database

CREATE NONCLUSTERED INDEX IX_TransactionHistory_ReferenceOrderID
  ON Production.TransactionHistory (ReferenceOrderID)
  ON TransactionsPS1 (TransactionDate);
GO

K.K. 建立篩選的索引Creating a filtered index

下列範例會對 AdventureWorks2012AdventureWorks2012 資料庫中的 Production.BillOfMaterials 資料表建立篩選的索引。The following example creates a filtered index on the Production.BillOfMaterials table in the AdventureWorks2012AdventureWorks2012 database. 篩選述詞可以包含在已篩選之索引中不是索引鍵資料行的資料行。The filter predicate can include columns that are not key columns in the filtered index. 此範例中的述詞只會選取 EndDate 不是 NULL 的資料列。The predicate in this example selects only the rows where EndDate is non-NULL.

CREATE NONCLUSTERED INDEX "FIBillOfMaterialsWithEndDate"
  ON Production.BillOfMaterials (ComponentID, StartDate)
  WHERE EndDate IS NOT NULL;

L.L. 建立壓縮索引Create a compressed index

下列範例會使用資料列壓縮,在非分割區資料表上建立索引。The following example creates an index on a nonpartitioned table by using row compression.

CREATE NONCLUSTERED INDEX IX_INDEX_1
  ON T1 (C2)
  WITH (DATA_COMPRESSION = ROW);
GO

下列範例會在索引的所有分割區上使用資料列壓縮,以便在分割區資料表上建立索引。The following example creates an index on a partitioned table by using row compression on all partitions of the index.

CREATE CLUSTERED INDEX IX_PartTab2Col1
  ON PartitionTable1 (Col1)
  WITH (DATA_COMPRESSION = ROW);
GO

下列範例會在分割區資料表上建立索引,其方式是在索引的分割區 1 上使用頁面壓縮,並在索引的分割區 24 上使用資料列壓縮。The following example creates an index on a partitioned table by using page compression on partition 1 of the index and row compression on partitions 2 through 4 of the index.

CREATE CLUSTERED INDEX IX_PartTab2Col1
  ON PartitionTable1 (Col1)
  WITH (
    DATA_COMPRESSION = PAGE ON PARTITIONS(1),
    DATA_COMPRESSION = ROW ON PARTITIONS (2 TO 4)
  );
GO

M.M. 建立、繼續、暫停及中止可繼續的索引作業Create, resume, pause, and abort resumable index operations

適用於SQL ServerSQL Server (從 SQL Server 2019 (15.x)SQL Server 2019 (15.x) 開始) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x)) and Azure SQL DatabaseAzure SQL Database

-- Execute a resumable online index create statement with MAXDOP=1
CREATE INDEX test_idx1 ON test_table (col1) WITH (ONLINE = ON, MAXDOP = 1, RESUMABLE = ON);

-- Executing the same command again (see above) after an index operation was paused, resumes automatically the index create operation.

-- Execute a resumable online index creates operation with MAX_DURATION set to 240 minutes. After the time expires, the resumable index create operation is paused.
CREATE INDEX test_idx2 ON test_table (col2) WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 240);

-- Pause a running resumable online index creation
ALTER INDEX test_idx1 ON test_table PAUSE;
ALTER INDEX test_idx2 ON test_table PAUSE;

-- Resume a paused online index creation
ALTER INDEX test_idx1 ON test_table RESUME;
ALTER INDEX test_idx2 ON test_table RESUME;

-- Abort resumable index create operation which is running or paused
ALTER INDEX test_idx1 ON test_table ABORT;
ALTER INDEX test_idx2 ON test_table ABORT;

範例:Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW)平行處理資料倉儲Parallel Data WarehouseExamples: Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW) and 平行處理資料倉儲Parallel Data Warehouse

N.N. 基本語法Basic syntax

建立、繼續、暫停及中止可繼續的索引作業Create, resume, pause, and abort resumable index operations

適用於SQL ServerSQL Server (從 SQL Server 2019 (15.x)SQL Server 2019 (15.x) 開始) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x)) and Azure SQL DatabaseAzure SQL Database

-- Execute a resumable online index create statement with MAXDOP=1
CREATE INDEX test_idx ON test_table WITH (ONLINE = ON, MAXDOP = 1, RESUMABLE = ON);

-- Executing the same command again (see above) after an index operation was paused, resumes automatically the index create operation.

-- Execute a resumable online index creates operation with MAX_DURATION set to 240 minutes. After the time expires, the resumable index create operation is paused.
CREATE INDEX test_idx ON test_table WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 240);

-- Pause a running resumable online index creation
ALTER INDEX test_idx ON test_table PAUSE;

-- Resume a paused online index creation
ALTER INDEX test_idx ON test_table RESUME;

-- Abort resumable index create operation which is running or paused
ALTER INDEX test_idx ON test_table ABORT;

O.O. 在目前資料庫的資料表上建立非叢集索引Create a nonclustered index on a table in the current database

下列範例會在 VendorID資料表的 ProductVendor 資料行上建立非叢集索引。The following example creates a nonclustered index on the VendorID column of the ProductVendor table.

CREATE INDEX IX_ProductVendor_VendorID
  ON ProductVendor (VendorID);

P.P. 在另一個資料庫的資料表上建立叢集索引Create a clustered index on a table in another database

下列範例會在 VendorID 資料庫中 ProductVendor 資料表的 Purchasing 資料行上建立非叢集索引。The following example creates a nonclustered index on the VendorID column of the ProductVendor table in the Purchasing database.

CREATE CLUSTERED INDEX IX_ProductVendor_VendorID
  ON Purchasing..ProductVendor (VendorID);

Q.Q. 在資料表上建立已排序的叢集索引Create an ordered clustered index on a table

下列範例會在 MyDB 資料庫中 T1 資料表的 c1c2 資料行上建立已排序叢集索引。The following example creates an ordered clustered index on the c1 and c2 columns of the T1 table in the MyDB database.

CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON MyDB.dbo.T1 
ORDER (c1, c2);

R.R. 在資料表上將 CCI 轉換成已排序的叢集索引Convert a CCI to an ordered clustered index on a table

下列範例會將現有叢集資料行存放區索引轉換成已排序的叢集資料行存放區索引,其稱為 MyOrderedCCI (位於 MyDB 資料庫中 T2 資料表的 c1c2 資料行)。The following example convert the existing clustered columnstore index to an ordered clustered columnstore index called MyOrderedCCI on the c1 and c2 columns of the T2 table in the MyDB database.

CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON MyDB.dbo.T2
ORDER (c1, c2)
WITH (DROP_EXISTING = ON);

另請參閱See Also

SQL Server 索引架構和設計指南 SQL Server Index Architecture and Design Guide
線上執行索引作業Perform Index Operations Online
索引和 ALTER TABLE Indexes and ALTER TABLE
ALTER INDEX ALTER INDEX
CREATE PARTITION FUNCTION CREATE PARTITION FUNCTION
CREATE PARTITION SCHEME CREATE PARTITION SCHEME
CREATE SPATIAL INDEX CREATE SPATIAL INDEX
CREATE STATISTICS CREATE STATISTICS
CREATE TABLE CREATE TABLE
CREATE XML INDEX CREATE XML INDEX
資料類型 Data Types
DBCC SHOW_STATISTICS DBCC SHOW_STATISTICS
DROP INDEX DROP INDEX
XML 索引 (SQL Server) XML Indexes (SQL Server)
sys.indexes sys.indexes
sys.index_columns sys.index_columns
sys.xml_indexes sys.xml_indexes
EVENTDATAEVENTDATA