ALTER TABLE (Transact-SQL)

發行項
12/15/2008

更新： 2007 年 9 月 15 日

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

語法

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 ] 
    | {ADD | DROP } { ROWGUIDCOL | PERSISTED | NOT FOR REPLICATION}
    } 
    | [ WITH { CHECK | NOCHECK } ] ADD 
    { 
        <column_definition>
      | <computed_column_definition>
      | <table_constraint> 
    } [ ,...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 ] }
    | SWITCH [ PARTITION source_partition_number_expression ]
        TO target_table 
        [ PARTITION target_partition_number_expression ]
}
[ ; ]

<drop_clustered_constraint_option> ::=  
    { 
        MAXDOP = max_degree_of_parallelism
      | ONLINE = {ON | OFF }
      | MOVE TO { partition_scheme_name ( column_name ) | filegroup
          | "default"}
    }

引數

database_name
這是建立資料表的資料庫名稱。

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

table_name
這是要變更的資料表名稱。如果資料表不在目前資料庫中，或未包含在目前使用者擁有的結構描述內，則必須明確指定該資料庫和結構描述。

ALTER COLUMN
指定將要變更或改變的具名資料行。如果相容性層級為 65 或更低，則不允許使用 ALTER COLUMN。如需詳細資訊，請參閱＜sp_dbcmptlevel (Transact-SQL)＞。

修改過的資料行不得為下列任何一項：
- 資料類型為 timestamp 的資料行。
- 資料表的 ROWGUIDCOL。
- 計算資料行，或用於計算資料行。
- 用於索引時，除非資料行是 varchar、nvarchar 或 varbinary 資料類型，否則，不會變更資料類型；新大小會等於或大於舊大小；而且該索引不會是 PRIMARY KEY 條件約束的結果。
- 在 CREATE STATISTICS 陳述式產生的統計資料中使用。首先，利用 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 2005 系統資料類型。
- 基於 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 子句，變更資料行的資料類型會使資料庫的預設定序發生定序變更。

附註：
如果未指定 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 定序名稱 (Transact-SQL)＞。

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

如果存在下列一或多個條件，ALTER COLUMN 不能有定序變更：
- 如果 CHECK 條件約束、FOREIGN KEY 條件約束或計算資料行參考變更的資料行。
- 如果在資料行上建立任何索引、統計資料或全文檢索索引。如果資料行定序變更了，在變更的資料行上自動建立的統計資料就會卸除。
- 如果結構描述繫結檢視或函數參考資料行。
如需詳細資訊，請參閱＜COLLATE (Transact-SQL)＞。

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 的資料行而言，當計算資料行相依的任何其他資料行更新時，SQL Server 2005 Database Engine 實際上會將計算值儲存在資料表並將值更新。將計算資料行標示計算資料行為 PERSISTED，就可以在定義於具決定性 (但不是精確) 運算式上的計算資料行上建立索引。如需詳細資訊，請參閱＜在計算資料行上建立索引＞。

當做資料分割資料表之資料分割資料行的任何計算資料行，都必須明確標示為 PERSISTED。

NOT FOR REPLICATION
指定當複寫代理程式執行插入作業時，值不會在識別資料行中累加。只有當 column_name 是識別資料行時，才能指定這個子句。如需詳細資訊，請參閱＜使用 NOT FOR REPLICATION 控制條件約束、識別和觸發程序＞。

WITH CHECK | WITH NOCHECK
指定是否要依照新加入或重新啟用的 FOREIGN KEY 或 CHECK 條件約束來驗證資料表中的資料。如果未指定，則假設 WITH CHECK 為新條件約束，並假設 WITH NOCHECK 為重新啟用的條件約束。

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

查詢最佳化工具不考量定義為 WITH NOCHECK 的條件約束。這類條件約束會被忽略，直到利用 ALTER TABLE table CHECK CONSTRAINT ALL 重新啟用為止。

ADD
指定新增一或多個資料行定義、計算資料行定義或資料表條件約束。

DROP { [ CONSTRAINT ] constraint_name | COLUMN column_name }
指定從資料表移除 constraint_name 或 column_name。可以列出多個資料行和條件約束。如果相容性層級為 65 或更舊，則不允許使用 DROP COLUMN。如需詳細資訊，請參閱＜sp_dbcmptlevel (Transact-SQL)＞。

您可以查詢 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 2005 Enterprise Edition 才支援平行索引作業。

附註：
只有 SQL Server 2005 Enterprise Edition 才支援平行索引作業。

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

附註：

只有 SQL Server 2005 Enterprise Edition 能夠使用線上索引作業。

附註：
只有 SQL Server 2005 Enterprise Edition 能夠使用線上索引作業。

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
指定要啟用或啟用的觸發程序名稱。

SWITCH [ PARTITION source_partition_number_expression ] TO 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 陳述式。

附註：
您不能在複寫的資料表上使用 SWITCH 陳述式。

備註

若要加入新的資料列，請使用 INSERT。若要移除資料列，請使用 DELETE 或 TRUNCATE TABLE。若要變更現有資料列中的值，請使用 UPDATE。

如果參考資料表的程序快取中有任何執行計劃，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 2005 Enterprise Edition 中，用來執行單一 ALTER TABLE ADD (以索引為依據) CONSTRAINT 或 DROP (叢集索引) CONSTRAINT 陳述式的處理器數目，取決於平行處理原則的最大程度組態選項和目前的工作負載。如果 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 新增條件約束。

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

刪除建立叢集索引的條件約束時，儲存在叢集索引分葉層級中的資料列會儲存在非叢集資料表中。在 SQL Server 2005 中，您可以卸除叢集索引，以及指定 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 訂閱者。這項功能具有某些限制，而且可停用。如需詳細資訊，請參閱＜對發行集資料庫進行結構描述變更＞。

權限

需要資料表的 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 AdventureWorks ; 
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 AdventureWorks;
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 AdventureWorks;
GO
ALTER TABLE Production.TransactionHistoryArchive
DROP CONSTRAINT PK_TransactionHistoryArchive_TransactionID
WITH (ONLINE = ON);
GO

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

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

USE AdventureWorks ;
GO
CREATE TABLE Person.ContactBackup
(ContactID int) ;
GO
ALTER TABLE Person.ContactBackup
ADD CONSTRAINT FK_ContactBacup_Contact FOREIGN KEY (ContactID)
    REFERENCES Person.Contact (ContactID) ;
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');

請參閱

變更歷程記錄

版本	歷程記錄
2007 年 9 月 15 日	變更的內容：新增「變更資料行的大小」一節和「範例 P」。
2006 年 12 月 12 日	變更的內容：在語法與引數章節中，釐清 NOT FOR REPLICATION 子句的位置和意義。釐清 SWITCH 子句的目標資料表可表示成多重部分識別碼。
2006 年 4 月 14 日	新增內容：記載您不能在複寫的資料表上使用 SWITCH 陳述式。
2005 年 12 月 5 日	新增內容：在語法圖和「引數」定義清單中加入 DROP NOT FOR REPLICATION 子句。變更的內容：將 COLLATE 子句移到語法圖中的正確位置。修正範例 M 和 N。