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

    空間索引

    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 會重建所有資料分割。

    警告注意事項注意

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

  • 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
    指定將重新組織索引分葉層級。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 和 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 選項。

    如需詳細資訊，請參閱＜索引的 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。

    重要事項重要事項

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

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

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

    [!附註]

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

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

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

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

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

    • XML 索引

    • 本機暫存資料表的索引

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

  • 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 (預設值)
      根據目前的系統工作負載來使用實際數目的處理器或比實際數目更少的處理器。

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

    [!附註]

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

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

在舊版的 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 或 OFF

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

ALLOW_PAGE_LOCKS = ON

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

ALLOW_PAGE_LOCKS = OFF

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

線上索引作業

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

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

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

  • 建立多個非叢集索引。

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

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

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

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

空間索引的限制

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

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

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

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

資料行存放區索引限制

除了 REBUILD 選項以外，您無法改變 xVelocity 記憶體最佳化的資料行存放區索引。請改為卸除並重新建立資料行存放區索引。

資料壓縮

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

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

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

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

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

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

統計資料

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

權限

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

範例

A. 重建索引

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

USE AdventureWorks2012;
GO
ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD;
GO

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

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

USE AdventureWorks2012;
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 AdventureWorks2012;
GO
ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto
REORGANIZE ;
GO

D. 設定索引選項

下列範例會設定 AK_SalesOrderHeader_SalesOrderNumber 索引的幾個選項。

USE AdventureWorks2012;
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 AdventureWorks2012;
GO
ALTER INDEX IX_Employee_OrganizationNode ON HumanResources.Employee
DISABLE ;
GO

F. 停用條件約束

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

USE AdventureWorks2012;
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 AdventureWorks2012;
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

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

請參閱

參考

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)

線上執行索引作業

重新組織與重建索引