發行項
04/02/2012

ALTER TABLE (Transact-SQL)

利用下列方式來修改資料表定義：改變、新增或卸除資料行和條件約束、重新指派資料分割，或者停用或啟用條件約束和觸發程序。

語法

ALTER TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name 
{ 
    ALTER COLUMN column_name 
    { 
        [ type_schema_name. ] type_name [ ( { precision [ , scale ] 
            | max | xml_schema_collection } ) ] 
        [ COLLATE collation_name ] 
        [ NULL | NOT NULL ] [ SPARSE ]
    | {ADD | DROP } 
        { ROWGUIDCOL | PERSISTED | NOT FOR REPLICATION | SPARSE }
    } 
        | [ WITH { CHECK | NOCHECK } ]

    | ADD 
    { 
        <column_definition>
      | <computed_column_definition>
      | <table_constraint> 
      | <column_set_definition> 
    } [ ,...n ]

    | DROP 
    { 
        [ CONSTRAINT ] constraint_name 
        [ WITH ( <drop_clustered_constraint_option> [ ,...n ] ) ]
        | COLUMN column_name 
    } [ ,...n ] 

    | [ WITH { CHECK | NOCHECK } ] { CHECK | NOCHECK } CONSTRAINT 
        { ALL | constraint_name [ ,...n ] } 

    | { ENABLE | DISABLE } TRIGGER 
        { ALL | trigger_name [ ,...n ] }

    | { ENABLE | DISABLE } CHANGE_TRACKING 
        [ WITH ( TRACK_COLUMNS_UPDATED = { ON | OFF } ) ]

    | SWITCH [ PARTITION source_partition_number_expression ]
        TO target_table 
        [ PARTITION target_partition_number_expression ]

    | SET ( FILESTREAM_ON = { partition_scheme_name | filegroup | 
                "default" | "NULL" } )

    | REBUILD 
      [ [PARTITION = ALL]
        [ WITH ( <rebuild_option> [ ,...n ] ) ] 
      | [ PARTITION = partition_number 
           [ WITH ( <single_partition_rebuild_option> [ ,...n ] )]
        ]
      ]

    | (<table_option>)
}
[ ; ]

<column_set_definition> ::= 
    column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS

<drop_clustered_constraint_option> ::=  
    { 
        MAXDOP = max_degree_of_parallelism

      | ONLINE = {ON | OFF }
      | MOVE TO { partition_scheme_name ( column_name ) | filegroup
          | "default" }
    }
<table_option> ::=
    {
        SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )
    }

<single_partition_rebuild__option> ::=
{
      SORT_IN_TEMPDB = { ON | OFF }
    | MAXDOP = max_degree_of_parallelism
    | DATA_COMPRESSION = { NONE | ROW | PAGE} }
}

引數

database_name
這是建立資料表的資料庫名稱。
schema_name
這是資料表所屬的結構描述名稱。
table_name
這是要變更的資料表名稱。如果資料表不在目前資料庫中，或未包含在目前使用者擁有的結構描述內，則必須明確指定該資料庫和結構描述。
ALTER COLUMN
指定將要變更或改變的具名資料行。如需詳細資訊，請參閱＜sp_dbcmptlevel (Transact-SQL)＞。

修改過的資料行不得為下列任何一項：
- 資料類型為 timestamp 的資料行。
- 資料表的 ROWGUIDCOL。
- 計算資料行，或用於計算資料行。
- 用於索引時，除非資料行是 varchar、nvarchar 或 varbinary 資料類型，否則不會變更資料類型，且新大小等於或大於舊大小，而且索引不是 PRIMARY KEY 條件約束的結果。
- 用於 CREATE STATISTICS 陳述式所產生的統計資料時，除非資料行是 varchar、nvarchar 或 varbinary 資料類型，否則不會變更資料類型，且新大小等於或大於舊大小，而且資料行會從非 Null 變為 Null。首先，利用 DROP STATISTICS 陳述式移除統計資料。ALTER COLUMN 會自動卸除查詢最佳化工具自動產生的統計資料。
- 在 PRIMARY KEY 或 [FOREIGN KEY] REFERENCES 條件約束中使用。
- 在 CHECK 或 UNIQUE 條件約束中使用。不過，允許變更用於 CHECK 或 UNIQUE 條件約束的可變長度資料行的長度。
- 與預設定義相關聯。不過，如果資料類型沒有變更，則會變更資料行的長度、有效位數或小數位數。
  
  text、ntext 及 image 資料行的資料類型只能利用下列方式來變更：
  - 將 text 改為 varchar(max)、nvarchar(max) 或 xml
  - 將 ntext 改為 varchar(max)、nvarchar(max) 或 xml
  - image 改為varbinary(max)
  某些資料類型變更可能會使資料變更。例如，如果將 nchar 或 nvarchar 資料行改為 char 或 varchar，則可能會轉換擴充字元。如需詳細資訊，請參閱＜CAST 和 CONVERT (Transact-SQL)＞。減少資料行的有效位數或小數位數可能會使資料截斷。
  
  資料分割資料表之資料行的資料類型不能變更。
column_name
這是要變更、加入或卸除的資料行名稱。column_name 最多可有 128 個字元。如果是新資料行，以 timestamp 資料類型建立的資料行，可以省略 column_name。如果 timestamp 資料類型資料行未指定任何 column_name，便會使用 timestamp 這個名稱。
[ type_schema_name**.**] type_name
這是改變資料行的新資料類型，或加入的資料行之資料類型。無法針對已進行資料分割的資料表現有資料行指定 type_name。type_name 可以是下列項目之一：
- SQL Server 系統資料類型。
- 基於 SQL Server 系統資料類型的別名資料類型。別名資料類型是利用 CREATE TYPE 陳述式建立的，在這之後才能在資料表定義中使用它們。
- .NET Framework 使用者定義型別及其所屬結構描述。必須先利用 CREATE TYPE 陳述式建立 .NET Framework 使用者定義型別，才能在資料表定義中使用它們。
以下是改變資料行之 type_name 的準則：
- 前一個資料類型必須可隱含轉換至新資料類型。
- type_name 不得為 timestamp。
- ALTER COLUMN 的 ANSI_NULL 預設值一律開啟；如果未指定，資料行可為 Null。
- ALTER COLUMN 的 ANSI_PADDING 填補一律為 ON。
- 如果修改的資料行是識別欄位，new_data_type 必須是支援識別屬性的資料類型。
- SET ARITHABORT 的目前設定會被忽略。如果 ARITHABORT 設為 ON，ALTER TABLE 就會執行作業。
[!附註]

如果未指定 COLLATE 子句，變更資料行的資料類型會使資料庫的預設定序發生定序變更。
precision
這是指定之資料類型的有效位數。如需有關有效位數值的詳細資訊，請參閱＜有效位數、小數位數和長度 (Transact-SQL)＞。
scale
這是指定之資料類型的小數位數。如需有關有效小數位數值的詳細資訊，請參閱＜有效位數、小數位數和長度 (Transact-SQL)＞。
max
只適用於 varchar、nvarchar 及 varbinary 資料類型，可用來儲存 2^31-1 位元組的字元、二進位資料及 Unicode 資料。
xml_schema_collection
只適用於 xml 資料類型，可用來將 XML 結構描述關聯於該類型。將 xml 資料行輸入結構描述集合之前，必須先利用 CREATE XML SCHEMA COLLECTION，在資料庫中建立結構描述集合。
COLLATE < collation_name >
指定變更資料行的新定序。若未指定，就會將資料庫的預設定序指派給資料行。定序名稱可以是 Windows 定序名稱或 SQL 定序名稱。如需清單和詳細資訊，請參閱＜Windows 定序名稱 (Transact-SQL)＞和＜SQL Server 定序名稱 (Transact-SQL)＞。

COLLATE 子句只可用來變更 char、varchar、nchar 及 nvarchar 資料類型之資料行的定序。若要變更使用者定義之別名資料類型資料行的定序，您必須執行個別的 ALTER TABLE 陳述式，將資料行變更為 SQL Server 系統資料類型，並變更它的定序，然後再將資料行改回別名資料類型。

如果存在下列一個或多個條件，ALTER COLUMN 不能有定序變更：
- 如果 CHECK 條件約束、FOREIGN KEY 條件約束或計算資料行參考變更的資料行。
- 如果在資料行上建立任何索引、統計資料或全文檢索索引。如果資料行定序變更了，在變更的資料行上自動建立的統計資料就會卸除。
- 如果結構描述繫結檢視表或函數參考資料行。
如需詳細資訊，請參閱＜COLLATE (Transact-SQL)＞。
SPARSE NULL | NOT NULL
指定資料行是否為疏鬆資料行或者可接受 Null 值。如果要改變的資料行是疏鬆資料行，您必須明確指定這個屬性，否則資料行將會還原為非疏鬆資料行。疏鬆資料行無法指定為 NOT NULL。將資料行從疏鬆轉換成非疏鬆 (或相反) 會在命令執行期間疏鬆鎖定資料表。

如需有關疏鬆資料行以及可為 Null 的其他限制和詳細資訊，請參閱＜使用疏鬆資料行＞。

只有在不允許 Null 值的資料行指定了預設值，或資料表是空的情況下，才能利用 ALTER TABLE 新增這些資料行。只有在也指定了 PERSISTED 時，計算資料行才能指定 NOT NULL。如果新資料行允許 Null 值，且未指定預設值，資料表每個資料列的新資料行都會包含 Null 值。如果新資料行允許 Null 值，且加入了預設定義，就可以利用 WITH VALUES，將預設值儲存在資料表每個現有資料列的新資料行中。

如果新資料行不允許 Null 值，且資料表不是空的，則必須利用新資料行新增 DEFAULT 定義，則新資料行會自動將預設值載入每個現有資料列中的新資料行。

您可以在 ALTER COLUMN 中指定 NULL，來強制 NOT NULL 資料行允許 NULL 值 (但不包括 PRIMARY KEY 條件約束中的資料行)。只有在資料行沒有包含 Null 值的情況下，才能在 ALTER COLUMN 中指定 NOT NULL。必須先將 Null 值更新為某些值，才能允許 ALTER COLUMN NOT NULL，例如：
```
UPDATE MyTable SET NullCol = N'some_value' WHERE NullCol IS NULL
ALTER TABLE MyTable ALTER COLUMN NullCOl NVARCHAR(20) NOT NULL
```
當您建立或改變一份含有 CREATE TABLE 或 ALTER TABLE 陳述式的資料表時，資料庫和工作階段設定會影響且可能會覆寫資料行定義中使用之資料類型的 Null 屬性。我們建議您一定要針對非計算資料行，明確將資料行定義為 NULL 或 NOT NULL。

如果您加入一個具有使用者定義資料類型的資料行，我們建議您最好使用與此使用者定義資料類型相同的 Null 屬性來定義此資料行，並為此資料行指定預設值。如需詳細資訊，請參閱＜CREATE TABLE (Transact-SQL)＞。

[!附註]

如果利用 ALTER COLUMN 指定 NULL 或 NOT NULL，則必須同時指定 new_data_type [(precision [, scale ])]。如果資料類型、有效位數及小數位數沒有變更，請指定目前的資料行值。
[ {ADD | DROP} ROWGUIDCOL ]
指定將 ROWGUIDCOL 屬性加入至指定的資料行，或從指定的資料行卸除該屬性。ROWGUIDCOL 指出資料行是資料列 GUID 資料行。每份資料表只能指定一個 uniqueidentifier 資料行做為 ROWGUIDCOL 資料行，且只能將 ROWGUIDCOL 屬性指派給 uniqueidentifier 資料行。不能將 ROWGUIDCOL 指派給使用者定義資料類型的資料行。

ROWGUIDCOL 不強制使用儲存在資料行中之值的唯一性，且不針對插入資料表中的新資料列自動產生值。若要產生每個資料行的唯一值，請在 INSERT 陳述式上使用 NEWID 函數，或將 NEWID 函數指定為資料行的預設值。
[ {ADD | DROP} PERSISTED ]
指定將 PERSISTED 屬性加入至指定的資料行，或從指定的資料行卸除該屬性。該資料行必須是一個利用具決定性運算式定義的計算資料行。就指定為 PERSISTED 的資料行而言，當計算資料行相依的任何其他資料行更新時，Database Engine 實際上會將計算值儲存在資料表並將值更新。將計算資料行標示計算資料行為 PERSISTED，就可以在定義於具決定性 (但不是精確) 運算式上的計算資料行上建立索引。如需詳細資訊，請參閱＜在計算資料行上建立索引＞。

當做資料分割資料表之資料分割資料行的任何計算資料行，都必須明確標示為 PERSISTED。
DROP NOT FOR REPLICATION
指定當複寫代理程式執行插入作業時，識別欄位中的值會累加。只有當 column_name 是識別欄位時，才能指定這個子句。如需詳細資訊，請參閱＜使用 NOT FOR REPLICATION 控制條件約束、識別和觸發程序＞。
SPARSE
指定要加入或卸除的資料行為疏鬆資料行。疏鬆資料行的儲存體會針對 Null 值最佳化。疏鬆資料行無法指定為 NOT NULL。將資料行從疏鬆轉換成非疏鬆 (或相反) 會在命令執行期間疏鬆鎖定資料表。您可能必須使用 REBUILD 子句來回收任何空間節約。

重要事項

每當您改變資料行時，都必須指定 SPARSE 屬性，否則資料行將會還原為非疏鬆資料行。

如需有關疏鬆資料行的其他限制和詳細資訊，請參閱＜使用疏鬆資料行＞。
WITH CHECK | WITH NOCHECK
指定是否要依照新加入或重新啟用的 FOREIGN KEY 或 CHECK 條件約束來驗證資料表中的資料。如果未指定，則假設 WITH CHECK 為新條件約束，並假設 WITH NOCHECK 為重新啟用的條件約束。

如果您不要依照現有的資料來確認新的 CHECK 或 FOREIGN KEY 條件約束，請使用 WITH NOCHECK。除了極少數的狀況外，我們建議您不要這麼做。在以後的所有資料更新中將會評估新條件約束。新增條件約束時，如果 WITH NOCHECK 抑制任何強制違規，當未來的更新作業更新含有不符合該條件約束的資料列時，這些強制違規可能會使這些更新作業失敗。

查詢最佳化工具不考量定義為 WITH NOCHECK 的條件約束。這類條件約束會被忽略，直到利用 ALTER TABLE <table> WITH CHECK CHECK CONSTRAINT ALL 重新啟用為止。
ADD
指定加入一個或多個資料行定義、計算資料行定義或資料表條件約束。
DROP { [ CONSTRAINT ] constraint_name | COLUMN column_name }
指定從資料表中移除 constraint_name 或 column_name。可以列出多個資料行和條件約束。

您可以透過查詢 sys.check_constraint、sys.default_constraints、sys.key_constraints 和 sys.foreign_keys 目錄檢視，判斷條件約束的使用者定義或系統提供名稱。

如果 XML 索引存在於資料表上，則不能卸除 PRIMARY KEY 條件約束。

當資料行符合下列條件時，則無法將它卸除：
- 用於索引。
- 用於 CHECK、FOREIGN KEY、UNIQUE 或 PRIMARY KEY 條件約束。
- 與利用 DEFAULT 關鍵字定義的預設值相關聯，或者繫結到預設物件。
- 繫結至規則。
[!附註]

卸除資料行不會回收資料行的磁碟空間。當資料表的資料列大小接近或已超出限制時，您可能需要回收卸除之資料行的磁碟空間。您可以在資料表上建立叢集索引，或利用 ALTER INDEX 重建現有的叢集索引來回收空間。
WITH <drop_clustered_constraint_option>
指定設定一個或多個卸除叢集條件約束選項。
MAXDOP = max_degree_of_parallelism
只針對作業持續時間覆寫 max degree of parallelism 組態選項。如需詳細資訊，請參閱＜max degree of parallelism 選項＞。

請利用 MAXDOP 選項來限制執行平行計畫所用的處理器數目。最大值是 64 個處理器。

max_degree_of_parallelism 可以是下列值之一：
- 1
  抑制平行計畫的產生。
- >1
  將平行索引作業所用的最大處理器數目限制為指定的數目。
- 0 (預設值)
  根據目前的系統工作負載來使用實際數目的處理器或比實際數目更少的處理器。
如需詳細資訊，請參閱＜設定平行索引作業＞。

[!附註]

只有 SQL Server Enterprise、Developer 和 Evaluation 版本才可使用平行索引作業。
ONLINE = { ON | OFF }
指定在索引作業期間，查詢和資料修改是否能夠使用基礎資料表和相關聯的索引。預設值為 OFF。REBUILD 可以執行為 ONLINE 作業。
- ON
  索引作業持續期間不會保留長期資料表鎖定。在索引作業的主要階段期間，來源資料表上只保留意圖共用 (IS) 鎖定。這使得基礎資料表和索引的查詢或更新能夠繼續運作。在作業開始時，只會在一段很短的時間內，保留來源物件的共用 (S) 鎖定。在作業結束時，如果建立非叢集索引的話，便會短時間取得來源的 S (共用) 鎖定；在線上建立或卸除叢集索引時，以及重建叢集或非叢集索引時，會取得 SCH-M (結構描述修改) 鎖定。建立本機暫存資料表的索引時，ONLINE 不能設為 ON。只允許使用單一執行緒的 HEAP 重建作業。
- OFF
  在索引作業期間，套用資料表鎖定。建立、重建或卸除叢集索引的離線索引作業，或重建或卸除非叢集索引的離線索引作業，會取得資料表的結構描述修改 (Sch-M) 鎖定。這可防止所有使用者在作業持續期間存取基礎資料表。建立非叢集索引的離線索引作業會取得資料表的共用 (S) 鎖定。這可防止對基礎資料表進行更新，但可允許讀取作業，如 SELECT 陳述式。允許使用多執行緒的 HEAP 重建作業。
如需詳細資訊，請參閱＜線上索引作業如何運作＞。如需有關鎖定的詳細資訊，請參閱＜鎖定模式＞。

[!附註]

SQL Server 的所有版本都無法使用線上索引作業。如需詳細資訊，請參閱＜SQL Server 2008 R2 版本支援的功能＞。
MOVE TO { partition_scheme_name(column_name [ 1, ... n]) | filegroup | "default" }
指定目前在叢集索引分葉層級中之資料列所要移往的位置。資料表會移至新位置。這個選項只適用於建立叢集索引的條件約束。

[!附註]

在這個內容中，default 不是關鍵字。它是預設檔案群組的識別碼，且必須加以分隔，如 MOVE TO "default" 或 MOVE TO [default]。如果指定了 "default"，目前工作階段的 QUOTED_IDENTIFIER 選項就必須是 ON。這是預設值。如需詳細資訊，請參閱＜SET QUOTED_IDENTIFIER (Transact-SQL)＞。
{ CHECK | NOCHECK } CONSTRAINT
指定 constraint_name 為已啟用或已停用。這個選項只能搭配 FOREIGN KEY 和 CHECK 條件約束使用。當指定 NOCHECK 時，會停用條件約束，且不會依照條件約束條件來驗證未來資料行的插入或更新作業。不能停用 DEFAULT、PRIMARY KEY 及 UNIQUE 條件約束。
ALL
指定利用 NOCHECK 選項停用所有條件約束，或利用 CHECK 選項啟用所有條件約束。
{ ENABLE | DISABLE } TRIGGER
指定 trigger_name 為已啟用或已停用。當停用觸發程序時，仍會針對資料表定義觸發程序；不過，當依照資料表執行 INSERT、UPDATE 或 DELETE 陳述式時，在重新啟用觸發程序之前，並不會執行觸發程序中的動作。
ALL
指定資料表中的所有觸發程序為已啟用或已停用。
trigger_name
指定要停用或啟用的觸發程序名稱。
{ ENABLE | DISABLE } CHANGE_TRACKING
指定資料表是要啟用還是停用變更追蹤。預設會停用變更追蹤。

只有當資料庫啟用了變更追蹤時，才能使用此選項。如需詳細資訊，請參閱＜ALTER DATABASE SET 選項 (Transact-SQL)＞。

若要啟用變更追蹤，資料表必須具有主索引鍵。
WITH ( TRACK_COLUMNS_UPDATED = { ON | OFF } )
指定 Database Engine 是否追蹤哪些啟用變更追蹤的資料行已更新。預設值為 OFF。
SWITCH [ PARTITION source_partition_number_expression ] TO [ schema_name.] target_table [ PARTITION target_ partition_number_expression ]
利用下列其中一種方式切換資料區塊：
- 將資料表的所有資料當做資料分割重新指派給已存在的資料分割資料表。
- 將資料分割從某一資料分割資料表切換到另一資料分割資料表。
- 將資料分割資料表之一個資料分割中的所有資料，重新指派給現有的非資料分割資料表。
如果 table 是資料分割資料表，則必須指定 source_partition_number_expression。如果 target_table 是資料分割資料表，則必須指定 target_partition_number_expression。如果將做為資料分割之資料表的資料重新指派給已存在的資料分割資料表，或將資料分割從某一資料分割資料表切換到另一資料分割資料表，則目標資料分割必須存在，且它必須是空的。

如果重新指派一個資料分割的資料來形成單一資料表，則目標資料表必須已經建立，且它必須是空的。來源資料表或資料分割和目標資料表或資料分割必須位於相同的檔案群組。相對應的索引或索引資料分割區也必須位於相同的檔案群組。切換資料分割還有許多其他限制。如需詳細資訊，請參閱＜使用資料分割切換有效傳送資料＞。table 和 target_table 不得相同。target_table 可以是多重部分識別碼。

source_partition_number_expression 和 target_partition_number_expression 是可以參考變數和函數的常數運算式。其中包括使用者定義型別變數和使用者定義函數。它們不能參考 Transact-SQL 運算式。

如需使用複寫時的 SWITCH 限制，請參閱＜複寫資料分割資料表及索引＞。
SET ( FILESTREAM_ON = { partition_scheme_name | filestream_filegroup_name | "default" | "NULL"} )
指定 FILESTREAM 資料存放的位置。

具有 SET FILESTREAM_ON 子句的 ALTER TABLE 只有在資料表沒有任何 FILESTREAM 資料行時才會成功。您可以使用第二個 ALTER TABLE 陳述式來加入 FILESTREAM 資料行。

如果指定了 partition_scheme_name，則會套用 CREATE TABLE 的規則。資料表應該已經針對資料列資料進行資料分割，且其資料分割配置所使用的資料分割函數和資料行必須與 FILESTREAM 資料分割配置相同。

filestream_filegroup_name 指定 FILESTREAM 檔案群組的名稱。此檔案群組必須有一個針對此檔案群組定義的檔案 (其方式是使用 CREATE DATABASE 或 ALTER DATABASE 陳述式)，否則會引發錯誤。

"default" 會使用 DEFAULT 屬性集指定 FILESTREAM 檔案群組。如果沒有任何 FILESTREAM 檔案群組，就會引發錯誤。

"NULL" 會指定將資料表的 FILESTREAM 檔案群組的所有參考移除。首先必須卸除所有的 FILESTREAM 資料行。您必須使用 SET FILESTREAM_ON**="NULL"** 來刪除所有與資料表相關聯的 FILESTREAM 資料。
SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )
指定資料表鎖定擴大的允許方法。
- AUTO
  此選項允許 SQL Server Database Engine 選取適用於資料表結構描述的鎖定擴大資料粒度。
  - 如果資料表是資料分割資料表，將允許鎖定擴大進行資料分割。當鎖定擴大為資料分割層級後，就無法進一步擴大為 TABLE 資料粒度。
  - 如果資料表不是資料分割，則會對 TABLE 資料粒度執行鎖定擴大。
- TABLE
  不論資料表是否為資料分割資料表，鎖定擴大將在資料表層級的資料粒度上完成。此行為與 SQL Server 2005 相同。TABLE 為預設值。
- DISABLE
  在大多數情況下都避免使用鎖定擴大，但並非完全不允許資料表層級的鎖定。例如，當您在可序列化隔離層級下掃描沒有任何叢集索引的資料表時，Database Engine 必須採用資料表鎖定以保護資料的完整性。
REBUILD
REBUILD WITH 語法可用來重建整個資料表，包括已資料分割之資料表中的所有資料分割。如果資料表有叢集索引，則 REBUILD 選項會重建叢集索引。REBUILD 可以執行為 ONLINE 作業。

REBUILD PARTITION 語法可用來重建已資料分割之資料表中的單一資料分割。
PARTITION = ALL
在變更資料分割壓縮設定時重建所有資料分割。
REBUILD WITH ( <rebuild_option> )
所有選項都適用於具有叢集索引的資料表。如果資料表沒有叢集索引，則只有其中一些選項會影響堆積結構。

當特定壓縮設定並非使用 REBUILD 作業來指定時，就會使用資料分割的目前壓縮設定。若要傳回目前設定，請查詢 sys.partitions 目錄檢視中的 data_compression 資料行。

如需重建選項的完整描述，請參閱＜index_option (Transact-SQL)＞。
DATA_COMPRESSION
針對指定的資料表、資料分割編號或資料分割範圍指定資料壓縮選項。選項如下：
- NONE
  不壓縮資料表或指定的資料分割。
- ROW
  使用資料列壓縮來壓縮資料表或指定的資料分割。
- PAGE
  使用頁面壓縮來壓縮資料表或指定的資料分割。
若要同時重建多個資料分割，請參閱＜index_option (Transact-SQL)＞。如果資料表沒有叢集索引，變更資料壓縮將會重建堆積和非叢集索引。如需有關壓縮的詳細資訊，請參閱＜建立壓縮資料表及索引＞和＜UNICODE 壓縮的概觀＞。
column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
這是資料行集的名稱。資料行集是不具類型的 XML 表示，可將資料表的所有疏鬆資料行結合到結構化輸出中。如果資料表已包含疏鬆資料行，資料行集無法加入該資料表中。如需有關資料行集的詳細資訊，請參閱＜使用資料行集＞。

重要事項
每當您改變資料行時，都必須指定 SPARSE 屬性，否則資料行將會還原為非疏鬆資料行。

備註

如果參考資料表的程序快取中有任何執行計畫，ALTER TABLE 會加以標示，以便在下一次執行時重新編譯。

變更資料行的大小

您可以在 ALTER COLUMN 子句中指定資料行資料類型的新大小，以變更資料行的長度、有效位數或小數位數。如果資料行中有資料存在，則新大小不得小於資料的最大大小。此外，除非資料行是 varchar、nvarchar 或 varbinary 資料類型，且索引不是 PRIMARY KEY 條件約束的結果，否則不能在索引中定義資料行。請參閱範例 P。

鎖定和 ALTER TABLE

ALTER TABLE 中指定的變更會立即實作。如果變更作業需要修改資料表中的資料列，ALTER TABLE 會更新資料列。ALTER TABLE 會取得資料表上的結構描述修改鎖定，以確定下列事項：在變更期間，除了在結束時需要非常短暫的 SCH-M 鎖定之線上索引作業以外，沒有其他連接會參考資料表的中繼資料。在 ALTER TABLE…SWITCH 作業中，不論在來源資料表或目標資料表上都會取得鎖定。對資料表所做的修改會記錄下來，且完全可復原。影響極大型資料表中之所有資料列的變更 (例如，卸除資料行或新增含有預設值的 NOT NULL 資料行) 可能需要很長的時間才能完成及產生多個記錄。執行這些 ALTER TABLE 陳述式時，要像執行任何影響多個資料列的 INSERT、UPDATE 或 DELETE 陳述式時一樣地小心。

平行計畫執行

在 SQL Server 2008 Enterprise 中，用來執行單一 ALTER TABLE ADD (以索引為依據) CONSTRAINT 或 DROP (叢集索引) CONSTRAINT 陳述式的處理器數目，取決於 max degree of parallelism 組態選項和目前的工作負載。如果 Database Engine 偵測到系統在忙碌中，則在開始執行陳述式之前，會先自動降低作業平行原則的程度。您可以指定 MAXDOP 選項，手動設定用來執行陳述式的處理器數目。

資料分割資料表

除了執行涉及資料分割資料表的 SWITCH 作業以外，ALTER TABLE 還可用來變更資料分割資料表之資料行、條件約束及觸發程序的狀態，就像它是用於非資料分割資料表一樣。不過，這個陳述式不可用來變更資料表本身的資料分割方式。若要重新分割某份資料分割資料表，請使用 ALTER PARTITION SCHEME 和 ALTER PARTITION FUNCTION。此外，您也不能變更資料分割資料表的資料行資料類型。

含有結構描述繫結檢視表之資料表的限制

適用於含有結構描述繫結檢視之資料表上的 ALTER TABLE 陳述式的限制，跟修改含有簡式索引之資料表時目前所適用的限制一樣。允許加入資料行。不過，不允許移除或變更參與任何結構描述繫結檢視表的資料行。如果 ALTER TABLE 陳述式需要變更結構描述繫結檢視中使用的資料行，ALTER TABLE 會失敗，且 Database Engine 會引發錯誤訊息。如需有關結構描述繫結和索引檢視表的詳細資訊，請參閱＜CREATE VIEW (Transact-SQL)＞。

建立參考資料表的結構描述繫結檢視，不會影響基底資料表上之觸發程序的新增或移除。

索引和 ALTER TABLE

當條件約束卸除時，建立為條件約束之一部分的索引也會卸除。必須利用 DROP INDEX 來卸除利用 CREATE INDEX 建立的索引。ALTER INDEX 陳述式可用來重建條件約束定義中索引的部分；不必卸除條件約束，然後又利用 ALTER TABLE 新增條件約束。

若要移除某資料行，必須先移除以該資料行為基礎的所有索引和條件約束。

刪除建立叢集索引的條件約束時，儲存在叢集索引分葉層級中的資料列會儲存在非叢集資料表中。您可以卸除叢集索引，再藉由指定 MOVE TO 選項，於單一交易中將結果資料表移到另一個檔案群組或資料分割配置。MOVE TO 選項有下列限制：

MOVE TO 對於索引檢視表或非叢集索引無效。
資料分割配置或檔案群組必須已經存在。
如果未指定 MOVE TO，資料表會放在針對叢集索引定義的相同資料分割配置或檔案群組中。

當您卸除叢集索引時，您可以指定 ONLINE = ON 選項，使 DROP INDEX 交易不會封鎖基礎資料和關聯之非叢集索引的查詢和修改。

ONLINE = ON 有下列限制：

ONLINE = ON 不適用於也已停用的叢集索引。停用的索引必須利用 ONLINE = OFF 來卸除。
一次只能卸除一個索引。
ONLINE = ON 不適用於索引檢視、非叢集索引或本機暫存資料表上的索引。

卸除叢集索引時，需要一個大小等於現有叢集索引的暫存磁碟空間。作業完成時，就會立即釋放這個額外空間。

[!附註]

<drop_clustered_constraint_option> 下方列出的選項可套用至資料表上的叢集索引，但不可套用至檢視表上的叢集索引或套用至非叢集索引。

複寫結構描述變更

根據預設，當您在 SQL Server 發行者的已發行資料表上執行 ALTER TABLE 時，該項變更就會傳播到所有 SQL Server 訂閱者。這項功能具有某些限制，而且可停用。如需詳細資訊，請參閱＜對發行集資料庫進行結構描述變更＞。

資料壓縮

系統資料表無法啟用壓縮。如果資料表是堆積，ONLINE 模式的重建作業將會是單一執行緒。請針對多執行緒的堆積重建作業使用 OFFLINE 模式。如需有關資料壓縮的詳細資訊，請參閱＜建立壓縮資料表及索引＞。

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

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

您無法在資料表具有非對齊索引時變更單一資料分割的壓縮設定。
ALTER TABLE <table> REBUILD PARTITION ... 語法會重建指定的資料分割。
ALTER TABLE <table> REBUILD WITH ... 語法會重建所有資料分割。

權限

需要資料表的 ALTER 權限。

ALTER TABLE 權限可套用至涉及 ALTER TABLE SWITCH 陳述式的兩種資料表。所切換的任何資料，都會繼承目標資料表的安全性。

如果將 ALTER TABLE 陳述式中的任何資料行定義為屬於 Common Language Runtime (CLR) 使用者定義型別或別名資料類型，則需要該類型的 REFERENCES 權限。

範例

A. 加入新資料行

下列範例會加入一個資料行，該資料行允許 Null 值，且不含利用 DEFAULT 定義提供的值。在這個新資料行中，每一個資料列都會有 NULL。

CREATE TABLE dbo.doc_exa (column_a INT) ;
GO
ALTER TABLE dbo.doc_exa ADD column_b VARCHAR(20) NULL ;
GO
EXEC sp_help doc_exa ;
GO
DROP TABLE dbo.doc_exa ;
GO

B. 卸除資料行

下列範例會修改資料表來移除資料行。

CREATE TABLE dbo.doc_exb (column_a INT, column_b VARCHAR(20) NULL) ;
GO
ALTER TABLE dbo.doc_exb DROP COLUMN column_b ;
GO
EXEC sp_help doc_exb ;
GO
DROP TABLE dbo.doc_exb ;
GO

C. 變更資料行的資料類型

下列範例會將資料表的資料行從 INT 變更為 DECIMAL。

CREATE TABLE dbo.doc_exy (column_a INT ) ;
GO
INSERT INTO dbo.doc_exy (column_a) VALUES (10) ;
GO
ALTER TABLE dbo.doc_exy ALTER COLUMN column_a DECIMAL (5, 2) ;
GO
DROP TABLE dbo.doc_exy ;
GO

D. 加入含有條件約束的資料行

下列範例會加入一個含有 UNIQUE 條件約束的新資料行。

CREATE TABLE dbo.doc_exc (column_a INT) ;
GO
ALTER TABLE dbo.doc_exc ADD column_b VARCHAR(20) NULL 
    CONSTRAINT exb_unique UNIQUE ;
GO
EXEC sp_help doc_exc ;
GO
DROP TABLE dbo.doc_exc ;
GO

E. 將未確認的 CHECK 條件約束加入至現有的資料行

下列範例將條件約束加入至資料表中的現有資料行。該資料行含有違反該條件約束的值。因此，WITH NOCHECK 可用來防止根據現有的資料列來驗證條件約束，並允許新增條件約束。

CREATE TABLE dbo.doc_exd ( column_a INT) ;
GO
INSERT INTO dbo.doc_exd VALUES (-1) ;
GO
ALTER TABLE dbo.doc_exd WITH NOCHECK 
ADD CONSTRAINT exd_check CHECK (column_a > 1) ;
GO
EXEC sp_help doc_exd ;
GO
DROP TABLE dbo.doc_exd ;
GO

F. 將 DEFAULT 條件約束加入至現有的資料行

下列範例會建立一份含有兩個資料行的資料表、在第一個資料行插入值，並讓另一個資料行保持 NULL。然後再將 DEFAULT 條件約束加入至第二個資料行。若要確認已套用預設值，請在第一個資料行中插入其他值，然後查詢資料表。

CREATE TABLE dbo.doc_exz ( column_a INT, column_b INT) ;
GO
INSERT INTO dbo.doc_exz (column_a)VALUES ( 7 ) ;
GO
ALTER TABLE dbo.doc_exz
ADD CONSTRAINT col_b_def
DEFAULT 50 FOR column_b ;
GO
INSERT INTO dbo.doc_exz (column_a) VALUES ( 10 ) ;
GO
SELECT * FROM dbo.doc_exz ;
GO
DROP TABLE dbo.doc_exz ;
GO

G. 加入數個含有條件約束的資料行

下列範例會加入數個資料行，這些資料行含有利用新資料行定義的條件約束。第一個新資料行有 IDENTITY 屬性。資料表中每一個資料列在識別欄位中都有新的累加值。

CREATE TABLE dbo.doc_exe ( column_a INT CONSTRAINT column_a_un UNIQUE) ;
GO
ALTER TABLE dbo.doc_exe ADD 

-- Add a PRIMARY KEY identity column.
column_b INT IDENTITY
CONSTRAINT column_b_pk PRIMARY KEY, 

-- Add a column that references another column in the same table.
column_c INT NULL  
CONSTRAINT column_c_fk 
REFERENCES doc_exe(column_a),

-- Add a column with a constraint to enforce that 
-- nonnull data is in a valid telephone number format.
column_d VARCHAR(16) NULL 
CONSTRAINT column_d_chk
CHECK 
(column_d LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]' OR
column_d LIKE
'([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'),

-- Add a nonnull column with a default.
column_e DECIMAL(3,3)
CONSTRAINT column_e_default
DEFAULT .081 ;
GO
EXEC sp_help doc_exe ;
GO
DROP TABLE dbo.doc_exe ;
GO

H. 加入含有預設值且可為 Null 的資料行

下列範例會加入含有 DEFAULT 定義且可為 Null 的資料行，並利用 WITH VALUES 為資料表中的每一個現有資料列提供值。如果沒有使用 WITH VALUES，每一個資料列的新資料行中都會有 NULL 值。

USE AdventureWorks2008R2 ; 
GO
CREATE TABLE dbo.doc_exf ( column_a INT) ;
GO
INSERT INTO dbo.doc_exf VALUES (1) ;
GO
ALTER TABLE dbo.doc_exf 
ADD AddDate smalldatetime NULL
CONSTRAINT AddDateDflt
DEFAULT GETDATE() WITH VALUES ;
GO
DROP TABLE dbo.doc_exf ;
GO

I. 停用及重新啟用條件約束

下列範例會停用一個用來限制資料中可接受之薪資的條件約束。NOCHECK CONSTRAINT 可搭配 ALTER TABLE 使用來停用條件約束，並允許通常會違反此條件約束的插入作業。CHECK CONSTRAINT 會重新啟用此條件約束。

CREATE TABLE dbo.cnst_example 
(id INT NOT NULL,
 name VARCHAR(10) NOT NULL,
 salary MONEY NOT NULL
    CONSTRAINT salary_cap CHECK (salary < 100000)
);

-- Valid inserts
INSERT INTO dbo.cnst_example VALUES (1,'Joe Brown',65000);
INSERT INTO dbo.cnst_example VALUES (2,'Mary Smith',75000);

-- This insert violates the constraint.
INSERT INTO dbo.cnst_example VALUES (3,'Pat Jones',105000);

-- Disable the constraint and try again.
ALTER TABLE dbo.cnst_example NOCHECK CONSTRAINT salary_cap;
INSERT INTO dbo.cnst_example VALUES (3,'Pat Jones',105000);

-- Re-enable the constraint and try another insert; this will fail.
ALTER TABLE dbo.cnst_example CHECK CONSTRAINT salary_cap;
INSERT INTO dbo.cnst_example VALUES (4,'Eric James',110000) ;

J. 卸除條件約束

下列範例會從資料表中移除 UNIQUE 條件約束。

CREATE TABLE dbo.doc_exc ( column_a INT
CONSTRAINT my_constraint UNIQUE) ;
GO
ALTER TABLE dbo.doc_exc DROP CONSTRAINT my_constraint ;
GO
DROP TABLE dbo.doc_exc ;
GO

K. 在資料表之間切換資料分割

下列範例會建立資料分割資料表，並假設資料庫中已經建立資料分割配置 myRangePS1。接著，利用與資料分割資料表相同的結構，在與資料表 PartitionTable 的 PARTITION 2 相同的檔案群組上建立非資料分割資料表。然後，資料表 PartitionTable 之 PARTITION 2 的資料就會切換到資料表 NonPartitionTable 中。

CREATE TABLE PartitionTable (col1 int, col2 char(10))
ON myRangePS1 (col1) ;
GO
CREATE TABLE NonPartitionTable (col1 int, col2 char(10))
ON test2fg ;
GO
ALTER TABLE PartitionTable SWITCH PARTITION 2 TO NonPartitionTable ;
GO

L. 停用及重新啟用觸發程序

下列範例會利用 ALTER TABLE 的 DISABLE TRIGGER 選項來停用觸發程序，並允許通常會違反此觸發程序規定的插入作業。然後，再利用 ENABLE TRIGGER 來重新啟用此觸發程序。

CREATE TABLE dbo.trig_example 
(id INT, 
name VARCHAR(12),
salary MONEY) ;
GO
-- Create the trigger.
CREATE TRIGGER dbo.trig1 ON dbo.trig_example FOR INSERT
AS
IF (SELECT COUNT(*) FROM INSERTED
WHERE salary > 100000) > 0
BEGIN
    print 'TRIG1 Error: you attempted to insert a salary > $100,000'
    ROLLBACK TRANSACTION
END ;
GO
-- Try an insert that violates the trigger.
INSERT INTO dbo.trig_example VALUES (1,'Pat Smith',100001) ;
GO
-- Disable the trigger.
ALTER TABLE dbo.trig_example DISABLE TRIGGER trig1 ;
GO
-- Try an insert that would typically violate the trigger.
INSERT INTO dbo.trig_example VALUES (2,'Chuck Jones',100001) ;
GO
-- Re-enable the trigger.
ALTER TABLE dbo.trig_example ENABLE TRIGGER trig1 ;
GO
-- Try an insert that violates the trigger.
INSERT INTO dbo.trig_example VALUES (3,'Mary Booth',100001) ;
GO

M. 建立含有索引選項的 PRIMARY KEY 條件約束

下列範例會建立 PRIMARY KEY 條件約束 PK_TransactionHistoryArchive_TransactionID，並設定選項 FILLFACTOR、ONLINE 及 PAD_INDEX。產生的叢集索引將與條件約束同名。

USE AdventureWorks2008R2;
GO
ALTER TABLE Production.TransactionHistoryArchive WITH NOCHECK 
ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID)
WITH (FILLFACTOR = 75, ONLINE = ON, PAD_INDEX = ON);
GO

N. 在 ONLINE 模式中卸除 PRIMARY KEY 條件約束

下列範例會刪除 PRIMARY KEY 條件約束，並將 ONLINE 選項設為 ON。

USE AdventureWorks2008R2;
GO
ALTER TABLE Production.TransactionHistoryArchive
DROP CONSTRAINT PK_TransactionHistoryArchive_TransactionID
WITH (ONLINE = ON);
GO

O. 加入或卸除 FOREIGN KEY 條件約束

下列範例會建立資料表 ContactBackup，然後改變該資料表，方式如下：先新增一個參考 Person 資料表的 FOREIGN KEY 條件約束，再卸除 FOREIGN KEY 條件約束。

USE AdventureWorks2008R2 ;
GO
CREATE TABLE Person.ContactBackup
(ContactID int) ;
GO
ALTER TABLE Person.ContactBackup
ADD CONSTRAINT FK_ContactBacup_Contact FOREIGN KEY (ContactID)
    REFERENCES Person.Person (BusinessEntityID) ;
ALTER TABLE Person.ContactBackup
DROP CONSTRAINT FK_ContactBacup_Contact ;
GO
DROP TABLE Person.ContactBackup ;

P. 變更資料行的大小

下列範例會增加 varchar 資料行的大小，以及 decimal 資料行的有效位數和小數位數。因為資料行包含資料，所以資料行大小只能增加。另請注意，col_a 會定義於唯一索引中。col_a 的大小仍可增加，因為資料類型是 varchar，且索引不是 PRIMARY KEY 條件約束的結果。

IF OBJECT_ID ( 'dbo.doc_exy', 'U' ) IS NOT NULL 
    DROP TABLE dbo.doc_exy;
GO
-- Create a two-column table with a unique index on the varchar column.
CREATE TABLE dbo.doc_exy ( col_a varchar(5) UNIQUE NOT NULL, col_b decimal (4,2));
GO
INSERT INTO dbo.doc_exy VALUES ('Test', 99.99);
GO
-- Verify the current column size.
SELECT name, TYPE_NAME(system_type_id), max_length, precision, scale
FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.doc_exy');
GO
-- Increase the size of the varchar column.
ALTER TABLE dbo.doc_exy ALTER COLUMN col_a varchar(25);
GO
-- Increase the scale and precision of the decimal column.
ALTER TABLE dbo.doc_exy ALTER COLUMN col_b decimal (10,4);
GO
-- Insert a new row.
INSERT INTO dbo.doc_exy VALUES ('MyNewColumnSize', 99999.9999) ;
GO
-- Verify the current column size.
SELECT name, TYPE_NAME(system_type_id), max_length, precision, scale
FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.doc_exy');

Q. 可以在資料分割資料表上進行鎖定擴大

下列範例會在資料分割資料表上啟用資料分割層級的鎖定擴大。如果資料表不是資料分割資料表，則鎖定擴大會在 TABLE 層級進行。

ALTER TABLE T1 SET (LOCK_ESCALATION = AUTO);
GO

R. 在資料表上設定變更追蹤

下列範例會在 AdventureWorks2008R2 資料庫中啟用 Person.Person 資料表的變更追蹤。

USE AdventureWorks2008R2;
ALTER TABLE Person.Person
ENABLE CHANGE_TRACKING;

下列範例會啟用變更追蹤，並為變更期間更新的資料行啟用追蹤。

USE AdventureWorks2008R2;
ALTER TABLE Person.Person
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);

下列範例會在 AdventureWorks2008R2 資料庫中停用 Person.Person 資料表的變更追蹤。

USE AdventureWorks2008R2;
ALTER TABLE Person.Person
DISABLE CHANGE_TRACKING;

S. 修改資料表來變更壓縮

下列範例會變更非資料分割資料表的壓縮。堆積或叢集索引將會重建。如果資料表為堆積，則所有非叢集索引將會重建。

ALTER TABLE T1 
REBUILD WITH (DATA_COMPRESSION = PAGE);

下列範例會變更資料分割資料表的壓縮。REBUILD PARTITION = 1 語法只會造成資料分割號碼 1 的重建。

ALTER TABLE PartitionTable1 
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION =  NONE) ;
GO

使用下列替代語法的相同作業會造成資料表內所有資料分割的重建。

ALTER TABLE PartitionTable1 
REBUILD PARTITION = ALL 
WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1) ) ;

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

T. 加入疏鬆資料行

下列範例示範如何在資料表 T1 中加入及修改疏鬆資料行。建立資料表 T1 的程式碼如下。

CREATE TABLE T1
(C1 int PRIMARY KEY,
C2 varchar(50) SPARSE NULL,
C3 int SPARSE NULL,
C4 int ) ;
GO

若要加入其他的疏鬆資料行 C5，請執行下列陳述式。

ALTER TABLE T1
ADD C5 char(100) SPARSE NULL ;
GO

若要將 C4 非疏鬆資料行轉換成疏鬆資料行，請執行下列陳述式。

ALTER TABLE T1
ALTER COLUMN C4 ADD SPARSE ;
GO

若要將 C4 疏鬆資料行轉換成非疏鬆資料行，請執行下列陳述式。

ALTER TABLE T1
ALTER COLUMN C4 DROP SPARSE;
GO

U. 加入資料行集

下列範例示範如何將資料行加入至資料表 T2。如果資料表已包含疏鬆資料行，就無法在該資料表中加入資料行集。建立資料表 T2 的程式碼如下。

CREATE TABLE T2
(C1 int PRIMARY KEY,
C2 varchar(50) NULL,
C3 int NULL,
C4 int ) ;
GO

下列三個陳述式會加入名為 CS 的資料行集，然後將資料行 C2 和 C3 修改為 SPARSE。

ALTER TABLE T2
ADD CS XML COLUMN_SET FOR ALL_SPARSE_COLUMNS ;
GO

ALTER TABLE T2
ALTER COLUMN C2 ADD SPARSE ; 
GO

ALTER TABLE T2
ALTER COLUMN C3 ADD SPARSE ;
GO

V. 變更資料行定序

下列範例將示範如何變更資料行的定序。首先，建立包含預設使用者定序的資料表 T3：

CREATE TABLE T3
(C1 int PRIMARY KEY,
C2 varchar(50) NULL,
C3 int NULL,
C4 int ) ;
GO

接著，將資料行 C2 定序變更為 Latin1_General_BIN。請注意，資料類型即使不會變更，但卻是必要的。

ALTER TABLE T3
ALTER COLUMN C2 varchar(50) COLLATE Latin1_General_BIN 
GO

請參閱

共用方式為