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 表示，可將資料表的所有疏鬆資料行結合到結構化輸出中。如果資料表已包含疏鬆資料行，資料行集無法加入該資料表中。如需有關資料行集的詳細資訊，請參閱＜使用資料行集＞。

備註

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

變更資料行的大小

鎖定和 ALTER TABLE

平行計畫執行

資料分割資料表

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

索引和 ALTER TABLE

複寫結構描述變更

資料壓縮

權限

需要資料表的 ALTER 權限。

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

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

範例

A. 加入新資料行

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. 變更資料行的資料類型

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. 加入含有條件約束的資料行

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 條件約束加入至現有的資料行

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 條件約束加入至現有的資料行

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. 加入數個含有條件約束的資料行

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 的資料行

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. 停用及重新啟用條件約束

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. 卸除條件約束

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. 在資料表之間切換資料分割

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. 停用及重新啟用觸發程序

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 條件約束

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 條件約束

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

O. 加入或卸除 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. 變更資料行的大小

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. 可以在資料分割資料表上進行鎖定擴大

ALTER TABLE T1 SET (LOCK_ESCALATION = AUTO);
GO

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

USE AdventureWorks2008R2;
ALTER TABLE Person.Person
ENABLE CHANGE_TRACKING;

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

USE AdventureWorks2008R2;
ALTER TABLE Person.Person
DISABLE CHANGE_TRACKING;

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

ALTER TABLE T1 
REBUILD WITH (DATA_COMPRESSION = PAGE);

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. 加入疏鬆資料行

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

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

ALTER TABLE T1
ALTER COLUMN C4 ADD SPARSE ;
GO

ALTER TABLE T1
ALTER COLUMN C4 DROP SPARSE;
GO

U. 加入資料行集

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

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. 變更資料行定序

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

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

請參閱

參考

概念

其他資源