Share via

ALTER INDEX (Transact-SQL)

  • 發行項

藉由停用、重建或重新組織索引或設定索引選項，修改現有的資料表或檢視表索引 (關聯式或 XML)。

適用於：SQL Server (SQL Server 2008 到目前的版本)、Azure SQL Database。

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

語法

-- SQL Server Syntax

ALTER INDEX { index_name | ALL }
    ON <object>
    { REBUILD
        [ PARTITION = ALL ]
        [ WITH ( <rebuild_index_option> [ ,...n ] ) ] 
        | [ PARTITION = partition_number 
              [ WITH ( <single_partition_rebuild_index_option> ) [ ,...n ] ]
          ]  
    | DISABLE
    | REORGANIZE 
        [ PARTITION = partition_number ]
        [ WITH ( LOB_COMPACTION = { ON | OFF } ) ]
    | SET ( <set_index_option> [ ,...n ] ) 
    }
[ ; ]

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

<rebuild_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 }
  | ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF } 
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE } 
     [ ON PARTITIONS ( { <partition_number_expression> | <range> } 
     [ , ...n ] ) ]
}
<range> ::= 
<partition_number_expression> TO <partition_number_expression>

<single_partition_rebuild_index_option> ::=
{
    SORT_IN_TEMPDB = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE} }
  | ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF }
}

<set_index_option>::=
{
    ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
}

<low_priority_lock_wait>::=
{
    WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time> [ MINUTES ] , 
                           ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } )
}

-- Windows Azure SQL Database Syntax

ALTER INDEX { index_name | ALL }
    ON <object>
    { REBUILD 
        [
          [ WITH ( <rebuild_index_option> [ ,...n ] ) ] 

        ] 
    | DISABLE
    | SET ( <set_index_option> [ ,...n ] ) 
    }
 [ ; ] 

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

<rebuild_index_option > ::= 
{
   IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | ONLINE = { ON | OFF } 
}

<set_index_option>::=
{
   IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
}

引數

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

  • ALL
    指定與資料表或檢視表相關聯的所有索引 (不論索引類型為何)。 如果有一個或多個索引在離線或唯讀檔案群組中，或有一個或多個索引類型不允許指定的作業，指定 ALL 便會使陳述式失敗。 下表列出索引作業和不允許的索引類型。

    這項作業指定 ALL

    如果資料表有一個或多個下列項目，便告失敗

    REBUILD WITH ONLINE = ON

    XML 索引

    空間索引

    資料行存放區索引

    適用於： SQL Server 2012 到 SQL Server 2014。

    REBUILD PARTITION = partition_number

    未分割索引、XML 索引、空間索引或停用的索引

    REORGANIZE

    ALLOW_PAGE_LOCKS 設定為 OFF 的索引

    REORGANIZE PARTITION = partition_number

    未分割索引、XML 索引、空間索引或停用的索引

    IGNORE_DUP_KEY = ON

    XML 索引

    空間索引

    資料行存放區索引

    適用於： SQL Server 2012 到 SQL Server 2014。

    ONLINE = ON

    XML 索引

    空間索引

    資料行存放區索引

    適用於： SQL Server 2012 到 SQL Server 2014。

    警告

    如需有關可以在線上執行之索引作業的詳細資訊，請參閱＜線上索引作業的指導方針＞。

    如果設定 PARTITION = partition_number 來指定 ALL，便會對齊所有索引。 這表示它們會根據對等的分割區函數來進行分割。 搭配 PARTITION 子句來使用 ALL 時，會重建或重新組織含有相同 partition_number 的所有索引分割區。 如需有關分割區索引的詳細資訊，請參閱＜分割資料表與索引＞。

  • database_name
    這是資料庫的名稱。

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

  • table_or_view_name
    這是與索引相關聯的資料表或檢視表的名稱。 若要顯示物件的索引報表，請使用 sys.indexes 目錄檢視。

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

  • REBUILD [ WITH (<rebuild_index_option> [ ,...n]) ]
    指定將利用相同的資料行、索引類型、唯一性屬性和排序次序來重建索引。 如果是資料行存放區索引，排序次序不適用。 這個子句相當於 DBCC DBREINDEX。 REBUILD 會啟用停用的索引。 除非指定了 ALL 關鍵字，否則重建叢集索引不會重建相關聯的非叢集索引。 如果未指定索引選項，便會套用儲存在 sys.indexes 中的現有索引選項值。 任何值未儲存在 sys.indexes 中的索引選項，都會套用選項引數定義中所指示的預設值。

    如果指定了 ALL，且基礎資料表是堆積，重建作業便不會影響資料表。 與資料表相關聯的任何非叢集索引都會重建。

    如果資料庫復原模式設為大量記錄模式或簡單模式，重建作業便可以只進行最基本的記錄。

    注意

    當您重建主要 XML 索引時，在索引作業的持續時間，無法使用基礎使用者資料表。

  • PARTITION

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

    指定只重建或重新組織索引的一個分割區。 如果 index_name 不是分割區索引，便不能指定 PARTITION。

    PARTITION = ALL 會重建所有分割區。

    警告

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

  • partition_number

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

    要重建或重新組織之分割區索引的分割區數。 partition_number 是一個可以參考變數的常數運算式。 其中包括使用者定義類型變數或函數及使用者定義函數，但無法參考 Transact-SQL 陳述式。 partition_number 必須存在，否則陳述式將會失敗。

  • WITH (<single_partition_rebuild_index_option>)

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

    SORT_IN_TEMPDB、MAXDOP 和 DATA_COMPRESSION 是重建單一分割區 (PARTITION = n) 時所能指定的選項。 不能在單一分割區重建作業中指定 XML 索引。

  • DISABLE
    將索引標示為已停用，無法供 Database Engine 使用。 任何索引都可以停用。 已停用之索引的索引定義會保留在系統目錄中，但不含基礎索引資料。 停用叢集索引可以防止使用者存取基礎資料表資料。 若要啟用索引，請使用 ALTER INDEX REBUILD 或 CREATE INDEX WITH DROP_EXISTING。 如需詳細資訊，請參閱＜停用索引和條件約束＞和＜啟用索引與條件約束＞。

  • REORGANIZE
    指定將重新組織索引分葉層級。 如果是叢集資料行存放區索引，則指定所有 CLOSED 資料列群組將會移到資料行存放區。 ALTER INDEX REORGANIZE 陳述式一律是在線上執行。 這表示不會保留長期封鎖的資料表鎖定，而且在 ALTER INDEX REORGANIZE 交易期間，可以繼續查詢或更新基礎資料表。 停用的索引或 ALLOW_PAGE_LOCKS 設為 OFF 的索引不能指定 REORGANIZE。 若回復交易，則在交易中執行的 REORGANIZE 不會回復。

  • WITH ( LOB_COMPACTION = { ON | OFF } )

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

    指定壓縮包含大型物件 (LOB) 資料的所有頁面。 LOB 資料類型有 image、text、ntext、varchar(max)、nvarchar(max)、varbinary(max) 和 xml。 壓縮這項資料可以改善磁碟空間的使用情況。 預設值是 ON。

    • ON
      壓縮包含大型物件資料的所有頁面。

      重新組織指定的叢集索引會壓縮叢集索引所包含的所有 LOB 資料行。

      重新組織非叢集索引會壓縮索引中本身是非索引鍵資料行 (內含資料行) 的所有 LOB 資料行。當指定 ALL 時，會重新組織與指定之資料表或檢視表相關聯的所有索引，且會壓縮與叢集索引、基礎資料表或具有內含資料行之非叢集索引相關聯的所有 LOB 資料行。

    • OFF
      不壓縮包含大型物件資料的頁面。

      OFF 對堆積沒有作用。

    如果 LOB 資料行不存在，便會忽略 LOB_COMPACTION 子句。

  • SET ( <set_index option> [ ,...n] )
    在不重建或重新組織索引的情況下，指定索引選項。 停用的索引不能指定 SET。

  • PAD_INDEX = { ON | OFF }

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

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

    • ON
      FILLFACTOR 指定的可用空間百分比會套用到索引的中繼層級頁面上。 如果 PAD_INDEX 設為 ON 時，並未指定 FILLFACTOR，就會使用 sys.indexes 中所儲存的填滿因數值。

    • OFF 或未指定 fillfactor
      填入中繼層級頁面至接近填滿的程度。 這會保留至少足以容納一個資料列的空間，且該資料列具有索引所能擁有的大小上限 (以中繼頁面的索引鍵組為基礎)。

    如需詳細資訊，請參閱＜CREATE INDEX (Transact-SQL)＞。

  • FILLFACTOR = fillfactor

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

    指定一個百分比來指出在建立或改變索引期間，Database Engine 應該使各索引頁面之分葉層級填滿的程度。 fillfactor 必須是 1 到 100 之間的整數值。 預設值是 0。 填滿因數值 0 和 100 在各方面都是一樣的。

    只有在最初建立或重建索引時，才適用明確的 FILLFACTOR 設定。 Database Engine 不會動態保留頁面中空白空間的指定百分比。 如需詳細資訊，請參閱＜CREATE INDEX (Transact-SQL)＞。

    若要檢視填滿因數設定，請使用 sys.indexes。

    重要事項重要事項

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

  • SORT_IN_TEMPDB = { ON | OFF }

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

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

    • ON
      用來建立索引的中繼排序結果會儲存在 tempdb 中。 如果 tempdb 是在使用者資料庫以外的磁碟組中，這可能會縮短建立索引所需要的時間。 不過，這會增加建立索引時所使用的磁碟空間量。

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

    如果不需要排序作業，或者可以在記憶體中執行排序，則忽略 SORT_IN_TEMPDB 選項。

    如需詳細資訊，請參閱＜索引的 SORT_IN_TEMPDB 選項＞。

  • IGNORE_DUP_KEY = { ON | OFF }
    指定當插入作業嘗試將重複的索引鍵值插入唯一索引時所產生的錯誤回應。 IGNORE_DUP_KEY 選項只適用於在建立或重建索引之後所發生的插入作業。 預設值為 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。

    重要事項重要事項

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

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

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

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

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

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

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

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

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

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

    適用於：SQL Server 2014 到 SQL Server 2014。

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

    如果是 XML 索引或空間索引，則只支援 ONLINE = OFF，而如果將 ONLINE 設定為 ON，將會引發錯誤。

    注意

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

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

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

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

    您可以在線上重建索引，其中包括全域暫存資料表的索引，但下列情況除外：

    • XML 索引

    • 本機暫存資料表的索引

    • 分割區索引的子集 (可以在線上重建整個分割區索引)。

  • ALLOW_ROW_LOCKS = { ON | OFF }

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

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

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

    • OFF
      不使用資料列鎖定。

  • ALLOW_PAGE_LOCKS = { ON | OFF }

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

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

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

    • OFF
      不使用頁面鎖定。

    注意

    當 ALLOW_PAGE_LOCKS 設為 OFF 時，無法重新組織索引。

  • MAXDOP **=**max_degree_of_parallelism

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

    在索引作業期間，覆寫 max degree of parallelism 組態選項。 如需詳細資訊，請參閱＜設定 max degree of parallelism 伺服器組態選項＞。 請利用 MAXDOP 來限制執行平行計畫所用的處理器數目。 最大值是 64 個處理器。

    重要事項重要事項

    雖然所有 XML 索引在語法上都支援 MAXDOP 選項，但是對於空間索引或主要 XML 索引而言，ALTER INDEX 目前只會使用單一處理器。

    max_degree_of_parallelism 可以是：

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

    • >1
      將平行索引作業所用的最大處理器數目限制為指定的數目。

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

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

    注意

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

  • DATA_COMPRESSION

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

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

    • NONE
      不壓縮索引或指定的分割區。 這不適用於資料行存放區索引。

    • ROW
      使用資料列壓縮來壓縮索引或指定的分割區。 這不適用於資料行存放區索引。

    • PAGE
      使用頁面壓縮來壓縮索引或指定的分割區。 這不適用於資料行存放區索引。

    • COLUMNSTORE

      適用於：SQL Server 2014 到 SQL Server 2014。

      只適用於資料行存放區索引，包括非叢集資料行存放區索引和叢集資料行存放區索引。 COLUMNSTORE 會指定解壓縮以 COLUMNSTORE_ARCHIVE 選項壓縮的索引或指定的分割區。 當還原資料時，資料將會繼續以用於所有資料行存放區索引的資料行存放區壓縮來壓縮。

    • COLUMNSTORE_ARCHIVE

      適用於：SQL Server 2014 到 SQL Server 2014。

      只適用於資料行存放區索引，包括非叢集資料行存放區索引和叢集資料行存放區索引。 COLUMNSTORE_ARCHIVE 將進一步將指定的分割區壓縮成較小的大小。 這可用於封存，或是其他需要較小儲存體，而且可負擔更多時間來儲存和擷取的狀況。

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

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

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

    指定套用 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)
);

  • ONLINE = { ON | OFF } <在套用至 single_partition_rebuild_index_option 時>
    指定基礎資料表的索引或索引分割區可以在線上重建或離線重建。 如果 REBUILD 是在線上執行 (ON)，這個資料表中的資料可以在索引作業期間用於查詢和資料修改。 預設值是 OFF。

    • ON
      索引作業持續期間不會保留長期資料表鎖定。 在索引作業的主要階段期間，來源資料表上只保留意圖共用 (IS) 鎖定。 在索引重建開始時，資料表上需要 S 鎖定，而在線上索引重建結束時，資料表上需要 Sch-M 鎖定。 雖然這兩個鎖定是短暫的中繼資料鎖定，尤其是 Sch-M 鎖定必須等候所有封鎖交易完成。 在等候期間，Sch-M 鎖定會在存取相同資料表時，封鎖等待在這個鎖定之後的所有其他交易。

      注意

      線上索引重建可設定 low_priority_lock_wait 選項，如本節稍後所述。

    • OFF
      在索引作業期間會套用資料表鎖定。 這可防止所有使用者在作業持續期間存取基礎資料表。

  • WAIT_AT_LOW_PRIORITY

    適用於：SQL Server 2014 到 SQL Server 2014。

    線上索引重建必須等候這個資料表的封鎖作業。 WAIT_AT_LOW_PRIORITY 表示線上索引重建作業將會等候低優先權鎖定，讓其他作業在線上索引建立作業等候時繼續進行。 省略 WAIT AT LOW PRIORITY 選項相當於 WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE)。

  • MAX_DURATION = time [MINUTES ]

    適用於：SQL Server 2014 到 SQL Server 2014。

    執行 DDL 命令時，線上索引重建鎖定將會以低優先權等候的等候時間 (以分鐘為單位指定的整數值)。 如果作業在 MAX_DURATION 這段時間遭封鎖，則會執行其中一個 ABORT_AFTER_WAIT 動作。 MAX_DURATION 時間一律以分鐘為單位，而且可以省略 MINUTES 這個字。

  • ABORT_AFTER_WAIT = [NONE | SELF | BLOCKERS } ]

    適用於：SQL Server 2014 到 SQL Server 2014。

    • NONE
      繼續等候一般 (標準) 優先權的鎖定。

    • SELF
      結束目前正在執行的線上索引重建 DDL 作業，但不採取任何動作。

    • BLOCKERS
      終止封鎖線上索引重建 DDL 作業的所有使用者交易，讓作業可以繼續。 BLOCKERS 選項需要登入具有 ALTER ANY CONNECTION 權限。

備註

ALTER INDEX 無法用來重新進行索引的分割區，或將它移到另一個檔案群組。 您不能利用這個陳述式來修改索引定義，例如新增或刪除資料行，或變更資料行順序。 請搭配 DROP_EXISTING 子句來使用 CREATE INDEX，以執行這些作業。

未明確指定選項時，會套用目前的設定。 例如，如果 REBUILD 子句並未指定 FILLFACTOR 設定，在重建過程中，會使用系統目錄中所儲存的填滿因數值。 若要檢視目前的索引選項設定，請使用 sys.indexes。

注意

ONLINE、MAXDOP 和 SORT_IN_TEMPDB 的值並未儲存在系統目錄中。除非索引陳述式中另有指定，否則會使用選項的預設值。

在多重處理器的電腦上，ALTER INDEX REBUILD 也如同其他查詢一樣，會利用更多處理器來執行與修改索引相關的掃描和排序作業。 當您執行 ALTER INDEX REORGANIZE 時，不論是否設定了 LOB_COMPACTION，max degree of parallelism 值都是單一執行緒作業。 如需詳細資訊，請參閱＜設定平行索引作業＞。

如果索引所在的檔案群組離線或設為唯讀，便無法重新組織或重建索引。 當指定了 ALL 關鍵字，且有一個或多個索引在離線或唯讀檔案群組中，陳述式會失敗。

重建索引

重建索引會卸除和重新建立索引。 這會移除片段；根據指定的或現有的填滿因數設定壓縮頁面來收回磁碟空間，以及重新排序連續頁面中的索引資料列。 當指定 ALL 時，會在單一交易中卸除和重建資料表的所有索引。 不需要事先卸除 FOREIGN KEY 條件約束。 當重建含有 128 個或更多範圍的索引時，Database Engine 會延遲取消配置實際的頁面，也會延遲其關聯鎖定，直到認可交易之後。

重建或重新組織小型索引通常不會減少片段。 小型索引的頁面會儲存在混合範圍上， 混合範圍最多可由八個物件所共用，所以當重新組織或重建索引之後，小型索引中的片段可能不會減少。

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

在舊版的 SQL Server 中，有時候您可以重建非叢集索引來更正硬體故障所造成的任何不一致情況。 在 SQL Server 2008 和更新版本中，您仍可能離線重建非叢集索引來修復索引和叢集索引之間的這類不一致的情況。 不過，您無法利用線上重建索引的方式來修復非叢集索引不一致的情況，因為線上重建機制會以現有的非叢集索引做為重建基礎而保存不一致的情況。 相反地，離線重建索引會強制進行叢集索引 (或堆積) 掃描，藉此移除不一致的情況。 如果要從不一致的情況中復原，在舊版中，我們建議的方法是從備份中還原受影響的資料，不過，您現在可以利用離線重建非叢集索引的方式來修復索引不一致的情況。 如需詳細資訊，請參閱＜DBCC CHECKDB (Transact-SQL)＞。

為了重建叢集資料行存放區索引，SQL Server 會進行以下作業：

  1. 在進行重建時，取得資料表或分割區上的獨佔鎖定。 在重建期間，資料處於「離線」狀態而且無法使用。

  2. 藉由實際刪除已經以邏輯方式從資料表刪除的資料列，以重組資料行存放區，而已刪除的位元組會在實體媒體上回收。

  3. 從原始資料行存放區索引讀取所有資料，包括差異存放區。 這會將資料合併成新的資料列群組，並將資料列群組壓縮到資料行存放區。

  4. 進行重建時，實體媒體上需要有空間可儲存資料行存放區索引的兩份副本。 當重建完成時，SQL Server 會刪除原始叢集資料行存放區索引。

重新組織索引

重新組織索引所用的系統資源最少。 它會實際重新排序分葉層級的頁面，使它們由左至右符合分葉節點的邏輯順序，以重新組織資料表和檢視表之叢集和非叢集索引的分葉層級。 重新組織也會壓縮索引頁面。 壓縮是以現有填滿因數值為基礎。 若要檢視填滿因數設定，請使用 sys.indexes。

當指定 ALL 時，會重新組織資料表的叢集和非叢集關聯式索引及 XML 索引。 當指定 ALL 時，適用某些限制，請參閱＜引數＞一節中的 ALL 定義。

為了重新組織叢集資料行存放區索引，SQL Server 會將標示為 CLOSED 的所有資料列群組移到資料行存放區。將 CLOSED 資料列群組移到資料行存放區並不需要重新組織。 Tuple 移動處理序最後會找到所有 CLOSED 資料列群組並加以移動。 不過，Tuple 移動是單一執行緒，而且移動資料列群組的速度對於您的工作負載可能不夠快。 為了確保資料列群組在關閉之後會移動，您可以在每次載入之後執行 ALTER INDEX REORGANIZE。

如需詳細資訊，請參閱＜重新組織與重建索引＞。

停用索引

停用索引可防止使用者存取索引，如果是叢集索引，則可防止使用者存取基礎資料表的資料。 索引定義會保留在系統目錄中。 停用檢視的非叢集索引或叢集索引時，會實際刪除索引資料。 停用叢集索引可防止存取資料，資料仍會保留在 B 型樹狀目錄中，但不進行維護，直到卸除或重建索引為止。 若要檢視已啟用或停用之索引的狀態，請查詢 sys.indexes 目錄檢視中的 is_disabled 資料行。

如果資料表在異動複寫發行集中，您便無法停用關聯於主索引鍵資料行的任何索引。 複寫需要這些索引。 若要停用索引，您必須先從發行集中卸除資料表。 如需詳細資訊，請參閱＜發行資料和資料庫物件＞。

請利用 ALTER INDEX REBUILD 陳述式或 CREATE INDEX WITH DROP_EXISTING 陳述式來啟用索引。 當 ONLINE 選項設為 ON 時，無法重建停用的叢集索引。 如需詳細資訊，請參閱＜停用索引和條件約束＞。

設定選項

您可以在不重建或重新組織指定之索引的情況下，設定這個索引的 ALLOW_ROW_LOCKS、ALLOW_PAGE_LOCKS、IGNORE_DUP_KEY 和 STATISTICS_NORECOMPUTE 選項。 修改的值會立即套用在索引上。 若要檢視這些設定，請使用 sys.indexes。 如需詳細資訊，請參閱＜設定索引選項＞。

資料列和頁面鎖定選項

如果 ALLOW_ROW_LOCKS = ON 且 ALLOW_PAGE_LOCK = ON，當您存取索引時，允許資料列、頁面和資料表層級的鎖定。 Database Engine 會選擇適當的鎖定，且可以將鎖定從資料列或頁面鎖定擴大到資料表鎖定。

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

如果指定 ALL，且設定了資料列或頁面鎖定，便會將這些設定套用至所有索引上。 當基礎資料表是堆積時，會依照下列方式來套用設定：

ALLOW_ROW_LOCKS = ON 或 OFF

套用在堆積和任何相關聯的非叢集索引上。

ALLOW_PAGE_LOCKS = ON

套用在堆積和任何相關聯的非叢集索引上。

ALLOW_PAGE_LOCKS = OFF

完整套用在非叢集索引上。 這表示在非叢集索引上，不允許所有頁面鎖定。 在堆積上，不允許的鎖定只有頁面的共用 (S)、更新 (U) 和獨佔 (X) 鎖定。 Database Engine 仍能取得意圖頁面鎖定 (IS、IU 或 IX)，供內部使用。

線上索引作業

當重建索引且 ONLINE 選項設為 ON 時，查詢和資料修改可以使用基礎物件、資料表和相關聯的索引。 您也可以在線上重建位於單一分割區之索引的一部分。 在改變過程中，只會在非常短的時間內，保留獨佔的資料表鎖定。

索引一律是在線上重新組織。 這個過程不會長期保留鎖定，因此，不會封鎖執行中的查詢或更新。

只有在執行下列動作時，您才能在相同資料表或資料表分割區上執行並行的線上索引作業：

  • 建立多個非叢集索引。

  • 在相同資料表上重新組織不同的索引。

  • 在重建相同資料表的非重疊索引時，重新組織不同的索引。

同時執行的所有其他線上索引作業都會失敗。 例如，您不能在相同資料表上，同時重建兩個或更多索引，或在相同資料表上重建現有索引時，建立新的索引。

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

WAIT_AT_LOW_PRIORITY

為了執行線上索引重建的 DDL 陳述式，特定資料表上執行的所有使用中封鎖交易都必須完成。 當線上索引重建執行時，它會封鎖這個資料表上準備開始執行的所有新交易。 雖然線上索引重建的鎖定期間很短，但是等候特定資料表上所有未完成的交易完成並封鎖要開始的新交易，可能會大幅影響輸送量，導致工作負載速度變慢或逾時，並且大幅限制對基礎資料表的存取。 WAIT_AT_LOW_PRIORITY 選項可讓 DBA 管理線上索引重建所需的 S 鎖定和 Sch-M 鎖定，並允許它們選取 3 個選項的其中一個。 在這 3 個案例中，如果在等候期間 ((MAX_DURATION = n [minutes])) 沒有封鎖活動，線上索引重建會立即執行而不等候，並 DDL 陳述式會完成。

空間索引的限制

當您重建空間索引時，在索引作業的持續時間，無法使用基礎使用者資料表，因為空間索引會持有結構描述鎖定。

使用者資料表中的 PRIMARY KEY 條件約束無法在空間索引定義於該資料表的資料行上時，加以修改。 若要變更 PRIMARY KEY 條件約束，請先卸除此資料表的每一個空間索引。 在修改 PRIMARY KEy 條件約束之後，您可以重新建立每一個空間索引。

在單一分割區重建作業中，您不能指定任何空間索引。 但是，您可以在完整分割區重建中指定空間索引。

若要變更空間索引特定的選項 (例如 BOUNDING_BOX 或 GRID)，您可以使用指定 DROP_EXISTING = ON 的 CREATE SPATIAL INDEX 陳述式，或是卸除此空間索引並建立新的索引。 如需範例，請參閱＜CREATE SPATIAL INDEX (Transact-SQL)＞。

資料壓縮

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

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

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

  • 當您使用 ALTER INDEX ALL ..., 時，您無法在資料表具有非對齊索引時變更單一分割區的壓縮設定。

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

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

Statistics

當您針對資料表執行 ALTER INDEX ALL … 時，只會更新與索引相關聯的統計資料。 針對資料表 (而非索引) 所建立的自動或手動統計資料不會進行更新。

Permissions

若要執行 ALTER INDEX，至少需要資料表或檢視表的 ALTER 權限。

範例

A.重建索引

下列範例會在 AdventureWorks2012 資料庫的 Employee 資料表上重建單一索引。

ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee
REBUILD;

B.在資料表上重建所有索引以及指定選項

下列範例指定 ALL 關鍵字。 這樣會重建與 AdventureWorks2012 資料庫中 Production.Product 資料表相關聯的所有索引。 指定三個選項。

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

 
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON);

下列範例會加入包括低優先權鎖定選項的 ONLINE 選項，並加入資料列壓縮選項。

適用於：SQL Server 2014 到 SQL Server 2014。

 
ALTER INDEX ALL ON Production.Product
REBUILD WITH 
(
    FILLFACTOR = 80, 
    SORT_IN_TEMPDB = ON,
    STATISTICS_NORECOMPUTE = ON,
    ONLINE = ON ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 4 MINUTES, ABORT_AFTER_WAIT = BLOCKERS ) ), 
    DATA_COMPRESSION = ROW
)
;

C.重建叢集資料行存放區索引

第一個步驟是準備包含叢集資料行存放區索引的 FactInternetSales2 資料表，並插入前四個資料行的資料。

USE AdventureWorksDW2012;
GO
CREATE TABLE dbo.FactInternetSales2 (
    ProductKey [int] NOT NULL, 
    OrderDateKey [int] NOT NULL, 
    DueDateKey [int] NOT NULL, 
    ShipDateKey [int] NOT NULL);

CREATE CLUSTERED COLUMNSTORE INDEX cci_FactInternetSales2
ON dbo.FactInternetSales2;

INSERT INTO dbo.FactInternetSales2
SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey
FROM dbo.FactInternetSales;

SELECT * FROM sys.column_store_row_groups;

結果顯示一個 OPEN 資料列群組，這表示 SQL Server 將會等候更多的資料列加入，然後才會關閉資料列群組並將資料移到資料行存放區。 下一個陳述式會重建叢集資料行存放區索引。

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REBUILD;
SELECT * FROM sys.column_store_row_groups;

SELECT 陳述式的結果顯示資料列群組為 COMPRESSED，這表示資料列群組的資料行區段現在已壓縮，而且儲存在資料行存放區中。

D.重新組織具有 LOB 壓縮的索引

下列範例會重新組織 AdventureWorks2012 資料庫中的單一叢集索引。 由於索引在分葉層級中包含 LOB 資料類型，因此，這個陳述式也會壓縮包含大型物件資料的所有頁面。 請注意，您不需要指定 WITH (LOB_COMPACTION) 選項，因為預設值是 ON。

ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto
REORGANIZE ;

E.設定索引選項

下列範例會設定 AdventureWorks2012 資料庫中 AK_SalesOrderHeader_SalesOrderNumber 索引的幾個選項。

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

 
ALTER INDEX AK_SalesOrderHeader_SalesOrderNumber ON
    Sales.SalesOrderHeader
SET (
    STATISTICS_NORECOMPUTE = ON,
    IGNORE_DUP_KEY = ON,
    ALLOW_PAGE_LOCKS = ON
    ) ;
GO

F.停用索引

下列範例會停用 AdventureWorks2012 資料庫中 Employee 資料表的非叢集索引。

ALTER INDEX IX_Employee_ManagerID ON HumanResources.Employee
DISABLE ;

G.停用條件約束

下列範例會藉由停用 AdventureWorks2012 資料庫中 PRIMARY KEY 索引來停用 PRIMARY KEY 條件約束。 基礎資料表的 FOREIGN KEY 條件約束會自動停用，並且會顯示一則警告訊息。

ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department
DISABLE ;

結果集會傳回這則警告訊息。

Warning: Foreign key 'FK_EmployeeDepartmentHistory_Department_DepartmentID'

on table 'EmployeeDepartmentHistory' referencing table 'Department'

was disabled as a result of disabling the index 'PK_Department_DepartmentID'.

H.啟用條件約束

下列範例會啟用 F 範例所停用的 PRIMARY KEY 和 FOREIGN KEY 條件約束。

PRIMARY KEY 條件約束是藉由重建 PRIMARY KEY 索引來啟用。

ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department
REBUILD ;

然後會啟用 FOREIGN KEY 條件約束。

ALTER TABLE HumanResources.EmployeeDepartmentHistory
CHECK CONSTRAINT FK_EmployeeDepartmentHistory_Department_DepartmentID;
GO

I.重建分割區索引

下列範例會重建 AdventureWorks2012 資料庫中分割區索引 IX_TransactionHistory_TransactionDate 的單一分割區，分割區編號是 5。 分割區 5 在線上重建，而且低優先權鎖定的 10 分鐘等候時間會分別套用至索引重建作業取得的每一個鎖定。 如果在此時間無法取得鎖定來完成索引重建，重建作業陳述式會中止。

適用於：SQL Server 2014 到 SQL Server 2014。

 
-- Verify the partitioned indexes.
SELECT *
FROM sys.dm_db_index_physical_stats (DB_ID(),OBJECT_ID(N'Production.TransactionHistory'), NULL , NULL, NULL);
GO
--Rebuild only partition 5.
ALTER INDEX IX_TransactionHistory_TransactionDate
ON Production.TransactionHistory
REBUILD Partition = 5 
   WITH ( ONLINE = ON ( WAIT_AT_LOW_PRIORITY (MAX_DURATION = 10 minutes, ABORT_AFTER_WAIT = SELF )))
;
GO

J.變更索引的壓縮設定

下列範例會在非分割資料列存放區資料表上重建索引。

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

 
ALTER INDEX IX_INDEX1 
ON T1
REBUILD 
WITH ( DATA_COMPRESSION = PAGE );
GO

下列範例會重建叢集資料行存放區索引來使用封存壓縮，然後示範如何移除封存壓縮。 最後的結果只會使用資料行存放區壓縮。

適用於：SQL Server 2014 到 SQL Server 2014。

 
--Prepare the example by creating a table with a clustered columnstore index.
CREATE TABLE SimpleTable (
    ProductKey [int] NOT NULL, 
    OrderDateKey [int] NOT NULL, 
    DueDateKey [int] NOT NULL, 
    ShipDateKey [int] NOT NULL
);

CREATE CLUSTERED INDEX cci_SimpleTable ON SimpleTable (ProductKey);

CREATE CLUSTERED COLUMNSTORE INDEX cci_SimpleTable
ON SimpleTable
WITH ( DROP_EXISTING = ON );

--Compress the table further by using archival compression.
ALTER INDEX cci_SimpleTable ON SimpleTable
REBUILD
WITH ( DATA_COMPRESSION = COLUMNSTORE_ARCHIVE );

--Remove the archive compression and only use columnstore compression.
ALTER INDEX cci_SimpleTable ON SimpleTable
REBUILD
WITH ( DATA_COMPRESSION = COLUMNSTORE );
GO

如需其他資料壓縮範例，請參閱＜資料壓縮＞。

請參閱

參考

CREATE INDEX (Transact-SQL)

CREATE SPATIAL INDEX (Transact-SQL)

CREATE XML INDEX (Transact-SQL)

DROP INDEX (Transact-SQL)

sys.dm_db_index_physical_stats (Transact-SQL)

EVENTDATA (Transact-SQL)

概念

停用索引和條件約束

XML 索引 (SQL Server)

線上執行索引作業

重新組織與重建索引