ALTER INDEX (Transact-SQL)

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

Transact-SQL 語法慣例

語法

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

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

引數

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

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

  這項作業指定 ALL	如果資料表有一個或多個下列項目，便告失敗
  REBUILD WITH ONLINE = ON	XML 索引 空間索引 大型物件資料類型資料行：image、text、ntext、varchar(max)、nvarchar(max)、varbinary(max) 和 xml。
  REBUILD PARTITION = partition_number	非資料分割索引、XML 索引、空間索引或停用的索引
  REORGANIZE	ALLOW_PAGE_LOCKS 設定為 OFF 的索引
  REORGANIZE PARTITION = partition_number	非資料分割索引、XML 索引、空間索引或停用的索引
  IGNORE_DUP_KEY = ON	空間索引 XML 索引
  ONLINE = ON	空間索引 XML 索引

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

• database_name
  這是資料庫的名稱。

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

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

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

  當您重建 XML 索引或空間索引時，ONLINE = ON 和 IGNORE_DUP_KEY = ON 的選項無效。

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

  如果資料庫復原模式設為大量記錄模式或簡單模式，重建作業便可以只進行最基本的記錄。如需詳細資訊，請參閱＜選擇索引作業的復原模式＞。

  [!附註]

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

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

  PARTITION = ALL 會重建所有資料分割。

• partition_number
  這是將重建或重新組織之資料分割索引的資料分割數目。partition_number 是一個可以參考變數的常數運算式。其中包括使用者自訂類型變數或函數以及使用者自訂函數，但不能參考 Transact-SQL 陳述式。partition_number 必須存在，否則，陳述式便會失敗。

• WITH (<single_partition_rebuild_index_option>)
  SORT_IN_TEMPDB、MAXDOP 和 DATA_COMPRESSION 是重建單一資料分割 (PARTITION = n) 時所能指定的選項。在單一資料分割重建作業中，不能指定 XML 索引。

  重建資料分割索引不能在線上執行。進行這個作業期間，會鎖定整份資料表。

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

• REORGANIZE
  指定將重新組織索引分葉層級。這個子句相當於 DBCC INDEXDEFRAG。ALTER INDEX REORGANIZE 陳述式一律是在線上執行。這表示不會保留長期封鎖的資料表鎖定，在 ALTER INDEX REORGANIZE 交易期間，可以繼續查詢或更新基礎資料表。停用的索引或 ALLOW_PAGE_LOCKS 設為 OFF 的索引不能指定 REORGANIZE。

• WITH ( LOB_COMPACTION = { ON | OFF } )
  指定壓縮包含大型物件 (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 }
  指定索引填補。預設值是 OFF。

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

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

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

• FILLFACTOR = fillfactor
  指定百分比來指出在建立或更改索引期間，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 }
  指定是否將排序結果儲存在 tempdb 中。預設值是 OFF。

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

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

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

  如需詳細資訊，請參閱＜建立 tempdb 與索引＞。

• IGNORE_DUP_KEY = { ON | OFF }
  指定當插入作業嘗試將重複的索引鍵值插入唯一索引時所產生的錯誤回應。IGNORE_DUP_KEY 選項只適用於在建立或重建索引之後所發生的插入作業。執行 CREATE INDEX、ALTER INDEX 或 UPDATE 時，這個選項沒有任何作用。預設值是 OFF。

  • ON
    當重複的索引鍵值插入唯一索引時，就會出現警告訊息。只有違反唯一性條件約束的資料列才會失敗。

  • OFF
    當重複的索引鍵值插入唯一索引時，就會出現錯誤訊息。整個 INSERT 作業將會回復。

  若為針對檢視表所建立的索引、非唯一索引、XML 索引、空間索引和篩選索引，IGNORE_DUP_KEY 不得設為 ON。

  若要檢視 IGNORE_DUP_KEY，請使用 sys.indexes。

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

• STATISTICS_NORECOMPUTE = { ON | OFF }
  指定是否要重新計算散發統計資料。預設值是 OFF。

  • ON
    不會自動重新計算過期的統計資料。

  • OFF
    啟用自動統計資料更新。

  若要還原自動統計資料更新，請將 STATISTICS_NORECOMPUTE 設為 OFF，或執行不含 NORECOMPUTE 子句的 UPDATE STATISTICS。

  重要事項
  停用散發統計資料的自動重新計算，可以防止查詢最佳化工具取得包含資料表的查詢之最佳執行計畫。

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

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

  [!附註]

  只有 SQL Server Enterprise、Developer 和 Evaluation 版本上才可使用線上索引作業。

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

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

  如需詳細資訊，請參閱＜線上索引作業如何運作＞。如需有關鎖定的詳細資訊，請參閱＜鎖定模式＞。

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

  • XML 索引

  • 本機暫存資料表的索引

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

  • 基礎資料表包含 LOB 資料類型的叢集索引

  • 利用 LOB 資料類型資料行來定義的非叢集索引

  如果資料表包含 LOB 資料類型，但索引定義未利用任何這些資料行來做為索引鍵或非索引鍵資料行，便可以在線上重建非叢集索引。

• ALLOW_ROW_LOCKS = { ON | OFF }
  指定是否允許資料列鎖定。預設值是 ON。

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

  • OFF
    不使用資料列鎖定。

• ALLOW_PAGE_LOCKS = { ON | OFF }
  指定是否允許頁面鎖定。預設值是 ON。

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

  • OFF
    不使用頁面鎖定。

  [!附註]

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

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

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

  max_degree_of_parallelism 可以是：

  • 1
    抑制產生平行計畫。

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

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

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

  [!附註]

  只有 SQL Server Enterprise、Developer 和 Evaluation 版本才可使用平行索引作業。

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

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

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

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

  如需有關壓縮的詳細資訊，請參閱＜建立壓縮資料表及索引＞。

• ON PARTITIONS ( { <partition_number_expression> | <range> } [,...n] )
  指定套用 DATA_COMPRESSION 設定的資料分割。如果未分割此索引，ON PARTITIONS 引數將會產生錯誤。如果未提供 ON PARTITIONS 子句，DATA_COMPRESSION 選項會套用到資料分割索引的所有資料分割。

  可以使用以下方式來指定 <partition_number_expression>：

  • 提供資料分割的編號，例如：ON PARTITIONS (2)。

  • 為數個個別資料分割提供以逗號分隔的資料分割編號，例如：ON PARTITIONS (1, 5)。

  • 同時提供範圍和個別資料分割，例如：ON PARTITIONS (2, 4, 6 TO 8)。

  <range> 可以指定為以 TO 一字分隔的資料分割編號，例如：ON PARTITIONS (6 TO 8)。

  若要為不同的資料分割設定不同類型的資料壓縮，請指定 DATA_COMPRESSION 選項一次以上，例如：

  REBUILD WITH 
  (
  DATA_COMPRESSION = NONE ON PARTITIONS (1), 
  DATA_COMPRESSION = ROW ON PARTITIONS (2, 4, 6 TO 8), 
  DATA_COMPRESSION = PAGE ON PARTITIONS (3, 5)
  )
  

備註

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

重新組織索引

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

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

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

停用索引

停用索引可防止使用者存取索引，如果是叢集索引，則可防止使用者存取基礎資料表的資料。索引定義會保留在系統目錄中。停用檢視的非叢集索引或叢集索引時，會實際刪除索引資料。停用叢集索引可以防止存取資料，資料仍會保留在 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 or OFF	套用在堆積和任何相關聯的非叢集索引上。
ALLOW_PAGE_LOCKS = ON	套用在堆積和任何相關聯的非叢集索引上。
ALLOW_PAGE_LOCKS = OFF	完整套用在非叢集索引上。這表示在非叢集索引上，不允許所有頁面鎖定。在堆積上，不允許的鎖定只有頁面的共用 (S)、更新 (U) 和獨佔 (X) 鎖定。Database Engine 仍能取得意圖頁面鎖定 (IS、IU 或 IX)，供內部使用。

如需詳細資訊，請參閱＜鎖定擴大 (Database Engine)＞。

線上索引作業

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

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

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

• 建立多個非叢集索引。

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

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

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

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

空間索引的限制

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

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

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

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

資料壓縮

如需有關資料壓縮的詳細資訊，請參閱＜建立壓縮資料表及索引＞。

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

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

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

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

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

權限

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

範例

A. 重建索引

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

USE AdventureWorks;
GO
ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee
REBUILD;
GO

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

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

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

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

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

USE AdventureWorks;
GO
ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto
REORGANIZE ;
GO

D. 設定索引選項

下列範例設定 AK_SalesOrderHeader_SalesOrderNumber 索引的一些選項。

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

E. 停用索引

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

USE AdventureWorks;
GO
ALTER INDEX IX_Employee_ManagerID ON HumanResources.Employee
DISABLE ;
GO

F. 停用條件約束

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

USE AdventureWorks;
GO
ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department
DISABLE ;
GO

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

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

G. 啟用條件約束

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

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

USE AdventureWorks;
GO
ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department
REBUILD ;
GO

之後，便啟用 FOREIGN KEY 條件約束。

ALTER TABLE HumanResources.EmployeeDepartmentHistory
CHECK CONSTRAINT FK_EmployeeDepartmentHistory_Department_DepartmentID;
GO

H. 重建資料分割索引

下列範例會重建資料分割索引 IX_TransactionHistory_TransactionDate 的單一資料分割，資料分割編號是 5。這個範例假設您已安裝資料分割索引範例。

USE AdventureWorks;
GO
-- 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;
GO

I. 變更索引的壓縮設定

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

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

如需其他資料壓縮範例，請參閱＜建立壓縮資料表及索引＞。

變更記錄

更新的內容
從因為 REORGANIZE 而失敗的索引中移除停用的索引。

請參閱

參考

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 資料類型資料行中的索引

線上執行索引作業

重新組織和重建索引