ALTER TABLE (Transact-SQL)ALTER TABLE (Transact-SQL)

適用於: 是SQL Server (從 2008 開始) 是Azure SQL Database 是Azure SQL 資料倉儲 是平行處理資料倉儲 APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

您可以利用改變、加入或卸除資料行與條件約束、重新指派和重建分割區,或是停用或啟用條件約束與觸發程序等方式來修改資料表定義。Modifies a table definition by altering, adding, or dropping columns and constraints, reassigning and rebuilding partitions, or disabling or enabling constraints and triggers.

如需語法慣例的詳細資訊,請參閱 Transact-SQL 語法慣例For more information about the syntax conventions, see Transact-SQL Syntax Conventions.

重要

磁碟型資料表與記憶體最佳化資料表的 ALTER TABLE 語法不同。The syntax for ALTER TABLE is different for disk-based tables and memory-optimized tables. 請使用下列連結來直接前往適合您資料表類型的語法區塊,以及前往適當的語法範例:Use the following links to take you directly to the appropriate syntax block for your table types and to the appropriate syntax examples:

磁碟型資料表的語法Syntax for disk-based tables

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 | HIDDEN }  
      | { ADD | DROP } MASKED [ WITH ( FUNCTION = ' mask_function ') ]  
    }
    [ WITH ( ONLINE = ON | OFF ) ]  
    | [ WITH { CHECK | NOCHECK } ]  
  
    | ADD
    {
        <column_definition>  
      | <computed_column_definition>  
      | <table_constraint>
      | <column_set_definition>
    } [ ,...n ]  
      | [ system_start_time_column_name datetime2 GENERATED ALWAYS AS ROW START
                [ HIDDEN ] [ NOT NULL ] [ CONSTRAINT constraint_name ]
            DEFAULT constant_expression [WITH VALUES] ,  
                system_end_time_column_name datetime2 GENERATED ALWAYS AS ROW END
                   [ HIDDEN ] [ NOT NULL ]  [ CONSTRAINT constraint_name ]
            DEFAULT constant_expression [WITH VALUES] ,  
        ]  
       PERIOD FOR SYSTEM_TIME ( system_start_time_column_name, system_end_time_column_name )  
    | DROP
     [ {  
         [ CONSTRAINT ]  [ IF EXISTS ]  
         {
              constraint_name
              [ WITH
               ( <drop_clustered_constraint_option> [ ,...n ] )
              ]
          } [ ,...n ]  
          | COLUMN  [ IF EXISTS ]  
          {  
              column_name
          } [ ,...n ]  
          | PERIOD FOR SYSTEM_TIME  
     } [ ,...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 ]  
        [ WITH ( <low_priority_lock_wait> ) ]  

    | SET
        (  
            [ FILESTREAM_ON =
                { partition_scheme_name | filegroup | "default" | "NULL" } ]  
            | SYSTEM_VERSIONING =
                  {
                      OFF
                  | ON
                      [ ( HISTORY_TABLE = schema_name . history_table_name
                          [, DATA_CONSISTENCY_CHECK = { ON | OFF } ]
                          [, HISTORY_RETENTION_PERIOD =
                          {
                              INFINITE | number {DAY | DAYS | WEEK | WEEKS
                  | MONTH | MONTHS | YEAR | YEARS }
                          }
                          ]  
                        )  
                      ]  
                  }  
          )  

    | REBUILD
      [ [PARTITION = ALL]  
        [ WITH ( <rebuild_option> [ ,...n ] ) ]
      | [ PARTITION = partition_number
           [ WITH ( <single_partition_rebuild_option> [ ,...n ] ) ]  
        ]  
      ]  
  
    | <table_option>  
    | <filetable_option>  
    | <stretch_configuration>  
}  
[ ; ]  
  
-- ALTER TABLE options  
  
<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 } )  
    }  
  
<filetable_option> ::=  
    {  
       [ { ENABLE | DISABLE } FILETABLE_NAMESPACE ]  
       [ SET ( FILETABLE_DIRECTORY = directory_name ) ]  
    }  
  
<stretch_configuration> ::=  
    {  
      SET (  
        REMOTE_DATA_ARCHIVE
        {  
            = ON (  <table_stretch_options>  )  
          | = OFF_WITHOUT_DATA_RECOVERY ( MIGRATION_STATE = PAUSED )  
          | ( <table_stretch_options> [, ...n] )  
        }  
            )  
    }  
  
<table_stretch_options> ::=  
    {  
     [ FILTER_PREDICATE = { null | table_predicate_function } , ]  
       MIGRATION_STATE = { OUTBOUND | INBOUND | PAUSED }  
    }  
  
<single_partition_rebuild__option> ::=  
{  
      SORT_IN_TEMPDB = { ON | OFF }  
    | MAXDOP = max_degree_of_parallelism  
    | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE} }  
    | ONLINE = { ON [( <low_priority_lock_wait> ) ] | OFF }  
}  
  
<low_priority_lock_wait>::=  
{  
    WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time> [ MINUTES ], 
        ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } )   
}  

記憶體最佳化資料表的語法Syntax for memory-optimized tables

ALTER TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name   
{   
    ALTER COLUMN column_name   
    {   
        [ type_schema_name. ] type_name   
            [ (   
                {   
                   precision [ , scale ]   
                }   
            ) ]   
        [ COLLATE collation_name ]   
        [ NULL | NOT NULL ] 
    }  

    | ALTER INDEX index_name   
    {   
        [ type_schema_name. ] type_name   
        REBUILD   
        [ [ NONCLUSTERED ] WITH ( BUCKET_COUNT = bucket_count )
        ]  
    }  
    
    | ADD   
    {   
        <column_definition>  
      | <computed_column_definition>  
      | <table_constraint> 
      | <table_index>
      | <column_index>
    } [ ,...n ]  
      | [ system_start_time_column_name datetime2 GENERATED ALWAYS AS ROW START   
                   [ HIDDEN ] [ NOT NULL ] [ CONSTRAINT constraint_name ] 
           DEFAULT constant_expression [WITH VALUES] ,  
            system_end_time_column_name datetime2 GENERATED ALWAYS AS ROW END   
                   [ HIDDEN ] [ NOT NULL ]  [ CONSTRAINT constraint_name ] 
           DEFAULT constant_expression [WITH VALUES] ,  
         ]  
       PERIOD FOR SYSTEM_TIME ( system_start_time_column_name, system_end_time_column_name )  
       
    | DROP   
     [ {  
         CONSTRAINT  [ IF EXISTS ]  
         {   
              constraint_name   
          } [ ,...n ]  
      | INDEX  [ IF EXISTS ] 
      {
          index_name
      } [ ,...n ]
          | COLUMN  [ IF EXISTS ]  
          {  
              column_name   
          } [ ,...n ]  
          | PERIOD FOR SYSTEM_TIME  
     } [ ,...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 ]  
        [ WITH ( <low_priority_lock_wait> ) ]  
    
    | SET   
        (  
            SYSTEM_VERSIONING =   
                  {   
                      OFF   
                  | ON   
                      [ ( HISTORY_TABLE = schema_name . history_table_name   
                          [, DATA_CONSISTENCY_CHECK = { ON | OFF } ] 
                          [, HISTORY_RETENTION_PERIOD = 
                          { 
                               INFINITE | number {DAY | DAYS | WEEK | WEEKS 
                 | MONTH | MONTHS | YEAR | YEARS } 
                          } 
                          ]  
                        )  
                      ]  
                  }  
          )  
      
    | <table_option>    
}  
[ ; ]  
  
-- ALTER TABLE options  
  
< table_constraint > ::=  
 [ CONSTRAINT constraint_name ]  
{    
   { PRIMARY KEY | UNIQUE }  
     {   
       NONCLUSTERED (column [ ASC | DESC ] [ ,... n ])  
       | NONCLUSTERED HASH (column [ ,... n ] ) WITH ( BUCKET_COUNT = bucket_count )   
                    }   
    | FOREIGN KEY   
        ( column [ ,...n ] )   
        REFERENCES referenced_table_name [ ( ref_column [ ,...n ] ) ]   
    | CHECK ( logical_expression )   
}  

<column_index> ::=  
  INDEX index_name  
{ [ NONCLUSTERED ] | [ NONCLUSTERED ] HASH WITH (BUCKET_COUNT = bucket_count)  }  

<table_index> ::=  
  INDEX index_name  
{   [ NONCLUSTERED ] HASH (column [ ,... n ] ) WITH (BUCKET_COUNT = bucket_count)   
  | [ NONCLUSTERED ] (column [ ASC | DESC ] [ ,... n ] )   
      [ ON filegroup_name | default ]  
  | CLUSTERED COLUMNSTORE [WITH ( COMPRESSION_DELAY = {0 | delay [Minutes]})]  
      [ ON filegroup_name | default ]   
}  

<table_option> ::=  
{  
    MEMORY_OPTIMIZED = ON   
  | DURABILITY = {SCHEMA_ONLY | SCHEMA_AND_DATA}  
  | SYSTEM_VERSIONING = ON [ ( HISTORY_TABLE = schema_name . history_table_name  
        [, DATA_CONSISTENCY_CHECK = { ON | OFF } ] ) ]   
}  
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse  
  
ALTER TABLE [ database_name . [schema_name ] . | schema_name. ] source_table_name   
{  
    ALTER COLUMN column_name  
        {   
            type_name [ ( precision [ , scale ] ) ]   
            [ COLLATE Windows_collation_name ]   
            [ NULL | NOT NULL ]   
        }  
    | ADD { <column_definition> | <column_constraint> FOR column_name} [ ,...n ]  
    | DROP { COLUMN column_name | [CONSTRAINT] constraint_name } [ ,...n ]  
    | REBUILD {  
            [ PARTITION = ALL [ WITH ( <rebuild_option> ) ] ] 
          | [ PARTITION = partition_number [ WITH ( <single_partition_rebuild_option> ] ]
      } 
    | { SPLIT | MERGE } RANGE (boundary_value)  
    | SWITCH [ PARTITION source_partition_number  
        TO target_table_name [ PARTITION target_partition_number ] [ WITH ( TRUNCATE_TARGET_PARTITION = ON | OFF )
}  
[;]  
  
<column_definition>::=  
{  
    column_name  
    type_name [ ( precision [ , scale ] ) ]   
    [ <column_constraint> ]  
    [ COLLATE Windows_collation_name ]  
    [ NULL | NOT NULL ]  
}  
  
<column_constraint>::=  
    [ CONSTRAINT constraint_name ] DEFAULT constant_expression  

<rebuild_option > ::=   
{  
    DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }
        [ ON PARTITIONS ( {<partition_number> [ TO <partition_number>] } [ , ...n ] ) ]   
}

<single_partition_rebuild_option > ::=   
{  
    DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }  
}  

引數Arguments

database_namedatabase_name
這是建立資料表的資料庫名稱。Is the name of the database in which the table was created.

schema_nameschema_name
這是資料表所屬的結構描述名稱。Is the name of the schema to which the table belongs.

table_nametable_name
這是要變更的資料表名稱。Is the name of the table to be altered. 如果資料表不在目前資料庫中,或未包含在目前使用者擁有的結構描述內,則必須明確指定該資料庫和結構描述。If the table is not in the current database or is not contained by the schema owned by the current user, the database and schema must be explicitly specified.

ALTER COLUMNALTER COLUMN
指定將要變更或改變的具名資料行。Specifies that the named column is to be changed or altered.

修改過的資料行不得為下列任何一項:The modified column cannot be any one of the following:

  • 資料類型為 timestamp 的資料行。A column with a timestamp data type.

  • 資料表的 ROWGUIDCOL。The ROWGUIDCOL for the table.

  • 計算資料行,或用於計算資料行。A computed column or used in a computed column.

  • 用於 CREATE STATISTICS 陳述式所產生的統計資料時,除非資料行是 varcharnvarcharvarbinary 資料類型,否則不會變更資料類型,而且新的大小等於或大於舊的大小,或者資料行從非 Null 變為 Null。Used in statistics generated by the CREATE STATISTICS statement unless the column is a varchar, nvarchar, or varbinary data type, the data type is not changed, and the new size is equal to or greater than the old size, or if the column is changed from not null to null. 首先,利用 DROP STATISTICS 陳述式移除統計資料。First, remove the statistics using the DROP STATISTICS statement.

    注意

    ALTER COLUMN 會自動卸除查詢最佳化工具自動產生的統計資料。Statistics that are automatically generated by the query optimizer are automatically dropped by ALTER COLUMN.

  • 在 PRIMARY KEY 或 [FOREIGN KEY] REFERENCES 條件約束中使用。Used in a PRIMARY KEY or [FOREIGN KEY] REFERENCES constraint.

  • 在 CHECK 或 UNIQUE 條件約束中使用。Used in a CHECK or UNIQUE constraint. 不過,允許變更用於 CHECK 或 UNIQUE 條件約束的可變長度資料行的長度。However, changing the length of a variable-length column used in a CHECK or UNIQUE constraint is allowed.

  • 與預設定義相關聯。Associated with a default definition. 不過,如果資料類型沒有變更,則會變更資料行的長度、有效位數或小數位數。However, the length, precision, or scale of a column can be changed if the data type is not changed.

textntextimage 資料行的資料類型只能透過以下方式來變更:The data type of text, ntext, and image columns can be changed only in the following ways:

  • text 變更為 varchar(max)nvarchar(max)xmltext to varchar(max), nvarchar(max), or xml

  • ntext 變更為 varchar(max)nvarchar(max)xmlntext to varchar(max), nvarchar(max), or xml

  • image 變更為 varbinary(max)image to varbinary(max)

某些資料類型變更可能會使資料變更。Some data type changes may cause a change in the data. 例如,如果將 ncharnvarchar 資料行變更為 charvarchar,則可能會轉換擴充字元。For example, changing a nchar or nvarchar column, to char or varchar, may cause the conversion of extended characters. 如需詳細資訊,請參閱 CAST 和 CONVERT (Transact-SQL)For more information, see CAST and CONVERT (Transact-SQL). 減少資料行的有效位數或小數位數,可能會使資料截斷。Reducing the precision or scale of a column may cause data truncation.

注意

分割區資料表之資料行的資料類型不能變更。The data type of a column of a partitioned table cannot be changed.

索引中所包含之資料行的資料類型無法變更,除非資料行是 varcharnvarcharvarbinary 資料類型,且新大小等於或大於舊大小。The data type of columns included in an index cannot be changed unless the column is a varchar, nvarchar, or varbinary data type, and the new size is equal to or larger than the old size.

主索引鍵條件約束中包含的資料行無法從 NOT NULL 變更為 NULLA column included in a primary key constraint, cannot be changed from NOT NULL to NULL.

當使用 Always Encrypted (不含安全記憶體保護區) 時,若遭修改的資料行已使用 'ENCRYPTED WITH' 加密,您可以將資料類型變更為相容資料類型 (例如將 INT 變更為 BIGINT),但您無法變更加密設定。When using Always Encrypted (without secure enclaves), if the column being modified is encrypted using 'ENCRYPTED WITH', you can change the datatype to a compatible datatype (such as INT to BIGINT) but you cannot change any encryption settings.

當使用具有安全記憶體保護區的 Always Encrypted 時,只要保護資料行的資料行加密金鑰 (以及新的資料行加密金鑰 (若您有變更金鑰)) 支援記憶體保護區計算 (已使用啟用記憶體保護區的資料行主要金鑰進行加密),您便可以變更任何加密設定。When using Always Encrypted with secure enclaves, you can change any encryption setting, as long as the column encryption key protecting the column (and the new column encryption key, if you are changing the key) support enclave computations (are encrypted with enclave-enabled column master keys). 如需詳細資料,請參閱具有安全記憶體保護區的 Always EncryptedFor details, see Always Encrypted with secure enclaves.

column_namecolumn_name
要改變、加入或卸除的資料欄名稱。Is the name of the column to be altered, added, or dropped. column_name 最多可有 128 個字元。column_name can be a maximum of 128 characters. 針對以 timestamp 資料類型建立的新資料行,可以省略 column_nameFor new columns, column_name can be omitted for columns created with a timestamp data type. 如果沒有為 timestamp 資料類型資料行指定任何 column_name,則會使用 timestamp 這個名稱。The name timestamp is used if no column_name is specified for a timestamp data type column.

[ type_schema_name.[ type_schema_name. ] type_name] type_name
所改變之資料行的新資料類型,或是所加入之資料行的資料類型。Is the new data type for the altered column, or the data type for the added column. 不可為資料分割資料表的現有資料行指定 type_nametype_name cannot be specified for existing columns of partitioned tables. type_name 可以是下列其中之一:type_name can be any one of the following:

  • [SQL Server]SQL Server 系統資料類型。A [SQL Server]SQL Server system data type.

  • 基於 [SQL Server]SQL Server 系統資料類型的別名資料類型。An alias data type based on a [SQL Server]SQL Server system data type. 別名資料類型是利用 CREATE TYPE 陳述式建立的,在這之後才能在資料表定義中使用它們。Alias data types are created with the CREATE TYPE statement before they can be used in a table definition.

  • .NET Framework.NET Framework 使用者定義型別及其所屬的結構描述。A .NET Framework.NET Framework user-defined type, and the schema to which it belongs. .NET Framework.NET Framework 使用者定義型別必須先以 CREATE TYPE 陳述式加以建立,才可在資料表定義中使用。user-defined types are created with the CREATE TYPE statement before they can be used in a table definition.

以下是已更改之資料行的 type_name 準則:The following are criteria for type_name of an altered column:

  • 前一個資料類型必須可隱含轉換至新資料類型。The previous data type must be implicitly convertible to the new data type.
  • type_name 不可以是 timestamptype_name cannot be timestamp.
  • ALTER COLUMN 的 ANSI_NULL 預設值一律開啟;如果未指定,資料行可為 Null。ANSI_NULL defaults are always on for ALTER COLUMN; if not specified, the column is nullable.
  • ALTER COLUMN 的 ANSI_PADDING 填補一律為 ON。ANSI_PADDING padding is always ON for ALTER COLUMN.
  • 如果修改的資料行是識別欄位,new_data_type 必須是支援識別屬性的資料類型。If the modified column is an identity column, new_data_type must be a data type that supports the identity property.
  • SET ARITHABORT 的目前設定會被忽略。The current setting for SET ARITHABORT is ignored. 如果 ARITHABORT 設為 ON,ALTER TABLE 就會執行作業。ALTER TABLE operates as if ARITHABORT is set to ON.

注意

如果未指定 COLLATE 子句,變更資料行的資料類型會使資料庫的預設定序發生定序變更。If the COLLATE clause is not specified, changing the data type of a column will cause a collation change to the default collation of the database.

有效位數precision
這是指定之資料類型的有效位數。Is the precision for the specified data type. 如需有關有效位數值的詳細資訊,請參閱有效位數、小數位數和長度 (Transact-SQL)For more information about valid precision values, see Precision, Scale, and Length (Transact-SQL).

scalescale
這是指定資料類型的小數位數。Is the scale for the specified data type. 如需有關有效小數位數值的詳細資訊,請參閱有效位數、小數位數和長度 (Transact-SQL)For more information about valid scale values, see Precision, Scale, and Length (Transact-SQL).

maxmax
只適用於 varcharnvarcharvarbinary 資料類型,可用來儲存 2^31-1 位元組的字元、二進位資料與 Unicode 資料。Applies only to the varchar, nvarchar, and varbinary data types for storing 2^31-1 bytes of character, binary data, and of Unicode data.

xml_schema_collectionxml_schema_collection
適用於[SQL Server]SQL Server (SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017),以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: [SQL Server]SQL Server ( SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database.

只適用於 xml 資料類型,可用來將 XML 結構描述與該類型產生關聯。Applies only to the xml data type for associating an XML schema with the type. xml 資料行輸入結構描述集合之前,必須先使用 CREATE XML SCHEMA COLLECTION,在資料庫中建立結構描述集合。Before typing an xml column to a schema collection, the schema collection must first be created in the database by using CREATE XML SCHEMA COLLECTION.

COLLATE < collation_name > 指定已改變之資料行的新定序。COLLATE < collation_name > Specifies the new collation for the altered column. 若未指定,就會將資料庫的預設定序指派給資料行。If not specified, the column is assigned the default collation of the database. 定序名稱可以是 Windows 定序名稱或 SQL 定序名稱。Collation name can be either a Windows collation name or a SQL collation name. 如需清單和詳細資訊,請參閱 Windows 定序名稱 (Transact-SQL)SQL Server 定序名稱 (Transact-SQL)For a list and more information, see Windows Collation Name (Transact-SQL) and SQL Server Collation Name (Transact-SQL).

COLLATE 子句只能用來變更 charvarcharncharnvarchar 資料類型之資料行的定序。The COLLATE clause can be used to change the collations only of columns of the char, varchar, nchar, and nvarchar data types. 若要變更使用者定義之別名資料類型資料行的定序,您必須執行個別的 ALTER TABLE 陳述式,將資料行變更為 [SQL Server]SQL Server 系統資料類型,並變更它的定序,然後再將資料行改回別名資料類型。To change the collation of a user-defined alias data type column, you must execute separate ALTER TABLE statements to change the column to a [SQL Server]SQL Server system data type and change its collation, and then change the column back to an alias data type.

如果存在下列一個或多個條件,ALTER COLUMN 不能有定序變更:ALTER COLUMN cannot have a collation change if one or more of the following conditions exist:

  • 如果 CHECK 條件約束、FOREIGN KEY 條件約束或計算資料行參考變更的資料行。If a CHECK constraint, FOREIGN KEY constraint, or computed columns reference the column changed.
  • 如果在資料行上建立任何索引、統計資料或全文檢索索引。If any index, statistics, or full-text index are created on the column. 如果資料行定序變更了,在變更的資料行上自動建立的統計資料就會卸除。Statistics created automatically on the column changed are dropped if the column collation is changed.
  • 如果結構描述繫結檢視或函數參考資料行。If a schema-bound view or function references the column.

如需詳細資訊,請參閱 COLLATE (Transact-SQL)For more information, see COLLATE (Transact-SQL).

NULL | NOT NULLNULL | NOT NULL
指定資料行是否接受 Null 值。Specifies whether the column can accept null values. 只有在不允許 Null 值的資料行指定了預設值,或資料表是空的情況下,才能利用 ALTER TABLE 新增這些資料行。Columns that do not allow null values can be added with ALTER TABLE only if they have a default specified or if the table is empty. 只有在也指定了 PERSISTED 時,計算資料行才能指定 NOT NULL。NOT NULL can be specified for computed columns only if PERSISTED is also specified. 如果新資料行允許 Null 值,且未指定預設值,資料表每個資料列的新資料行都會包含 Null 值。If the new column allows null values and no default is specified, the new column contains a null value for each row in the table. 如果新資料行允許 Null 值,且加入了預設定義,就可以利用 WITH VALUES,將預設值儲存在資料表每個現有資料列的新資料行中。If the new column allows null values and a default definition is added with the new column, WITH VALUES can be used to store the default value in the new column for each existing row in the table.

如果新資料行不允許 Null 值,且資料表不是空的,則必須利用新資料行新增 DEFAULT 定義,則新資料行會自動將預設值載入每個現有資料列中的新資料行。If the new column does not allow null values and the table is not empty, a DEFAULT definition must be added with the new column, and the new column automatically loads with the default value in the new columns in each existing row.

您可以在 ALTER COLUMN 中指定 NULL,來強制 NOT NULL 資料行允許 NULL 值 (但不包括 PRIMARY KEY 條件約束中的資料行)。NULL can be specified in ALTER COLUMN to force a NOT NULL column to allow null values, except for columns in PRIMARY KEY constraints. 只有在資料行沒有包含 Null 值的情況下,才能在 ALTER COLUMN 中指定 NOT NULL。NOT NULL can be specified in ALTER COLUMN only if the column contains no null values. 必須先將 Null 值更新為某些值,才能允許 ALTER COLUMN NOT NULL,例如:The null values must be updated to some value before the ALTER COLUMN NOT NULL is allowed, for example:

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 屬性。When you create or alter a table with the CREATE TABLE or ALTER TABLE statements, the database and session settings influence and possibly override the nullability of the data type that is used in a column definition. 我們建議您一定要針對非計算資料行,明確將資料行定義為 NULL 或 NOT NULL。We recommend that you always explicitly define a column as NULL or NOT NULL for noncomputed columns.

如果您加入一個具有使用者定義資料類型的資料行,我們建議您最好使用與此使用者定義資料類型相同的 Null 屬性來定義此資料行,並為此資料行指定預設值。If you add a column with a user-defined data type, we recommend that you define the column with the same nullability as the user-defined data type and specify a default value for the column. 如需詳細資訊,請參閱 CREATE TABLE (Transact-SQL)For more information, see CREATE TABLE (Transact-SQL).

注意

如果使用 ALTER COLUMN 指定 NULL 或 NOT NULL,則必須同時指定 new_data_type [(有效位數 [, 小數位數 ])]。If NULL or NOT NULL is specified with ALTER COLUMN, new_data_type [(precision [, scale ])] must also be specified. 如果資料類型、有效位數及小數位數沒有變更,請指定目前的資料行值。If the data type, precision, and scale are not changed, specify the current column values.

[ {ADD | DROP} ROWGUIDCOL ][ {ADD | DROP} ROWGUIDCOL ]
適用於[SQL Server]SQL Server (SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017),以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: [SQL Server]SQL Server ( SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database.

指定將 ROWGUIDCOL 屬性加入至指定的資料行,或從指定的資料行卸除該屬性。Specifies the ROWGUIDCOL property is added to or dropped from the specified column. ROWGUIDCOL 指出資料行是資料列 GUID 資料行。ROWGUIDCOL indicates that the column is a row GUID column. 只能為每個資料表指定一個 uniqueidentifier 資料行作為 ROWGUIDCOL 資料行,且只能將 ROWGUIDCOL 屬性指派給 uniqueidentifier 資料行。Only one uniqueidentifier column per table can be designated as the ROWGUIDCOL column, and the ROWGUIDCOL property can be assigned only to a uniqueidentifier column. 不能將 ROWGUIDCOL 指派給使用者定義資料類型的資料行。ROWGUIDCOL cannot be assigned to a column of a user-defined data type.

ROWGUIDCOL 不強制使用儲存在資料行中之值的唯一性,且不針對插入資料表中的新資料列自動產生值。ROWGUIDCOL does not enforce uniqueness of the values that are stored in the column and does not automatically generate values for new rows that are inserted into the table. 若要為每個資料行產生唯一的值,請在 INSERT 陳述式上使用 NEWID 或 NEWSEQUENTIALID 函式,或將 NEWID 或 NEWSEQUENTIALID 函式指定為資料行的預設值。To generate unique values for each column, either use the NEWID or NEWSEQUENTIALID function on INSERT statements or specify the NEWID or NEWSEQUENTIALID function as the default for the column.

[ {ADD | DROP} PERSISTED ][ {ADD | DROP} PERSISTED ]
指定將 PERSISTED 屬性加入至指定的資料行,或從指定的資料行卸除該屬性。Specifies that the PERSISTED property is added to or dropped from the specified column. 該資料行必須是一個利用具決定性運算式定義的計算資料行。The column must be a computed column that is defined with a deterministic expression. 就指定為 PERSISTED 的資料行而言,當計算資料行相依的任何其他資料行更新時,Database EngineDatabase Engine 實際上會將計算值儲存在資料表並將值更新。For columns specified as PERSISTED, the Database EngineDatabase Engine physically stores the computed values in the table and updates the values when any other columns on which the computed column depends are updated. 將計算資料行標示為 PERSISTED,就可以在定義於具決定性 (但不是精確) 運算式上的計算資料行上建立索引。By marking a computed column as PERSISTED, you can create indexes on computed columns defined on expressions that are deterministic, but not precise. 如需詳細資訊,請參閱 計算資料行的索引For more information, see Indexes on Computed Columns.

當做分割區資料表之分割區資料行的任何計算資料行,都必須明確標示為 PERSISTED。Any computed column that is used as a partitioning column of a partitioned table must be explicitly marked PERSISTED.

DROP NOT FOR REPLICATIONDROP NOT FOR REPLICATION
適用於[SQL Server]SQL Server (SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017),以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: [SQL Server]SQL Server ( SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database.

指定當複寫代理程式執行插入作業時,識別欄位中的值會累加。Specifies that values are incremented in identity columns when replication agents perform insert operations. 只有當 column_name 是識別欄位時,才能指定這個子句。This clause can be specified only if column_name is an identity column.

SPARSESPARSE
指出此資料行是疏鬆資料行。Indicates that the column is a sparse column. 疏鬆資料行的儲存體會針對 Null 值最佳化。The storage of sparse columns is optimized for null values. 疏鬆資料行無法指定為 NOT NULL。Sparse columns cannot be designated as NOT NULL. 將資料行從疏鬆轉換成非疏鬆 (或相反) 會在命令執行期間疏鬆鎖定資料表。Converting a column from sparse to nonsparse or from nonsparse to sparse locks the table for the duration of the command execution. 您可能必須使用 REBUILD 子句來回收任何節省的空間。You may need to use the REBUILD clause to reclaim any space savings. 如需有關疏鬆資料行的其他限制和詳細資訊,請參閱使用疏鬆資料行For additional restrictions and more information about sparse columns, see Use Sparse Columns.

ADD MASKED WITH ( FUNCTION = ' mask_function ')ADD MASKED WITH ( FUNCTION = ' mask_function ')
適用於[SQL Server]SQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017),以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: [SQL Server]SQL Server ( SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database.

指定動態資料遮罩。Specifies a dynamic data mask. mask_function 是遮罩函數的名稱並具備適當的參數。mask_function is the name of the masking function with the appropriate parameters. 可用的函數有三個:Three functions are available:

  • default()default()
  • email()email()
  • partial()partial()
  • random()random()

若要卸除遮罩,請使用 DROP MASKEDTo drop a mask, use DROP MASKED. 針對函數參數,請參閱動態資料遮罩For function parameters, see Dynamic Data Masking.

WITH ( ONLINE = ON | OFF) <適用於改變資料行>WITH ( ONLINE = ON | OFF) <as applies to altering a column>
適用於[SQL Server]SQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017),以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: [SQL Server]SQL Server ( SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database.

資料表仍可使用時,允許執行多個改變資料行動作。Allows many alter column actions to be performed while the table remains available. 預設是 OFF。Default is OFF. 可線上針對資料類型、資料行長度或有效位數、Null 屬性、疏鬆度和定序來改變資料行。Alter column can be performed on line for column changes related to data type, column length or precision, nullability, sparseness, and collation.

線上改變資料行可讓使用者建立和自動統計資料於 ALTER COLUMN 作業期間參考變更資料行。Online alter column allows user created and auto statistics to reference the altered column for the duration of the ALTER COLUMN operation. 這可讓查詢如往常般執行。This allows queries to perform as usual. 在作業結束時會卸除參考資料行的自動統計,使用者建立的統計資料亦同時失效。At the end of the operation, auto-stats that reference the column are dropped and user-created stats are invalidated. 作業完成之後,使用者必須手動更新使用者產生的統計資料。The user must manually update user-generated statistics after the operation is completed. 如果資料行是任何統計資料或索引的篩選運算式的一部分,則您無法執行改變資料行的作業。If the column is part of a filter expression for any statistics or indexes then you cannot perform an alter column operation.

  • 雖然線上改變資料行的作業正在執行,但可能與資料行有相依性之所有作業(索引、檢視等)會因適當的錯誤訊息而封鎖或失敗。While the online alter column operation is running, all operations that could take a dependency on the column (index, views, etc.) will block or fail with an appropriate error. 這確保線上改變資料行不會因作業執行時導入的相依性之故而失敗。This guarantees that online alter column will not fail because of dependencies introduced while the operation was running.

  • 當非叢集索引參考資料行時,便不支援改變資料行以線上作業形式從 NOT NULL 變為 NULL。Altering a column from NOT NULL to NULL is not supported as an online operation when the altered column is references by nonclustered indexes.

  • 當檢查條件約束參考資料行,以及改變作業限制資料行的有效位數 (數值或日期時間) 時,便不支援線上改變。Online alter is not supported when the column is referenced by a check constraint and the alter operation is restricting the precision of the column (numeric or datetime).

  • WAIT_AT_LOW_PRIORITY 選項不能以線上改變資料行使用。The WAIT_AT_LOW_PRIORITY option cannot be used with online alter column.

  • ALTER COLUMN ... ADD/DROP PERSISTED 不支援線上改變資料行。ALTER COLUMN ... ADD/DROP PERSISTED is not supported for online alter column.

  • ALTER COLUMN ... ADD/DROP ROWGUIDCOL/NOT FOR REPLICATION 不會受到線上改變資料行的影響。ALTER COLUMN ... ADD/DROP ROWGUIDCOL/NOT FOR REPLICATION is not affected by online alter column.

  • 已啟用變更追蹤,或是屬於合併式複寫發行者之線上改變資料行不支援改變資料表。Online alter column does not support altering a table where change tracking is enabled or that is a publisher of merge replication.

  • 線上改變資料行不支援改變自或改變成 CLR 資料類型。Online alter column does not support altering from or to CLR data types.

  • 線上改變資料行不支援改變成具有不同於目前結構描述集合的 XML 資料類型。Online alter column does not support altering to an XML data type that has a schema collection different than the current schema collection.

  • 線上改變資料行不會減少針對何時可以更改資料行之限制。Online alter column does not reduce the restrictions on when a column can be altered. 索引及統計等之參考資料可能會導致改變失敗。References by index/stats, etc. might cause the alter to fail.

  • 線上改變資料行不支援同時修改多個資料行。Online alter column does not support altering more than one column concurrently.

  • 針對系統版本設定時態表,線上改變資料行並不會產生影響。Online alter column has no effect in case of system-versioned temporal table. 無論針對 ONLINE 選項指定何值,都不會以線上方式執行 ALTER 資料行。ALTER column is not performed as online regardless of which value was specified for ONLINE option.

線上改變資料行具有與線上索引重建類似的需求、限制和功能。Online alter column has similar requirements, restrictions, and functionality as online index rebuild. 這包括:This includes:

  • 當資料表包含舊版的 LOB 或 filestream 資料行或資料表具有資料行存放區索引時,不支援線上索引重建。Online index rebuild is not supported when the table contains legacy LOB or filestream columns or when the table has a columnstore index. 相同的限制適用於線上改變資料行。The same limitations apply for online alter column.

  • 與原始資料行和新建立的隱藏資料行相比較,要改變現有的資料行需要兩倍的空間配置。An existing column being altered requires twice the space allocation; for the original column and for the newly created hidden column.

  • 改變資料行線上作業期間的鎖定策略是遵循用於線上索引建立相同的鎖定模式。The locking strategy during an alter column online operation follows the same locking pattern used for online index build.

WITH CHECK | WITH NOCHECKWITH CHECK | WITH NOCHECK
指定是否要依照新加入或重新啟用的 FOREIGN KEY 或 CHECK 條件約束來驗證資料表中的資料。Specifies whether the data in the table is or is not validated against a newly added or re-enabled FOREIGN KEY or CHECK constraint. 如果未指定,則假設 WITH CHECK 為新條件約束,並假設 WITH NOCHECK 為重新啟用的條件約束。If not specified, WITH CHECK is assumed for new constraints, and WITH NOCHECK is assumed for re-enabled constraints.

如果您不要依照現有的資料來確認新的 CHECK 或 FOREIGN KEY 條件約束,請使用 WITH NOCHECK。If you do not want to verify new CHECK or FOREIGN KEY constraints against existing data, use WITH NOCHECK. 除了極少數的狀況外,我們建議您不要這麼做。We do not recommend doing this, except in rare cases. 在以後的所有資料更新中將會評估新條件約束。The new constraint will be evaluated in all later data updates. 新增條件約束時,如果 WITH NOCHECK 抑制任何強制違規,當未來的更新作業更新含有不符合該條件約束的資料列時,這些強制違規可能會使這些更新作業失敗。Any constraint violations that are suppressed by WITH NOCHECK when the constraint is added may cause future updates to fail if they update rows with data that does not comply with the constraint.

注意

查詢最佳化工具不考量定義為 WITH NOCHECK 的條件約束。The query optimizer does not consider constraints that are defined WITH NOCHECK. 這類條件約束將予忽略,直到使用 ALTER TABLE table WITH CHECK CHECK CONSTRAINT ALL 重新啟用為止。Such constraints are ignored until they are re-enabled by using ALTER TABLE table WITH CHECK CHECK CONSTRAINT ALL.

ALTER INDEX index_name 指定要變更或改變之 index_name 的貯體計數。ALTER INDEX index_name Specifies that the bucket count for index_name is to be changed or altered.

語法 ALTER TABLE ...只有記憶體最佳化資料表支援 ADD/DROP/ALTER INDEX。The syntax ALTER TABLE ... ADD/DROP/ALTER INDEX is supported only for memory-optimized tables.

重要

在未使用 ALTER TABLE 陳述式的情況下,記憶體最佳化資料表上的索引就不支援 CREATE INDEXDROP INDEXALTER INDEXPAD_INDEX 陳述式。Without using an ALTER TABLE statement, the statements CREATE INDEX, DROP INDEX, ALTER INDEX, and PAD_INDEX are not supported for indexes on memory-optimized tables.

ADDADD
指定加入一或多個資料行定義、計算資料行定義或資料表條件約束,或進行系統版本設定時,系統會用到的資料行。Specifies that one or more column definitions, computed column definitions, or table constraints are added, or the columns that the system will use for system versioning. 可以對記憶體最佳化資料表新增索引。For memory-optimized tables, an index can be added.

重要

在未使用 ALTER TABLE 陳述式的情況下,記憶體最佳化資料表上的索引就不支援 CREATE INDEXDROP INDEXALTER INDEXPAD_INDEX 陳述式。Without using an ALTER TABLE statement, the statements CREATE INDEX, DROP INDEX, ALTER INDEX, and PAD_INDEX are not supported for indexes on memory-optimized tables.

PERIOD FOR SYSTEM_TIME ( system_start_time_column_name, system_end_time_column_name )PERIOD FOR SYSTEM_TIME ( system_start_time_column_name, system_end_time_column_name )
適用對象[SQL Server]SQL Server (SQL Server 2017 (14.x)SQL Server 2017 (14.x)SQL Server 2017SQL Server 2017),以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: [SQL Server]SQL Server (SQL Server 2017 (14.x)SQL Server 2017 (14.x) through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database.

指定資料行的名稱,系統會使用這個資料行來記載某一筆記錄的有效期。Specifies the names of the columns that the system will use to record the period for which a record is valid. 您可以指定現有的資料行或建立新的資料行,作為 ADD PERIOD FOR SYSTEM_TIME 引數的一部分。You can specify existing columns or create new columns as part of the ADD PERIOD FOR SYSTEM_TIME argument. 資料行的資料類型必須是 datetime2,而且必須定義為 NOT NULL。The columns must have the datatype of datetime2 and must be defined as NOT NULL. 如果某個期間資料行定義為 NULL,則會擲回錯誤。If a period column is defined as NULL, an error will be thrown. 您可以為 system_start_time 和 system_end_time 資料行定義 column_constraint (Transact-SQL) 和/或指定資料行的預設值You can define a column_constraint (Transact-SQL) and/or Specify Default Values for Columns for the system_start_time and system_end_time columns. 請參閱系統版本設定範例中的範例 A,此範例示範 system_end_time 資料行如何使用預設值。See Example A in the System Versioning examples below demonstrating the use of a default value for the system_end_time column.

使用這個引數再加上 SET SYSTEM_VERSIONING 引數,在現有資料表上啟用系統版本設定。Use this argument in conjunction with the SET SYSTEM_VERSIONING argument to enable system versioning on an existing table. 如需詳細資訊,請參閱時態表開始使用 Azure SQL Database 中的時態表For more information, see Temporal Tables and Getting Started with Temporal Tables in Azure SQL Database.

SQL Server 2017 (14.x)SQL Server 2017 (14.x) 中,使用者可以使用 HIDDEN 旗標來標記一或兩個期間資料行來隱含隱藏這些資料行,使 SELECT * FROM <資料表名稱> 在查詢這些資料行時不會傳回任何值。As of SQL Server 2017 (14.x)SQL Server 2017 (14.x), users will be able to mark one or both period columns with HIDDEN flag to implicitly hide these columns such that SELECT * FROM <table_name> does not return a value for those columns. 根據預設,不會隱藏期間資料行。By default, period columns are not hidden. 為了方便我們使用,隱藏的資料行必須明確包含在所有會直接參考時態表的查詢中。In order to be used, hidden columns must be explicitly included in all queries that directly reference the temporal table.

DROPDROP
指定卸除一個或多個資料行定義、計算資料行定義或資料表條件約束,或卸除系統將用於系統版本設定的資料行。Specifies that one or more column definitions, computed column definitions, or table constraints are dropped, or to drop the specification for the columns that the system will use for system versioning.

CONSTRAINT constraint_name CONSTRAINT constraint_name
指定從資料表中移除 constraint_nameSpecifies that constraint_name is removed from the table. 可以列出多個條件約束。Multiple constraints can be listed.

您可以查詢 sys.check_constraintsys.default_constraintssys.key_constraintssys.foreign_keys 目錄檢視表,判斷條件約束的使用者自訂名稱或系統提供的名稱。The user-defined or system-supplied name of the constraint can be determined by querying the sys.check_constraint, sys.default_constraints, sys.key_constraints, and sys.foreign_keys catalog views.

如果 XML 索引存在於資料表上,則不能卸除 PRIMARY KEY 條件約束。A PRIMARY KEY constraint cannot be dropped if an XML index exists on the table.

INDEX index_name INDEX index_name
指定從資料表中移除 index_nameSpecifies that index_name is removed from the table.

語法 ALTER TABLE ...只有記憶體最佳化資料表支援 ADD/DROP/ALTER INDEX。The syntax ALTER TABLE ... ADD/DROP/ALTER INDEX is supported only for memory-optimized tables.

重要

在未使用 ALTER TABLE 陳述式的情況下,記憶體最佳化資料表上的索引就不支援 CREATE INDEXDROP INDEXALTER INDEXPAD_INDEX 陳述式。Without using an ALTER TABLE statement, the statements CREATE INDEX, DROP INDEX, ALTER INDEX, and PAD_INDEX are not supported for indexes on memory-optimized tables.

COLUMN column_nameCOLUMN column_name
指定從資料表移除 constraint_namecolumn_nameSpecifies that constraint_name or column_name is removed from the table. 可以列出多個資料行。Multiple columns can be listed.

當資料行符合下列條件時,則無法將它卸除:A column cannot be dropped when it is:

  • 無論是以索引鍵資料行或以 INCLUDE 的形式,用於索引中Used in an index, whether as a key column or as an INCLUDE

  • 用於 CHECK、FOREIGN KEY、UNIQUE 或 PRIMARY KEY 條件約束。Used in a CHECK, FOREIGN KEY, UNIQUE, or PRIMARY KEY constraint.

  • 與利用 DEFAULT 關鍵字定義的預設值相關聯,或者繫結到預設物件。Associated with a default that is defined with the DEFAULT keyword, or bound to a default object.

  • 繫結至規則。Bound to a rule.

注意

卸除資料行不會回收資料行的磁碟空間。Dropping a column does not reclaim the disk space of the column. 當資料表的資料列大小接近或已超出限制時,您可能需要回收卸除之資料行的磁碟空間。You may have to reclaim the disk space of a dropped column when the row size of a table is near, or has exceeded, its limit. 指您可以在資料表上建立叢集索引,或利用 ALTER INDEX 重建現有的叢集索引來回收空間。Reclaim space by creating a clustered index on the table or rebuilding an existing clustered index by using ALTER INDEX. 如需有關卸除 LOB 資料類型之影響的詳細資訊,請參閱此 CSS 部落格文章 (英文)。For information about the impact of dropping LOB data types, see this CSS blog entry.

PERIOD FOR SYSTEM_TIMEPERIOD FOR SYSTEM_TIME
適用於[SQL Server]SQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017),以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: [SQL Server]SQL Server ( SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database.

卸除系統將用於系統版本設定的資料行。Drops the specification for the columns that the system will use for system versioning.

WITH <drop_clustered_constraint_option>WITH <drop_clustered_constraint_option>
指定設定一個或多個卸除叢集條件約束選項。Specifies that one or more drop clustered constraint options are set.

MAXDOP = max_degree_of_parallelismMAXDOP = max_degree_of_parallelism
適用於[SQL Server]SQL Server (SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017),以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: [SQL Server]SQL Server ( SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database.

只針對作業持續時間覆寫 max degree of parallelism 設定選項。Overrides the max degree of parallelism configuration option only for the duration of the operation. 如需詳細資訊,請參閱 設定 max degree of parallelism 伺服器組態選項For more information, see Configure the max degree of parallelism Server Configuration Option.

請利用 MAXDOP 選項來限制執行平行計畫所用的處理器數目。Use the MAXDOP option to limit the number of processors used in parallel plan execution. 最大值是 64 個處理器。The maximum is 64 processors.

max_degree_of_parallelism 可以是下列其中一個值:max_degree_of_parallelism can be one of the following values:

11
隱藏平行計畫的產生。Suppresses parallel plan generation.

>1>1
將平行索引作業所用的最大處理器數目限制為指定的數目。Restricts the maximum number of processors used in a parallel index operation to the specified number.

0 (預設值)0 (default)
根據目前的系統工作負載,使用實際數目或比實際數目更少的處理器。Uses the actual number of processors or fewer based on the current system workload.

如需詳細資訊,請參閱 設定平行索引作業For more information, see Configure Parallel Index Operations.

注意

[SQL Server]SQL Server 的所有版本都無法使用平行索引作業。Parallel index operations are not available in every edition of [SQL Server]SQL Server. 如需詳細資訊,請參閱 SQL Server 2016 的版本及支援功能SQL Server 2017 的版本及支援功能For more information, see Editions and Supported Features for SQL Server 2016, and Editions and Supported Features for SQL Server 2017.

ONLINE = { ON | OFF } <適用於 drop_clustered_constraint_option>ONLINE = { ON | OFF } <as applies to drop_clustered_constraint_option>
指定在索引作業期間,查詢和資料修改是否能夠使用基礎資料表和相關聯的索引。Specifies whether underlying tables and associated indexes are available for queries and data modification during the index operation. 預設值為 OFF。The default is OFF. REBUILD 可以執行為 ONLINE 作業。REBUILD can be performed as an ONLINE operation.

ONON
索引作業持續期間不會保留長期資料表鎖定。Long-term table locks are not held for the duration of the index operation. 在索引作業的主要階段期間,來源資料表上只保留意圖共用 (IS) 鎖定。During the main phase of the index operation, only an Intent Share (IS) lock is held on the source table. 這使得基礎資料表和索引的查詢或更新能夠繼續運作。This enables queries or updates to the underlying table and indexes to continue. 在作業開始時,只會在一段很短的時間內,保留來源物件的共用 (S) 鎖定。At the start of the operation, a Shared (S) lock is held on the source object for a very short time. 在作業結束時,如果建立非叢集索引的話,便會短時間取得來源的 S (共用) 鎖定;在線上建立或卸除叢集索引時,以及重建叢集或非叢集索引時,會取得 SCH-M (結構描述修改) 鎖定。At the end of the operation, for a short time, an S (Shared) lock is acquired on the source if a nonclustered index is being created; or an SCH-M (Schema Modification) lock is acquired when a clustered index is created or dropped online and when a clustered or nonclustered index is being rebuilt. 建立本機暫存資料表的索引時,ONLINE 不可設為 ON。ONLINE cannot be set to ON when an index is being created on a local temporary table. 只可使用單一執行緒的堆積重建作業。Only single-threaded heap rebuild operation is allowed.

若要執行 SWITCH 或線上索引重建的 DDL,則特定資料表上執行的所有進行中封鎖交易都必須完成。To execute the DDL for SWITCH or online index rebuild, all active blocking transactions running on a particular table must be completed. 執行時,SWITCH 或重建作業會阻止新交易啟動,且可能會大幅影響工作負載輸送量並暫時延遲對基礎資料表的存取。When executing, the SWITCH or rebuild operation prevents new transaction from starting and might significantly affect the workload throughput and temporarily delay access to the underlying table.

OFFOFF
在索引作業期間會套用資料表鎖定。Table locks are applied for the duration of the index operation. 建立、重建或卸除叢集索引的離線索引作業,或重建或卸除非叢集索引的離線索引作業,會取得資料表的結構描述修改 (Sch-M) 鎖定。An offline index operation that creates, rebuilds, or drops a clustered index, or rebuilds or drops a nonclustered index, acquires a Schema modification (Sch-M) lock on the table. 這可防止所有使用者在作業持續期間存取基礎資料表。This prevents all user access to the underlying table for the duration of the operation. 建立非叢集索引的離線索引作業會取得資料表的共用 (S) 鎖定。An offline index operation that creates a nonclustered index acquires a Shared (S) lock on the table. 這可避免對基礎資料表進行更新,但仍可執行讀取作業,如 SELECT 陳述式。This prevents updates to the underlying table but allows read operations, such as SELECT statements. 允許多執行緒的堆積重建作業。Multi-threaded heap rebuild operations are allowed.

如需詳細資訊,請參閱線上索引作業如何運作For more information, see How Online Index Operations Work.

注意

[SQL Server]SQL Server的所有版本都無法使用線上索引作業。Online index operations are not available in every edition of [SQL Server]SQL Server. 如需詳細資訊,請參閱 SQL Server 2016 的版本及支援功能SQL Server 2017 的版本及支援功能For more information, see Editions and Supported Features for SQL Server 2016, and Editions and Supported Features for SQL Server 2017.

MOVE TO { partition_scheme_name(column_name [ 1 , ... n] ) | filegroup | " default " }MOVE TO { partition_scheme_name(column_name [ 1 , ... n] ) | filegroup | " default " }
適用於[SQL Server]SQL Server (SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017),以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: [SQL Server]SQL Server ( SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database.

指定目前在叢集索引分葉層級中之資料列所要移往的位置。Specifies a location to move the data rows currently in the leaf level of the clustered index. 資料表會移至新位置。The table is moved to the new location. 此選項只適用於建立叢集索引的條件約束。This option applies only to constraints that create a clustered index.

注意

在此內容中,default 不是關鍵字。In this context, default is not a keyword. 它是預設檔案群組的識別碼,必須加以分隔,如 MOVE TO " default " 或 MOVE TO [ default ]It is an identifier for the default filegroup and must be delimited, as in MOVE TO " default " or MOVE TO [ default ]. 如果指定了 " default ",則目前工作階段的 QUOTED_IDENTIFIER 選項就必須是 ON。If " default " is specified, the QUOTED_IDENTIFIER option must be ON for the current session. 這是預設值。This is the default setting. 如需詳細資訊,請參閱 SET QUOTED_IDENTIFIER (Transact-SQL)For more information, see SET QUOTED_IDENTIFIER (Transact-SQL).

{ CHECK | NOCHECK } CONSTRAINT{ CHECK | NOCHECK } CONSTRAINT
指定 constraint_name 為已啟用或已停用。Specifies that constraint_name is enabled or disabled. 這個選項只能搭配 FOREIGN KEY 和 CHECK 條件約束使用。This option can only be used with FOREIGN KEY and CHECK constraints. 當指定 NOCHECK 時,會停用條件約束,且不會依照條件約束條件來驗證未來資料行的插入或更新作業。When NOCHECK is specified, the constraint is disabled and future inserts or updates to the column are not validated against the constraint conditions. 不能停用 DEFAULT、PRIMARY KEY 及 UNIQUE 條件約束。DEFAULT, PRIMARY KEY, and UNIQUE constraints cannot be disabled.

ALLALL
指定利用 NOCHECK 選項停用所有條件約束,或利用 CHECK 選項啟用所有條件約束。Specifies that all constraints are either disabled with the NOCHECK option or enabled with the CHECK option.

{ ENABLE | DISABLE } TRIGGER{ ENABLE | DISABLE } TRIGGER
指定 trigger_name 為已啟用或已停用。Specifies that trigger_name is enabled or disabled. 當停用觸發程序時,仍會針對資料表定義觸發程序;不過,當依照資料表執行 INSERT、UPDATE 或 DELETE 陳述式時,在重新啟用觸發程序之前,並不會執行觸發程序中的動作。When a trigger is disabled it is still defined for the table; however, when INSERT, UPDATE, or DELETE statements are executed against the table, the actions in the trigger are not performed until the trigger is re-enabled.

ALLALL
指定資料表中的所有觸發程序為已啟用或已停用。Specifies that all triggers in the table are enabled or disabled.

trigger_nametrigger_name
指定要停用或啟用的觸發程序名稱。Specifies the name of the trigger to disable or enable.

{ ENABLE | DISABLE } CHANGE_TRACKING{ ENABLE | DISABLE } CHANGE_TRACKING
適用於[SQL Server]SQL Server (SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017),以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: [SQL Server]SQL Server ( SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database.

指定資料表是要啟用還是停用變更追蹤。Specifies whether change tracking is enabled disabled for the table. 預設會停用變更追蹤。By default, change tracking is disabled.

只有當資料庫啟用了變更追蹤時,才能使用此選項。This option is available only when change tracking is enabled for the database. 如需詳細資訊,請參閱 ALTER DATABASE SET 選項 (Transact-SQL)For more information, see ALTER DATABASE SET Options (Transact-SQL).

若要啟用變更追蹤,資料表必須具有主索引鍵。To enable change tracking, the table must have a primary key.

WITH ( TRACK_COLUMNS_UPDATED = { ON | OFF } )WITH ( TRACK_COLUMNS_UPDATED = { ON | OFF } )
適用於[SQL Server]SQL Server (SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017),以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: [SQL Server]SQL Server ( SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database.

指定 Database EngineDatabase Engine 是否追蹤哪些啟用變更追蹤的資料行已更新。Specifies whether the Database EngineDatabase Engine tracks which change tracked columns were updated. 預設值是 OFF。The default value is OFF.

SWITCH [ PARTITION source_partition_number_expression ] TO [ schema_name.SWITCH [ PARTITION source_partition_number_expression ] TO [ schema_name. ] target_table [ PARTITION target_partition_number_expression ]] target_table [ PARTITION target_partition_number_expression ]
適用於[SQL Server]SQL Server (SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017),以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: [SQL Server]SQL Server ( SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database.

利用下列其中一種方式切換資料區塊:Switches a block of data in one of the following ways:

  • 將資料表的所有資料當做分割區重新指派給已存在的分割區資料表。Reassigns all data of a table as a partition to an already-existing partitioned table.
  • 將分割區從某一分割區資料表切換到另一分割區資料表。Switches a partition from one partitioned table to another.
  • 將分割區資料表之一個分割區中的所有資料,重新指派給現有的非分割區資料表。Reassigns all data in one partition of a partitioned table to an existing non-partitioned table.

如果 table 是資料分割資料表,則必須指定 source_partition_number_expressionIf table is a partitioned table, source_partition_number_expression must be specified. 如果 target_table 是資料分割資料表,則必須指定 target_partition_number_expressionIf target_table is partitioned, target_partition_number_expression must be specified. 如果將做為分割區之資料表的資料重新指派給已存在的分割區資料表,或將分割區從某一分割區資料表切換到另一分割區資料表,則目標分割區必須存在,且它必須是空的。If reassigning a table's data as a partition to an already-existing partitioned table, or switching a partition from one partitioned table to another, the target partition must exist and it must be empty.

如果重新指派一個分割區的資料來形成單一資料表,則目標資料表必須已經建立,且它必須是空的。If reassigning one partition's data to form a single table, the target table must already be created and it must be empty. 來源資料表或分割區和目標資料表或分割區必須位於相同的檔案群組。Both the source table or partition, and the target table or partition, must reside in the same filegroup. 相對應的索引或索引分割區區也必須位於相同的檔案群組。The corresponding indexes, or index partitions, must also reside in the same filegroup. 切換分割區還有許多其他限制。Many additional restrictions apply to switching partitions. tabletarget_table 不可以相同。table and target_table cannot be the same. target_table 可以是多部分識別碼。target_table can be a multi-part identifier.

source_partition_number_expressiontarget_partition_number_expression 是可以參考變數和函數的常數運算式。source_partition_number_expression and target_partition_number_expression are constant expressions that can reference variables and functions. 其中包括使用者定義型別變數與使用者定義函數。These include user-defined type variables and user-defined functions. 其不可參考 Transact-SQLTransact-SQL 運算式。They cannot reference Transact-SQLTransact-SQL expressions.

具備叢集資料行存放區索引的資料分割資料表,其行為類似資料分割堆積:A partitioned table with a clustered columstore index behaves like a partitioned heap:

  • 主索引鍵必須包含資料分割索引鍵。The primary key must include the partition key.
  • 唯一索引必須包含資料分割索引鍵。A unique index must include the partition key. 請注意,在現有唯一索引包括資料分割索引鍵 會變更唯一性。Note that including the partition key to an existing unique index can change the uniqueness.
  • 為了切換資料分割,所有非叢集索引都必須包括資料分割索引鍵。In order to switch partitions, all non-clustered indexes must include the partition key.

針對使用複寫時的 SWITCH 限制,請參閱複寫資料分割資料表和索引For SWITCH restriction when using replication, see Replicate Partitioned Tables and Indexes.

針對 [SQL Server]SQL Server 2016 CTP1 以及 SQL Database V12 之前版本 建立的非叢集資料行存放區索引是唯讀格式。Nonclustered columnstore indexes built for [SQL Server]SQL Server 2016 CTP1, and for SQL Database before version V12 were in a read-only format. 執行任何 PARTITION 作業之前,必須將非叢集資料行存放區索引重建成最新的格式 (可以更新的格式)。Nonclustered columnstore indexes must be rebuilt to the current format (which is updatable) before any PARTITION operations can be performed.

SET ( FILESTREAM_ON = { partition_scheme_name | filestream_filegroup_name | " default " | " NULL " })SET ( FILESTREAM_ON = { partition_scheme_name | filestream_filegroup_name | " default " | " NULL " })
適用於[SQL Server]SQL Server (SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017)。Applies to: [SQL Server]SQL Server ( SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017). Azure SQL DatabaseAzure SQL Database 不支援 FILESTREAMdoes not support FILESTREAM.

指定 FILESTREAM 資料存放的位置。Specifies where FILESTREAM data is stored.

具有 SET FILESTREAM_ON 子句的 ALTER TABLE 只有在資料表沒有任何 FILESTREAM 資料行時才會成功。ALTER TABLE with the SET FILESTREAM_ON clause will succeed only if the table has no FILESTREAM columns. 您可以使用第二個 ALTER TABLE 陳述式來加入 FILESTREAM 資料行。The FILESTREAM columns can be added by using a second ALTER TABLE statement.

如果指定了 partition_scheme_name,則會套用 CREATE TABLE 的規則。If partition_scheme_name is specified, the rules for CREATE TABLE apply. 資料表應該已經針對資料列資料進行分割區,且其分割區配置所使用的分割區函數和資料行必須與 FILESTREAM 分割區配置相同。The table should already be partitioned for row data, and its partition scheme must use the same partition function and columns as the FILESTREAM partition scheme.

filestream_filegroup_name 會指定 FILESTREAM 檔案群組的名稱。filestream_filegroup_name specifies the name of a FILESTREAM filegroup. 檔案群組必須要有一個使用 CREATE DATABASEALTER DATABASE 陳述式針對此檔案群組定義的檔案,否則會引發錯誤。The filegroup must have one file that is defined for the filegroup by using a CREATE DATABASE or ALTER DATABASE statement, or an error is raised.

" default " 會使用 DEFAULT 屬性集指定 FILESTREAM 檔案群組。" default " specifies the FILESTREAM filegroup with the DEFAULT property set. 如果沒有任何 FILESTREAM 檔案群組,就會引發錯誤。If there is no FILESTREAM filegroup, an error is raised.

" NULL " 會指定將資料表的 FILESTREAM 檔案群組的所有參考移除。" NULL " specifies that all references to FILESTREAM filegroups for the table will be removed. 首先必須卸除所有的 FILESTREAM 資料行。All FILESTREAM columns must be dropped first. 您必須使用 SET FILESTREAM_ON =" NULL " 來刪除與資料表相關聯的所有 FILESTREAM 資料。You must use SET FILESTREAM_ON =" NULL " to delete all FILESTREAM data that is associated with a table.

SET ( SYSTEM_VERSIONING = { OFF | ON [ ( HISTORY_TABLE = schema_name .SET ( SYSTEM_VERSIONING = { OFF | ON [ ( HISTORY_TABLE = schema_name . history_table_name [ , DATA_CONSISTENCY_CHECK = { ON | OFF } ] ) ] } )history_table_name [ , DATA_CONSISTENCY_CHECK = { ON | OFF } ] ) ] } )
適用於[SQL Server]SQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017),以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: [SQL Server]SQL Server ( SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database.

停用資料表的系統版本設定或啟用資料表的系統版本設定。Either disables system versioning of a table or enables system versioning of a table. 為啟用資料表的系統版本設定,系統會確認資料類型、可 Null 性條件約束、 主索引鍵條件約束是否符合系統版本設定的各需求。To enable system versioning of a table, the system verifies that the datatype, nullability constraint, and primary key constraint requirements for system versioning are met. 如果未使用 HISTORY_TABLE 引數,系統會產生一個與目前資料表之結構描述相符的新記錄資料表,並在兩個資料表之間建立連結,然後讓系統將目前資料表中的每一筆資料記錄到記錄資料表。If the HISTORY_TABLE argument is not used, the system generates a new history table matching the schema of the current table, creating a link between the two tables and enables the system to record the history of each record in the current table in the history table. 此記錄資料表的名稱將會是 MSSQL_TemporalHistoryFor<primary_table_object_id>The name of this history table will be MSSQL_TemporalHistoryFor<primary_table_object_id>. 如果使用 HISTORY_TABLE 引數來建立連結,並使用現有的記錄資料表,則會在目前的資料表和指定的資料表之間建立連結。If the HISTORY_TABLE argument is used to create a link to and use an existing history table, the link is created between the current table and the specified table. 建立現有記錄資料表的連結時,您可以選擇執行資料一致性檢查。When creating a link to an existing history table, you can choose to perform a data consistency check. 這個資料一致性檢查可確保現有的記錄不會重疊。This data consistency check ensures that existing records do not overlap. 預設執行資料一致性檢查。Performing the data consistency check is the default. 如需相關資訊,請參閱 Temporal TablesFor more information, see Temporal Tables.

HISTORY_RETENTION_PERIOD = { INFINITE | number {DAY | DAYS | WEEK | WEEKS | MONTH | MONTHS | YEAR | YEARS} } 適用於Azure SQL DatabaseAzure SQL DatabaseHISTORY_RETENTION_PERIOD = { INFINITE | number {DAY | DAYS | WEEK | WEEKS | MONTH | MONTHS | YEAR | YEARS} } Applies to: Azure SQL DatabaseAzure SQL Database.

為時態表中的歷程記錄資料指定有限或無限保留期。Specifies finite or infinite retention for historical data in temporal table. 如果省略,會使用無限保留期。If omitted, infinite retention is assumed.

SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )
適用於[SQL Server]SQL Server (SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017),以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: [SQL Server]SQL Server ( SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database.

指定資料表鎖定擴大的允許方法。Specifies the allowed methods of lock escalation for a table.

AUTOAUTO
此選項允許 SQL Server Database EngineSQL Server Database Engine 選取適用於資料表結構描述的鎖定擴大資料粒度。This option allows SQL Server Database EngineSQL Server Database Engine to select the lock escalation granularity that is appropriate for the table schema.

  • 如果資料表是分割區資料表,將允許鎖定擴大進行分割區。If the table is partitioned, lock escalation will be allowed to partition. 當鎖定擴大為分割區層級後,就無法進一步擴大為 TABLE 資料粒度。After the lock is escalated to the partition level, the lock will not be escalated later to TABLE granularity.
  • 如果資料表不是分割區,則會對 TABLE 資料粒度執行鎖定擴大。If the table is not partitioned, the lock escalation will be done to the TABLE granularity.

TABLETABLE
不論資料表是否為分割區資料表,鎖定擴大將在資料表層級的資料粒度上完成。Lock escalation will be done at table-level granularity regardless whether the table is partitioned or not partitioned. TABLE 為預設值。TABLE is the default value.

DISABLEDISABLE
在大多數情況下都避免使用鎖定擴大,Prevents lock escalation in most cases. 但並非完全不允許資料表層級的鎖定。Table-level locks are not completely disallowed. 例如,當您在可序列化隔離層級下掃描沒有任何叢集索引的資料表時,Database EngineDatabase Engine 必須採用資料表鎖定以保護資料的完整性。For example, when you are scanning a table that has no clustered index under the serializable isolation level, Database EngineDatabase Engine must take a table lock to protect data integrity.

REBUILDREBUILD
REBUILD WITH 語法可用來重建整個資料表,包括已分割區之資料表中的所有分割區。Use the REBUILD WITH syntax to rebuild an entire table including all the partitions in a partitioned table. 如果資料表有叢集索引,則 REBUILD 選項會重建叢集索引。If the table has a clustered index, the REBUILD option rebuilds the clustered index. REBUILD 可以執行為 ONLINE 作業。REBUILD can be performed as an ONLINE operation.

REBUILD PARTITION 語法可用來重建已分割區之資料表中的單一分割區。Use the REBUILD PARTITION syntax to rebuild a single partition in a partitioned table.

PARTITION = ALLPARTITION = ALL
適用於[SQL Server]SQL Server (SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017),以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: [SQL Server]SQL Server ( SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database.

在變更分割區壓縮設定時重建所有分割區。Rebuilds all partitions when changing the partition compression settings.

REBUILD WITH ( <rebuild_option> )REBUILD WITH ( <rebuild_option> )
所有選項都適用於具有叢集索引的資料表。All options apply to a table with a clustered index. 如果資料表沒有叢集索引,則只有其中一些選項會影響堆積結構。If the table does not have a clustered index, the heap structure is only affected by some of the options.

當特定壓縮設定並非使用 REBUILD 作業來指定時,就會使用分割區的目前壓縮設定。When a specific compression setting is not specified with the REBUILD operation, the current compression setting for the partition is used. 若要傳回目前設定,請查詢 sys.partitions 目錄檢視表中的 data_compression 資料行。To return the current setting, query the data_compression column in the sys.partitions catalog view.

如需重建選項的完整描述,請參閱 index_option (Transact-SQL)For complete descriptions of the rebuild options, see index_option (Transact-SQL).

DATA_COMPRESSIONDATA_COMPRESSION
適用於[SQL Server]SQL Server (SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017),以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: [SQL Server]SQL Server ( SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database.

針對指定的資料表、分割區編號或分割區範圍指定資料壓縮選項。Specifies the data compression option for the specified table, partition number, or range of partitions. 選項如下:The options are as follows:

NONE
不壓縮資料表或指定的分割區。Table or specified partitions are not compressed. 這不適用於資料行存放區資料表。This does not apply to columnstore tables.

ROWROW
使用資料列壓縮來壓縮資料表或指定的分割區。Table or specified partitions are compressed by using row compression. 這不適用於資料行存放區資料表。This does not apply to columnstore tables.

PAGEPAGE
使用頁面壓縮來壓縮資料表或指定的分割區。Table or specified partitions are compressed by using page compression. 這不適用於資料行存放區資料表。This does not apply to columnstore tables.

COLUMNSTORECOLUMNSTORE
適用於[SQL Server]SQL Server (SQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017),以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: [SQL Server]SQL Server ( SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database.

只適用於資料行存放區資料表。Applies only to columnstore tables. COLUMNSTORE 指定解壓縮之前以 COLUMNSTORE_ARCHIVE 選項壓縮的分割區。COLUMNSTORE specifies to decompress a partition that was compressed with the COLUMNSTORE_ARCHIVE option. 當還原資料時,資料將會繼續以用於所有資料行存放區資料表的資料行存放區壓縮來壓縮。When the data is restored, it will continue to be compressed with the columnstore compression that is used for all columnstore tables.

COLUMNSTORE_ARCHIVECOLUMNSTORE_ARCHIVE
適用於[SQL Server]SQL Server (SQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017),以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: [SQL Server]SQL Server ( SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database.

只適用於資料行存放區資料表,這些資料表會與叢集資料行存放區索引一起儲存。Applies only to columnstore tables, which are tables stored with a clustered columnstore index. COLUMNSTORE_ARCHIVE 將進一步將指定的分割區壓縮成較小的大小。COLUMNSTORE_ARCHIVE will further compress the specified partition to a smaller size. 這可用於封存,或是其他需要較少儲存體,而且可負擔更多時間來儲存和擷取的狀況This can be used for archival, or for other situations that require less storage and can afford more time for storage and retrieval

若要同時重建多個資料分割,請參閱 index_option (Transact-SQL)To rebuild multiple partitions at the same time, see index_option (Transact-SQL). 如果資料表沒有叢集索引,變更資料壓縮將會重建堆積和非叢集索引。If the table does not have a clustered index, changing the data compression rebuilds the heap and the nonclustered indexes. 如需與壓縮有關的詳細資訊,請參閱資料壓縮For more information about compression, see Data Compression.

ONLINE = { ON | OFF } <適用於 single_partition_rebuild_option>ONLINE = { ON | OFF } <as applies to single_partition_rebuild_option>
指定在索引作業期間,查詢和資料修改是否能夠使用基礎資料表的單一分割區和相關聯的索引。Specifies whether a single partition of the underlying tables and associated indexes are available for queries and data modification during the index operation. 預設值為 OFF。The default is OFF. REBUILD 可以執行為 ONLINE 作業。REBUILD can be performed as an ONLINE operation.

ONON
索引作業持續期間不會保留長期資料表鎖定。Long-term table locks are not held for the duration of the index operation. 在索引重建開始時,資料表上需要 S 鎖定,而在線上索引重建結束時,資料表上需要 Sch-M 鎖定。A S-lock on the table is required in the beginning of the index rebuild and a Sch-M lock on the table at the end of the online index rebuild. 雖然這兩個鎖定是短暫的中繼資料鎖定,尤其是 Sch-M 鎖定必須等候所有封鎖交易完成。Although both locks are short metadata locks, especially the Sch-M lock must wait for all blocking transactions to be completed. 在等候期間,Sch-M 鎖定會在存取相同資料表時,封鎖等待在這個鎖定之後的所有其他交易。During the wait time the Sch-M lock blocks all other transactions that wait behind this lock when accessing the same table.

注意

線上索引重建可設定本節稍後所述的 low_priority_lock_wait 選項。Online index rebuild can set the low_priority_lock_wait options described later in this section.

OFFOFF
在索引作業期間會套用資料表鎖定。Table locks are applied for the duration of the index operation. 這可防止所有使用者在作業持續期間存取基礎資料表。This prevents all user access to the underlying table for the duration of the operation.

column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNScolumn_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
適用於[SQL Server]SQL Server (SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017),以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: [SQL Server]SQL Server ( SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database.

這是資料行集的名稱。Is the name of the column set. 資料行集是不具類型的 XML 表示,可將資料表的所有疏鬆資料行結合到結構化輸出中。A column set is an untyped XML representation that combines all of the sparse columns of a table into a structured output. 如果資料表已包含疏鬆資料行,資料行集無法加入該資料表中。A column set cannot be added to a table that contains sparse columns. 如需資料行集的詳細資訊,請參閱 使用資料行集For more information about column sets, see Use Column Sets.

{ ENABLE | DISABLE } FILETABLE_NAMESPACE{ ENABLE | DISABLE } FILETABLE_NAMESPACE
適用於[SQL Server]SQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017)。Applies to: [SQL Server]SQL Server ( SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017).

啟用或停用 FileTable 的系統定義條件約束。Enables or disables the system-defined constraints on a FileTable. 只能用於 FileTable。Can only be used with a FileTable.

SET ( FILETABLE_DIRECTORY = directory_name )SET ( FILETABLE_DIRECTORY = directory_name )
適用於[SQL Server]SQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017)。Applies to: [SQL Server]SQL Server ) SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017. Azure SQL DatabaseAzure SQL Database 不支援 FILETABLEdoes not support FILETABLE.

指定 Windows 相容的 FileTable 目錄名稱。Specifies the Windows-compatible FileTable directory name. 在資料庫的所有 FileTable 目錄名稱之間,此名稱必須是唯一的。This name should be unique among all the FileTable directory names in the database. 不論 SQL 定序設定為何,唯一性比較不區分大小寫。Uniqueness comparison is case-insensitive, regardless of SQL collation settings. 只能用於 FileTable。Can only be used with a FileTable.

 SET (  
        REMOTE_DATA_ARCHIVE   
        {  
            = ON (  <table_stretch_options> )  
          | = OFF_WITHOUT_DATA_RECOVERY  
          ( MIGRATION_STATE = PAUSED ) | ( <table_stretch_options> [, ...n] )  
        } )  

適用於[SQL Server]SQL Server (SQL Server 2017 (14.x)SQL Server 2017 (14.x)SQL Server 2017SQL Server 2017)。Applies to: [SQL Server]SQL Server (SQL Server 2017 (14.x)SQL Server 2017 (14.x) through SQL Server 2017SQL Server 2017).

啟用或停用資料表的 Stretch Database。Enables or disables Stretch Database for a table. 如需詳細資訊,請參閱 Stretch DatabaseFor more info, see Stretch Database.

啟用資料表的 Stretch DatabaseEnabling Stretch Database for a table

當您透過指定 ON 來啟用資料表的 Stretch 時,您也必須指定 MIGRATION_STATE = OUTBOUND 來立即開始移轉資料或指定 MIGRATION_STATE = PAUSED 來延後移轉資料。When you enable Stretch for a table by specifying ON, you also have to specify MIGRATION_STATE = OUTBOUND to begin migrating data immediately, or MIGRATION_STATE = PAUSED to postpone data migration. 預設值是 MIGRATION_STATE = OUTBOUNDThe default value is MIGRATION_STATE = OUTBOUND. 如需如何為資料表啟用 Stretch 的詳細資訊,請參閱為資料表啟用 Stretch DatabaseFor more info about enabling Stretch for a table, see Enable Stretch Database for a table.

必要條件Prerequisites. 為資料表啟用 Stretch 之前,您必須在伺服器和資料庫上啟用 Stretch。Before you enable Stretch for a table, you have to enable Stretch on the server and on the database. 如需詳細資訊,請參閱 Enable Stretch Database for a databaseFor more info, see Enable Stretch Database for a database.

權限Permissions. 為資料庫或資料表啟用 Stretch 時,需要 db_owner 權限。Enabling Stretch for a database or a table requires db_owner permissions. 為資料表啟用 Stretch 時,也需要資料表的 ALTER 權限。Enabling Stretch for a table also requires ALTER permissions on the table.

為資料表停用 Stretch DatabaseDisabling Stretch Database for a table

當您為資料表停用 Stretch 時,有兩個選項可用於已移轉至 Azure 的遠端資料。When you disable Stretch for a table, you have two options for the remote data that has already been migrated to Azure. 如需詳細資訊,請參閱 停用 Stretch Database 並帶回遠端資料For more info, see Disable Stretch Database and bring back remote data.

  • 若要針對資料表停用 Stretch,並將資料表的遠端資料從 Azure複製回 SQL Server,請執行下列命令。To disable Stretch for a table and copy the remote data for the table from Azure back to SQL Server, run the following command. 無法取消此命令。This command can't be canceled.

ALTER TABLE <table name> SET ( REMOTE_DATA_ARCHIVE ( MIGRATION_STATE = INBOUND ) ) ;
```

 <span data-ttu-id="bd6cd-576">此作業會產生資料傳輸成本,且無法取消。</span><span class="sxs-lookup"><span data-stu-id="bd6cd-576">This operation incurs data transfer costs, and it can't be canceled.</span></span> <span data-ttu-id="bd6cd-577">如需詳細資訊,請參閱 [資料傳輸定價詳細資料](https://azure.microsoft.com/pricing/details/data-transfers/)。</span><span class="sxs-lookup"><span data-stu-id="bd6cd-577">For more info, see [Data Transfers Pricing Details](https://azure.microsoft.com/pricing/details/data-transfers/).</span></span>  

 <span data-ttu-id="bd6cd-578">將所有遠端資料從 Azure 複製回 SQL Server 後,即會針對資料表停用 Stretch。</span><span class="sxs-lookup"><span data-stu-id="bd6cd-578">After all the remote data has been copied from Azure back to SQL Server, Stretch is disabled for the table.</span></span>  
  • 若要針對資料表停用 Stretch 並放棄遠端資料,請執行下列命令。To disable Stretch for a table and abandon the remote data, run the following command.

ALTER TABLE <table_name> SET ( REMOTE_DATA_ARCHIVE = OFF_WITHOUT_DATA_RECOVERY ( MIGRATION_STATE = PAUSED ) ) ;
```

針對資料表停用 Stretch Database 後,即會停止執行資料移轉作業,且查詢結果不再包含來自遠端資料表的結果。After you disable Stretch Database for a table, data migration stops and query results no longer include results from the remote table.

停用 Stretch 時,並不會移除遠端資料表。Disabling Stretch does not remove the remote table. 若您想要刪除遠端資料表,則必須使用 Azure 管理入口網站將其卸除。If you want to delete the remote table, you have to drop it by using the Azure management portal.

[ FILTER_PREDICATE = { null | predicate } ][ FILTER_PREDICATE = { null | predicate } ]
適用於[SQL Server]SQL Server (SQL Server 2017 (14.x)SQL Server 2017 (14.x)SQL Server 2017SQL Server 2017)。Applies to: [SQL Server]SQL Server (SQL Server 2017 (14.x)SQL Server 2017 (14.x) through SQL Server 2017SQL Server 2017).

您現在可以指定一個篩選述詞,以選取要從同時包含歷史資料和目前資料的資料表中移轉哪些資料列。Optionally specifies a filter predicate to select rows to migrate from a table that contains both historical and current data. 此述詞必須呼叫確定性內嵌資料表值函式。The predicate must call a deterministic inline table-valued function. 如需詳細資訊,請參閱為資料表啟用 Stretch Database使用篩選函數來選取要移轉的資料列 (Stretch Database)For more info, see Enable Stretch Database for a table and Select rows to migrate by using a filter function (Stretch Database).

重要

若您提供執行狀況不佳的篩選器述詞,資料移轉也無法順利執行。If you provide a filter predicate that performs poorly, data migration also performs poorly. Stretch Database 使用 CROSS APPLY 運算子,將篩選器述詞套用至資料表。Stretch Database applies the filter predicate to the table by using the CROSS APPLY operator.

若您未指定篩選器述詞,則會遷移整個資料表。If you don't specify a filter predicate, the entire table is migrated.

當您指定篩選述詞時,也必須指定 MIGRATION_STATEWhen you specify a filter predicate, you also have to specify MIGRATION_STATE.

MIGRATION_STATE = { OUTBOUND | INBOUND | PAUSED }MIGRATION_STATE = { OUTBOUND | INBOUND | PAUSED }
適用於[SQL Server]SQL Server (SQL Server 2017 (14.x)SQL Server 2017 (14.x)SQL Server 2017SQL Server 2017)。Applies to: [SQL Server]SQL Server (SQL Server 2017 (14.x)SQL Server 2017 (14.x) through SQL Server 2017SQL Server 2017).

WAIT_AT_LOW_PRIORITYWAIT_AT_LOW_PRIORITY
適用於[SQL Server]SQL Server (SQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017),以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: [SQL Server]SQL Server ( SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database.

線上索引重建必須等候這個資料表的封鎖作業。An online index rebuild has to wait for blocking operations on this table. WAIT_AT_LOW_PRIORITY 表示線上索引重建作業將會等候低優先順序鎖定,讓其他作業在線上索引建立作業等候時繼續進行。WAIT_AT_LOW_PRIORITY indicates that the online index rebuild operation will wait for low priority locks, allowing other operations to proceed while the online index build operation is waiting. 省略 WAIT AT LOW PRIORITY 選項相當於 WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE)Omitting the WAIT AT LOW PRIORITY option is equivalent to WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE).

MAX_DURATION = time [MINUTES ]MAX_DURATION = time [MINUTES ]
適用於[SQL Server]SQL Server (SQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017),以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: [SQL Server]SQL Server ( SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database.

執行 DDL 命令時, SWITCH 或線上索引重建鎖定將會以低優先順序等候的等候時間 (以分鐘為單位指定的整數值)。The wait time (an integer value specified in minutes) that the SWITCH or online index rebuild locks will wait with low priority when executing the DDL command. 如果作業被封鎖時間長度達到在 MAX_DURATION 中指定的時間,則會執行其中一個 ABORT_AFTER_WAIT 動作。If the operation is blocked for the MAX_DURATION time, one of the ABORT_AFTER_WAIT actions will be executed. MAX_DURATION 時間一律以分鐘為單位,而且可以省略 MINUTES 這個字。MAX_DURATION time is always in minutes, and the word MINUTES can be omitted.

ABORT_AFTER_WAIT = [NONE | SELF | BLOCKERS } ]ABORT_AFTER_WAIT = [NONE | SELF | BLOCKERS } ]
適用於[SQL Server]SQL Server (SQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017),以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: [SQL Server]SQL Server ( SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database.

NONE
繼續等候一般 (標準) 優先權的鎖定。Continue waiting for the lock with normal (regular) priority.

SELFSELF
結束目前正在執行的 SWITCH 或線上索引重建 DDL 作業,但不採取任何動作。Exit the SWITCH or online index rebuild DDL operation currently being executed without taking any action.

BLOCKERSBLOCKERS
終止目前封鎖 SWITCH 或線上索引重建 DDL 作業的所有使用者交易,讓作業可以繼續。Kill all user transactions that block currently the SWITCH or online index rebuild DDL operation so that the operation can continue.

需要 ALTER ANY CONNECTION 權限。Requires ALTER ANY CONNECTION permission.

IF EXISTSIF EXISTS
適用於[SQL Server]SQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017),以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: [SQL Server]SQL Server ( SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database.

只有在已經存在的情況下,才有條件地卸除資料行或條件約束。Conditionally drops the column or constraint only if it already exists.

RemarksRemarks

若要加入新的資料列,請使用 INSERTTo add new rows of data, use INSERT. 若要移除資料列,請使用 DELETETRUNCATE TABLETo remove rows of data, use DELETE or TRUNCATE TABLE. 若要變更現有資料列中的值,請使用 UPDATETo change the values in existing rows, use UPDATE.

如果參考資料表的程序快取中有任何執行計畫,ALTER TABLE 會加以標示,以便在下一次執行時重新編譯。If there are any execution plans in the procedure cache that reference the table, ALTER TABLE marks them to be recompiled on their next execution.

變更資料行的大小Changing the Size of a Column

您可以在 ALTER COLUMN 子句中指定資料行資料類型的新大小,以變更資料行的長度、有效位數或小數位數。You can change the length, precision, or scale of a column by specifying a new size for the column data type in the ALTER COLUMN clause. 如果資料行中有資料存在,則新大小不得小於資料的最大大小。If data exists in the column, the new size cannot be smaller than the maximum size of the data. 此外,除非資料行是 varcharnvarcharvarbinary 資料類型,且索引不是 PRIMARY KEY 條件約束的結果,否則不能在索引中定義資料行。Also, the column cannot be defined in an index, unless the column is a varchar, nvarchar, or varbinary data type and the index is not the result of a PRIMARY KEY constraint. 請參閱範例 P。See example P.

鎖定和 ALTER TABLELocks and ALTER TABLE

ALTER TABLE 中指定的變更會立即實作。The changes specified in ALTER TABLE are implemented immediately. 如果變更作業需要修改資料表中的資料列,ALTER TABLE 會更新資料列。If the changes require modifications of the rows in the table, ALTER TABLE updates the rows. ALTER TABLE 會取得資料表上的結構描述修改 (SCH-M) 鎖定,以確定下列事項:在變更期間,除了在結束時需要非常短暫的 SCH-M 鎖定之線上索引作業以外,沒有其他連接會參考資料表的中繼資料。ALTER TABLE acquires a schema modify (SCH-M) lock on the table to make sure that no other connections reference even the metadata for the table during the change, except online index operations that require a very short SCH-M lock at the end. ALTER TABLE...SWITCH 作業中,會同時在來源資料表與目標資料表上取得鎖定。In an ALTER TABLE...SWITCH operation, the lock is acquired on both the source and target tables. 對資料表所做的修改會記錄下來,且完全可復原。The modifications made to the table are logged and fully recoverable. 影響每份極大型資料表中之所有資料列的變更 (例如,卸除資料行或在某些 [SQL Server]SQL Server 版本上加入含有預設值的 NOT NULL 資料行) 可能需要很長的時間才能完成及產生多個記錄。Changes that affect all the rows in very large tables, such as dropping a column or, on some editions of [SQL Server]SQL Server, adding a NOT NULL column with a default value, can take a long time to complete and generate many log records. 執行這些 ALTER TABLE 陳述式時,要像執行任何影響多個資料列的 INSERT、UPDATE 或 DELETE 陳述式時一樣地小心。These ALTER TABLE statements should be executed with the same care as any INSERT, UPDATE, or DELETE statement that affects many rows.

以線上作業的方式加入 NOT NULL 資料行Adding NOT NULL Columns as an Online Operation

SQL Server 2012 (11.x)SQL Server 2012 (11.x) Enterprise Edition 開始,當預設值是「執行階段常數」時,加入含有預設值的 NOT NULL 資料行就是線上作業。Starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x) Enterprise Edition, adding a NOT NULL column with a default value is an online operation when the default value is a runtime constant. 這表示,不論資料表中的資料列數目為何,此作業幾乎會立即完成。This means that the operation is completed almost instantaneously regardless of the number of rows in the table. 這是因為資料表中的現有資料列不會在作業期間更新。而是,預設值只會儲存在資料表的中繼資料內,而且存取這些資料列的查詢會視需要查閱此值。This is because the existing rows in the table are not updated during the operation; instead, the default value is stored only in the metadata of the table and the value is looked up as needed in queries that access these rows. 這種行為是自動的。不需要任何額外的語法,即可實作超越 ADD COLUMN 語法的線上作業。This behavior is automatic; no additional syntax is required to implement the online operation beyond the ADD COLUMN syntax. 執行階段常數是一種運算式,它會在執行階段針對資料表中的每個資料列產生相同的值,不論其決定性為何。A runtime constant is an expression that produces the same value at runtime for each row in the table regardless of its determinism. 例如,常數運算式 "My temporary data" 或系統函數 GETUTCDATETIME() 都是執行階段常數。For example, the constant expression "My temporary data", or the system function GETUTCDATETIME() are runtime constants. 相反地,NEWID()NEWSEQUENTIALID() 函數不是執行階段常數,因為系統會針對資料表中的每個資料列產生唯一值。In contrast, the functions NEWID() or NEWSEQUENTIALID() are not runtime constants because a unique value is produced for each row in the table. 加入含有非執行階段常數之預設值的 NOT NULL 資料行一律以離線方式執行,而且系統會在作業期間取得獨佔 (SCH-M) 鎖定。Adding a NOT NULL column with a default value that is not a runtime constant is always performed offline and an exclusive (SCH-M) lock is acquired for the duration of the operation.

當現有的資料列參考中繼資料內儲存的值時,如果已插入任何新資料列但並未針對資料行指定另一個值,預設值就會儲存在資料列上。While the existing rows reference the value stored in metadata, the default value is stored on the row for any new rows that are inserted and do not specify another value for the column. 當您更新資料列 (即使沒有在 UPDATE 陳述式中指定實際的資料行) 或是重建資料表或叢集索引時,中繼資料內儲存的預設值會移至現有的資料列。The default value stored in metadata is moved to an existing row when the row is updated (even if the actual column is not specified in the UPDATE statement), or if the table or clustered index is rebuilt.

類型為 varchar(max)nvarchar(max)varbinary(max)xml, textntextimagehierarchyidgeometrygeography 或 CLR UDTS 的資料行,不可以在線上作業中加入。Columns of type varchar(max), nvarchar(max), varbinary(max), xml, text, ntext, image, hierarchyid, geometry, geography, or CLR UDTS, cannot be added in an online operation. 您無法線上加入資料行,如果這樣做,就會導致最大可能的資料列大小超過 8,060 位元組限制。A column cannot be added online if doing so causes the maximum possible row size to exceed the 8,060 byte limit. 在此情況中,資料行會以離線作業的方式加入。The column is added as an offline operation in this case.

平行計畫執行Parallel Plan Execution

Microsoft SQL Server 2012 EnterpriseMicrosoft SQL Server 2012 Enterprise 和更新版本中,用來執行單一 ALTER TABLE ADD (以索引為基礎) CONSTRAINT 或 DROP (叢集索引) CONSTRAINT 陳述式的處理器數目,取決於 max degree of parallelism 設定選項和目前的工作負載。In Microsoft SQL Server 2012 EnterpriseMicrosoft SQL Server 2012 Enterprise and higher, the number of processors employed to run a single ALTER TABLE ADD (index based) CONSTRAINT or DROP (clustered index) CONSTRAINT statement is determined by the max degree of parallelism configuration option and the current workload. 如果 Database EngineDatabase Engine 偵測到系統在忙碌中,則在開始執行陳述式之前,會先自動降低作業平行原則的程度。If the Database EngineDatabase Engine detects that the system is busy, the degree of parallelism of the operation is automatically reduced before statement execution starts. 您可以指定 MAXDOP 選項,手動設定用來執行陳述式的處理器數目。You can manually configure the number of processors that are used to run the statement by specifying the MAXDOP option. 如需詳細資訊,請參閱 設定 max degree of parallelism 伺服器組態選項For more information, see Configure the max degree of parallelism Server Configuration Option.

分割區資料表Partitioned Tables

除了執行涉及分割區資料表的 SWITCH 作業以外,ALTER TABLE 還可用來變更分割區資料表之資料行、條件約束及觸發程序的狀態,就像它是用於非分割區資料表一樣。In addition to performing SWITCH operations that involve partitioned tables, ALTER TABLE can be used to change the state of the columns, constraints, and triggers of a partitioned table just like it is used for nonpartitioned tables. 不過,這個陳述式不可用來變更資料表本身的分割區方式。However, this statement cannot be used to change the way the table itself is partitioned. 若要重新分割某個資料分割資料表,請使用 ALTER PARTITION SCHEMEALTER PARTITION FUNCTIONTo repartition a partitioned table, use ALTER PARTITION SCHEME and ALTER PARTITION FUNCTION. 此外,您也不能變更分割區資料表的資料行資料類型。Additionally, you cannot change the data type of a column of a partitioned table.

含有結構描述繫結檢視表之資料表的限制Restrictions on Tables with Schema-Bound Views

適用於含有結構描述繫結檢視之資料表上的 ALTER TABLE 陳述式的限制,跟修改含有簡式索引之資料表時目前所適用的限制一樣。The restrictions that apply to ALTER TABLE statements on tables with schema-bound views are the same as the restrictions currently applied when modifying tables with a simple index. 允許加入資料行。Adding a column is allowed. 不過,不允許移除或變更參與任何結構描述繫結檢視表的資料行。However, removing or changing a column that participates in any schema-bound view is not allowed. 如果 ALTER TABLE 陳述式需要變更結構描述繫結檢視中使用的資料行,ALTER TABLE 會失敗,且 Database EngineDatabase Engine 會引發錯誤訊息。If the ALTER TABLE statement requires changing a column used in a schema-bound view, ALTER TABLE fails and the Database EngineDatabase Engine raises an error message. 如需有關結構描述繫結和索引檢視表的詳細資訊,請參閱 CREATE VIEW (Transact-SQL)For more information about schema binding and indexed views, see CREATE VIEW (Transact-SQL).

建立參考資料表的結構描述繫結檢視,不會影響基底資料表上之觸發程序的新增或移除。Adding or removing triggers on base tables is not affected by creating a schema-bound view that references the tables.

索引和 ALTER TABLEIndexes and ALTER TABLE

當條件約束卸除時,建立為條件約束之一部分的索引也會卸除。Indexes created as part of a constraint are dropped when the constraint is dropped. 必須利用 DROP INDEX 來卸除利用 CREATE INDEX 建立的索引。Indexes that were created with CREATE INDEX must be dropped with DROP INDEX. ALTER INDEX 陳述式可用來重建條件約束定義中索引的部分;不必卸除條件約束,然後又利用 ALTER TABLE 新增條件約束。The ALTER INDEX statement can be used to rebuild an index part of a constraint definition; the constraint does not have to be dropped and added again with ALTER TABLE.

若要移除某資料行,必須先移除以該資料行為基礎的所有索引和條件約束。All indexes and constraints based on a column must be removed before the column can be removed.

刪除建立叢集索引的條件約束時,儲存在叢集索引分葉層級中的資料列會儲存在非叢集資料表中。When a constraint that created a clustered index is deleted, the data rows that were stored in the leaf level of the clustered index are stored in a nonclustered table. 您可以卸除叢集索引,再藉由指定 MOVE TO 選項,於單一交易中將結果資料表移到另一個檔案群組或分割區配置。You can drop the clustered index and move the resulting table to another filegroup or partition scheme in a single transaction by specifying the MOVE TO option. MOVE TO 選項有下列限制:The MOVE TO option has the following restrictions:

  • MOVE TO 對於索引檢視表或非叢集索引無效。MOVE TO is not valid for indexed views or nonclustered indexes.
  • 分割區配置或檔案群組必須已經存在。The partition scheme or filegroup must already exist.
  • 如果未指定 MOVE TO,資料表會放在針對叢集索引定義的相同分割區配置或檔案群組中。If MOVE TO is not specified, the table will be located in the same partition scheme or filegroup as was defined for the clustered index.

當您卸除叢集索引時,您可以指定 ONLINE = ON 選項,使 DROP INDEX 交易不會封鎖對底層資料和相關聯之非叢集索引的查詢和修改。When you drop a clustered index, you can specify ONLINE = ON option so the DROP INDEX transaction does not block queries and modifications to the underlying data and associated nonclustered indexes.

ONLINE = ON 有下列限制:ONLINE = ON has the following restrictions:

  • ONLINE = ON 不適用於也已停用的叢集索引。ONLINE = ON is not valid for clustered indexes that are also disabled. 停用的索引必須利用 ONLINE = OFF 來卸除。Disabled indexes must be dropped by using ONLINE = OFF.
  • 一次只能卸除一個索引。Only one index at a time can be dropped.
  • ONLINE = ON 不適用於索引檢視、非叢集索引或本機暫存資料表上的索引。ONLINE = ON is not valid for indexed views, nonclustered indexes or indexes on local temp tables.
  • ONLINE = ON 對於資料行存放區索引而言無效。ONLINE = ON is not valid for columnstore indexes.

卸除叢集索引時,需要一個大小等於現有叢集索引的暫存磁碟空間。Temporary disk space equal to the size of the existing clustered index is required to drop a clustered index. 作業完成時,會立即釋放此額外空間。This additional space is released as soon as the operation is completed.

注意

<drop_clustered_constraint_option> 底下所列的選項可套用到資料表上的叢集索引,但不可套用到檢視表上的叢集索引或套用至非叢集索引。The options listed under <drop_clustered_constraint_option> apply to clustered indexes on tables and cannot be applied to clustered indexes on views or nonclustered indexes.

複寫結構描述變更Replicating Schema Changes

根據預設,當您在 [SQL Server]SQL Server 發行者的已發行資料表上執行 ALTER TABLE 時,該項變更就會傳播到所有 [SQL Server]SQL Server 訂閱者。By default, when you run ALTER TABLE on a published table at a [SQL Server]SQL Server Publisher, that change is propagated to all [SQL Server]SQL Server Subscribers. 這項功能具有某些限制,而且可停用。This functionality has some restrictions and can be disabled. 如需詳細資訊,請參閱對發行集資料庫進行結構描述變更For more information, see Make Schema Changes on Publication Databases.

資料壓縮Data Compression

系統資料表無法啟用壓縮。System tables cannot be enabled for compression. 如果資料表是堆積,ONLINE 模式的重建作業將會是單一執行緒。If the table is a heap, the rebuild operation for ONLINE mode will be single threaded. 請針對多執行緒的堆積重建作業使用 OFFLINE 模式。Use OFFLINE mode for a multi-threaded heap rebuild operation. 如需資料壓縮的詳細資訊,請參閱資料壓縮For a more information about data compression, seeData Compression.

若要評估變更壓縮狀態如何影響資料表、索引或分割區,請使用 sp_estimate_data_compression_savings 預存程序。To evaluate how changing the compression state will affect a table, an index, or a partition, use the sp_estimate_data_compression_savings stored procedure.

下列限制適用於分割區資料表:The following restrictions apply to partitioned tables:

  • 您無法在資料表具有非對齊索引時變更單一分割區的壓縮設定。You cannot change the compression setting of a single partition if the table has nonaligned indexes.
  • ALTER TABLE <table> REBUILD PARTITION ... 語法可重建指定的資料分割。The ALTER TABLE <table> REBUILD PARTITION ... syntax rebuilds the specified partition.
  • ALTER TABLE <table> REBUILD WITH ... 語法可重建所有資料分割。The ALTER TABLE <table> REBUILD WITH ... syntax rebuilds all partitions.

卸除 NTEXT 資料行Dropping NTEXT Columns

卸除 NTEXT 資料行時,已刪除資料的清除作業會在所有資料列上的序列化作業執行時發生。When dropping NTEXT columns, the cleanup of the deleted data occurs as a serialized operation on all rows. 這可能需要相當長的時間。This can require a substantial time. 卸除包含大量資料列的資料表中的 NTEXT 資料行時,請先將 NTEXT 更新為 NULL 值,再卸除該資料行。When dropping an NTEXT column in a table with a large number rows, update the NTEXT column to NULL value first, then drop the column. 這可透過平行作業執行,而且速度加快許多。This can be performed with parallel operations and can be much faster.

線上索引重建Online Index Rebuild

為了執行線上索引重建的 DDL 陳述式,特定資料表上執行的所有使用中封鎖交易都必須完成。In order to execute the DDL statement for an online index rebuild, all active blocking transactions running on a particular table must be completed. 當線上索引重建執行時,它會封鎖這個資料表上準備開始執行的所有新交易。When the online index rebuild executes, it blocks all new transactions that are ready to start execution on this table. 雖然線上索引重建的鎖定期間很短,但是等候特定資料表上所有未完成的交易完成並封鎖要開始的新交易,可能會大幅影響輸送量,導致工作負載速度變慢或逾時,並且大幅限制對基礎資料表的存取。Although the duration of the lock for online index rebuild is very short, waiting for all open transactions on a given table to complete and blocking the new transactions to start, might significantly affect the throughput, causing a workload slow down or timeout, and significantly limit access to the underlying table. WAIT_AT_LOW_PRIORITY 選項可讓 DBA 管理線上索引重建所需的 S 鎖定和 Sch-M 鎖定,並允許它們選取 3 個選項的其中一個。The WAIT_AT_LOW_PRIORITY option allows DBA's to manage the S-lock and Sch-M locks required for online index rebuilds and allows them to select one of 3 options. 在這 3 個案例中,如果在等候期間 ((MAX_DURATION =n [minutes])) 沒有封鎖活動,線上索引重建會立即執行而不等候,並 DDL 陳述式會完成。In all 3 cases, if during the wait time ( (MAX_DURATION =n [minutes]) ) there are no blocking activities, the online index rebuild is executed immediately without waiting and the DDL statement is completed.

相容性支援Compatibility Support

ALTER TABLE 陳述式只允許兩部分 (schema.object) 資料表名稱。The ALTER TABLE statement allows only two-part (schema.object) table names. SQL Server 2017SQL Server 2017 中,使用下列格式來指定資料表名稱會在編譯階段失敗,並顯示錯誤 117。In SQL Server 2017SQL Server 2017, specifying a table name using the following formats fails at compile time with error 117.

  • server.database.schema.tableserver.database.schema.table
  • .database.schema.table.database.schema.table
  • ..schema.table..schema.table

在舊版中,指定 server.database.schema.table 格式會傳回錯誤 4902。In earlier versions specifying the format server.database.schema.table returned error 4902. 不過,指定 .database.schema.table 格式或 ..schema.table 格式會成功。Specifying the format .database.schema.table or the format ..schema.table succeeded.

若要解決此問題,請移除 4 部分前置詞的用法。To resolve the problem, remove the use of a 4-part prefix.

[權限]Permissions

需要資料表的 ALTER 權限。Requires ALTER permission on the table.

ALTER TABLE 權限可套用至涉及 ALTER TABLE SWITCH 陳述式的兩種資料表。ALTER TABLE permissions apply to both tables involved in an ALTER TABLE SWITCH statement. 所切換的任何資料,都會繼承目標資料表的安全性。Any data that is switched inherits the security of the target table.

如果將 ALTER TABLE 陳述式中的任何資料行定義為屬於 Common Language Runtime (CLR) 使用者定義類型或別名資料類型,則需要對該類型具有 REFERENCES 權限。If any columns in the ALTER TABLE statement are defined to be of a common language runtime (CLR) user-defined type or alias data type, REFERENCES permission on the type is required.

加入會更新資料表之資料列的資料行時,需要該資料表的 UPDATE 權限。Adding a column that updates the rows of the table requires UPDATE permission on the table. 例如,當資料表非空白時,加入具有預設值的 NOT NULL 資料行或加入識別欄位。For example, adding a NOT NULL column with a default value or adding an identity column when the table is not empty.

範例Examples

類別目錄Category 代表性語法元素Featured syntax elements
加入資料行和條件約束Adding columns and constraints ADD • 含索引選項的 PRIMARY KEY • 疏鬆資料行和資料行集 •ADD • PRIMARY KEY with index options • sparse columns and column sets •
卸除資料行和條件約束Dropping columns and constraints DROPDROP
修改資料行定義Altering a column definition 變更資料類型 • 變更資料行大小 • 定序change data type • change column size • collation
修改資料表定義Altering a table definition DATA_COMPRESSION • SWITCH PARTITION • LOCK ESCALATION • 變更追蹤DATA_COMPRESSION • SWITCH PARTITION • LOCK ESCALATION • change tracking
停用和啟用條件約束與觸發程序Disabling and enabling constraints and triggers CHECK • NO CHECK • ENABLE TRIGGER • DISABLE TRIGGERCHECK • NO CHECK • ENABLE TRIGGER • DISABLE TRIGGER

加入資料行和條件約束Adding Columns and Constraints

本節中的範例示範如何將資料行和條件約束加入至資料表。Examples in this section demonstrate adding columns and constraints to a table.

A.A. 加入新資料行Adding a new column

下列範例會加入一個資料行,該資料行允許 Null 值,且不含利用 DEFAULT 定義提供的值。The following example adds a column that allows null values and has no values provided through a DEFAULT definition. 在這個新資料行中,每一個資料列都會有 NULLIn the new column, each row will have NULL.

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

B.B. 加入含有條件約束的資料行Adding a column with a constraint

下列範例會加入一個含有 UNIQUE 條件約束的新資料行。The following example adds a new column with a UNIQUE constraint.

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  

C.C. 將未確認的 CHECK 條件約束加入現有的資料行Adding an unverified CHECK constraint to an existing column

下列範例將條件約束加入至資料表中的現有資料行。The following example adds a constraint to an existing column in the table. 該資料行含有違反該條件約束的值。The column has a value that violates the constraint. 因此,WITH NOCHECK 可用來防止根據現有的資料列來驗證條件約束,並允許加入條件約束。Therefore, WITH NOCHECK is used to prevent the constraint from being validated against existing rows, and to allow for the constraint to be added.

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  

D.D. 將 DEFAULT 條件約束加入現有的資料行Adding a DEFAULT constraint to an existing column

下列範例會建立一份含有兩個資料行的資料表、在第一個資料行插入值,並讓另一個資料行保持 NULL。The following example creates a table with two columns and inserts a value into the first column, and the other column remains NULL. 然後再將 DEFAULT 條件約束加入至第二個資料行。A DEFAULT constraint is then added to the second column. 若要確認已套用預設值,請在第一個資料行中插入其他值,然後查詢資料表。To verify that the default is applied, another value is inserted into the first column, and the table is queried.

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  

E.E. 加入數個含有條件約束的資料行Adding several columns with constraints

下列範例會加入數個資料行,這些資料行含有利用新資料行定義的條件約束。The following example adds several columns with constraints defined with the new column. 第一個新資料行有 IDENTITY 屬性。The first new column has an IDENTITY property. 資料表中的每一個資料列在識別欄位中都有新的累加值。Each row in the table has new incremental values in the identity column.

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  

F.F. 加入含有預設值並可為 Null 的資料行Adding a nullable column with default values

下列範例會加入含有 DEFAULT 定義且可為 Null 的資料行,並利用 WITH VALUES 為資料表中的每一個現有資料列提供值。The following example adds a nullable column with a DEFAULT definition, and uses WITH VALUES to provide values for each existing row in the table. 如果沒有使用 WITH VALUES,每一個資料列的新資料行中都會有 NULL 值。If WITH VALUES is not used, each row has the value NULL in the new column.

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  

G.G. 建立具有索引或資料壓縮選項的 PRIMARY KEY 條件約束Creating a PRIMARY KEY constraint with index or data compression options

下列範例會建立 PRIMARY KEY 條件約束 PK_TransactionHistoryArchive_TransactionID,並設定選項 FILLFACTORONLINEPAD_INDEXThe following example creates the PRIMARY KEY constraint PK_TransactionHistoryArchive_TransactionID and sets the options FILLFACTOR, ONLINE, and PAD_INDEX. 產生的叢集索引將與條件約束同名。The resulting clustered index will have the same name as the constraint.

適用於SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017 以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017 and Azure SQL DatabaseAzure SQL Database.

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  

此類似範例會在套用叢集主索引鍵時,套用頁面壓縮。This similar example applies page compression while applying the clustered primary key.

USE AdventureWorks;  
GO  
ALTER TABLE Production.TransactionHistoryArchive WITH NOCHECK   
ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID)  
WITH (DATA_COMPRESSION = PAGE);  
GO  

H.H. 加入疏鬆資料行Adding a sparse column

下列範例示範如何在資料表 T1 中加入及修改疏鬆資料行。The following examples show adding and modifying sparse columns in table T1. 建立資料表 T1 的程式碼如下。The code to create table T1 is as follows.

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

若要加入其他的疏鬆資料行 C5,請執行下列陳述式。To add an additional sparse column C5, execute the following statement.

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

若要將 C4 非疏鬆資料行轉換成疏鬆資料行,請執行下列陳述式。To convert the C4 non-sparse column to a sparse column, execute the following statement.

ALTER TABLE T1  
ALTER COLUMN C4 ADD SPARSE ;  
GO  

若要將 C4 疏鬆資料行轉換成非疏鬆資料行,請執行下列陳述式。To convert the C4 sparse column to a nonsparse column, execute the following statement.

ALTER TABLE T1  
ALTER COLUMN C4 DROP SPARSE;  
GO  

I.I. 加入資料行集Adding a column set

下列範例示範如何將資料行加入至資料表 T2The following examples show adding a column to table T2. 如果資料表已包含疏鬆資料行,就無法在該資料表中加入資料行集。A column set cannot be added to a table that already contains sparse columns. 建立資料表 T2 的程式碼如下。The code to create table T2 is as follows.

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

下列三個陳述式會加入名為 CS 的資料行集,然後將資料行 C2C3 修改為 SPARSEThe following three statements add a column set named CS, and then modify columns C2 and C3 to 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  

J.J. 加入加密的資料行Adding an encrypted column

下列陳述式會加入名為 PromotionCode 的加密資料行。The following statement adds an encrypted column named PromotionCode.

ALTER TABLE Customers ADD  
    PromotionCode nvarchar(100)   
    ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = MyCEK,  
    ENCRYPTION_TYPE = RANDOMIZED,  
    ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') ;  

卸除資料行和條件約束Dropping Columns and Constraints

本節中的範例示範如何卸除資料行和條件約束。The examples in this section demonstrate dropping columns and constraints.

A.A. 卸除資料行Dropping a column or columns

第一個範例會修改資料表來移除資料行。The first example modifies a table to remove a column. 第二個範例會移除多個資料行。The second example removes multiple columns.

CREATE TABLE dbo.doc_exb   
    (column_a INT  
     ,column_b VARCHAR(20) NULL  
     ,column_c datetime  
     ,column_d int) ;  
GO  
-- Remove a single column.  
ALTER TABLE dbo.doc_exb DROP COLUMN column_b ;  
GO  
-- Remove multiple columns.  
ALTER TABLE dbo.doc_exb DROP COLUMN column_c, column_d;  

B.B. 卸除條件約束和資料行Dropping constraints and columns

第一個範例會從資料表中移除 UNIQUE 條件約束。The first example removes a UNIQUE constraint from a table. 第二個範例會移除兩個條件約束與單一資料行。The second example removes two constraints and a single column.

CREATE TABLE dbo.doc_exc ( column_a int NOT NULL CONSTRAINT my_constraint UNIQUE) ;  
GO  
  
-- Example 1. Remove a single constraint.  
ALTER TABLE dbo.doc_exc DROP my_constraint ;  
GO  
  
DROP TABLE dbo.doc_exc;  
GO  
  
CREATE TABLE dbo.doc_exc ( column_a int    
                          NOT NULL CONSTRAINT my_constraint UNIQUE  
                          ,column_b int   
                          NOT NULL CONSTRAINT my_pk_constraint PRIMARY KEY) ;  
GO  
  
-- Example 2. Remove two constraints and one column  
-- The keyword CONSTRAINT is optional. The keyword COLUMN is required.  
ALTER TABLE dbo.doc_exc   
  
    DROP CONSTRAINT CONSTRAINT my_constraint, my_pk_constraint, COLUMN column_b ;  
GO  

C.C. 在 ONLINE 模式中卸除 PRIMARY KEY 條件約束Dropping a PRIMARY KEY constraint in the ONLINE mode

下列範例會刪除 PRIMARY KEY 條件約束,並將 ONLINE 選項設為 ONThe following example deletes a PRIMARY KEY constraint with the ONLINE option set to ON.

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

D.D. 加入或卸除 FOREIGN KEY 條件約束Adding and dropping a FOREIGN KEY constraint

下列範例會建立資料表 ContactBackup,然後改變該資料表,方式是先加入一個參考 FOREIGN KEY 資料表的 Person.Person 條件約束,再卸除 FOREIGN KEY 條件約束。The following example creates the table ContactBackup, and then alters the table, first by adding a FOREIGN KEY constraint that references the table Person.Person, then by dropping the FOREIGN KEY constraint.

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

搭配 [回到頁首] 連結使用的箭號圖示 範例Arrow icon used with Back to Top link Examples

修改資料行定義Altering a Column Definition

A.A. 變更資料行的資料類型Changing the data type of a column

下列範例會將資料表的資料行從 INT 變更為 DECIMALThe following example changes a column of a table from INT to 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  

B.B. 變更資料欄的大小Changing the size of a column

下列範例會增加 varchar 資料行的大小,以及 decimal 資料行的有效位數和小數位數。The following example increases the size of a varchar column and the precision and scale of a decimal column. 因為資料行包含資料,所以資料行大小只能增加。Because the columns contain data, the column size can only be increased. 另請注意,col_a 會定義於唯一索引中。Also notice that col_a is defined in a unique index. col_a 的大小仍可增加,因為資料類型是 varchar,且索引不是 PRIMARY KEY 條件約束的結果。The size of col_a can still be increased because the data type is a varchar and the index is not the result of a PRIMARY KEY constraint.

-- 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');  

C.C. 變更資料行定序Changing column collation

下列範例將示範如何變更資料行的定序。The following example shows how to change the collation of a column. 首先,資料表是包含預設使用者定序的已建立資料表。First, a table is created table with the default user collation.

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

接著,將資料行 C2 定序變更為 Latin1_General_BIN。Next, column C2 collation is changed to Latin1_General_BIN. 請注意,資料類型即使不會變更,但卻是必要的。Note that the data type is required, even though it is not changed.

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

D.D. 加密資料行Encrypting a column

下列範例示範如何使用具有安全記憶體保護區的 Always Encrypted 來加密資料行。The following example shows how to encrypt a column using Always Encrypted with secure enclaves.

首先,會建立不含任何加密資料行的資料表。First, a table is created without any encrypted columns.

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

接著,資料行 'C2' 會使用名為 CEK1 的資料行加密金鑰,以及隨機加密進行加密。Next, column 'C2' is encrypted with a column encryption key, named CEK1, and randomized encryption. 請注意,若要讓以下陳述式成功執行:Note that for the below statement to succeed:

  • 資料行加密金鑰必須已啟用安全記憶體保護區,表示它必須以允許記憶體保護區計算的資料行主要金鑰進行加密。The column encryption key must be enclave-enabled, meaning it must be encrypted with a column master key that allows enclave computations.
  • 目標 SQL Server 執行個體必須支援具有安全記憶體保護區的 Always Encrypted。The target SQL Server instance must support Always Encrypted with secure enclaves.
  • 陳述式必須是透過為具有安全記憶體保護區 Always Encrypted 設定的連線發出,並使用支援的用戶端驅動程式。The statement must be issued over a connection set up for Always Encrypted with secure enclaves, and using a supported client driver.
  • 進行呼叫的應用程式必須具備存取資料行主要金鑰的權限,以保護 CEK1。The calling application must have access to the column master key, protecting CEK1.
ALTER TABLE T3  
ALTER COLUMN C2 varchar(50) ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK1], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL;  
GO  

修改資料表定義Altering a Table Definition

本節中的範例示範如何修改資料表的定義。The examples in this section demonstrate how to alter the definition of a table.

A.A. 修改資料表以變更壓縮Modifying a table to change the compression

下列範例會變更非分割區資料表的壓縮。The following example changes the compression of a nonpartitioned table. 堆積或叢集索引將會重建。The heap or clustered index will be rebuilt. 如果資料表為堆積,則所有非叢集索引將會重建。If the table is a heap, all nonclustered indexes will be rebuilt.

ALTER TABLE T1   
REBUILD WITH (DATA_COMPRESSION = PAGE);  

下列範例會變更分割區資料表的壓縮。The following example changes the compression of a partitioned table. REBUILD PARTITION = 1 語法只會造成分割區號碼 1 的重建。The REBUILD PARTITION = 1 syntax causes only partition number 1 to be rebuilt.

適用於SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017 以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017 and Azure SQL DatabaseAzure SQL Database.

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

使用下列替代語法的相同作業會造成資料表內所有分割區的重建。The same operation using the following alternate syntax causes all partitions in the table to be rebuilt.

適用於SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017 以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017 and Azure SQL DatabaseAzure SQL Database.

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

如需其他資料壓縮範例,請參閱資料壓縮For additional data compression examples, see Data Compression.

B.B. 修改資料行存放區資料表來變更封存壓縮Modifying a columnstore table to change archival compression

下列範例藉由套用其他壓縮演算法來進一步壓縮資料行存放區資料表的分割區。The following example further compresses a columnstore table partition by applying an additional compression algorithm. 這樣會縮小資料表,但是也會增加儲存和擷取所需的時間。This reduces the table to a smaller size, but also increases the time required for storage and retrieval. 這對於封存,或是需要較少空間而且可負擔更多時間來儲存和擷取的狀況很實用。This is useful for archiving or for situations that require less space and can afford more time for storage and retrieval.

適用於SQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017 以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017 and Azure SQL DatabaseAzure SQL Database.

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

以下範例會將之前以 COLUMNSTORE_ARCHIVE 選項壓縮的資料行存放區資料表分割區解壓縮。The following example decompresses a columnstore table partition that was compressed with COLUMNSTORE_ARCHIVE option. 當還原資料時,資料將會繼續以用於所有資料行存放區資料表的資料行存放區壓縮來壓縮。When the data is restored, it will continue to be compressed with the columnstore compression that is used for all columnstore tables.

適用於SQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017 以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017 and Azure SQL DatabaseAzure SQL Database.

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

C.C. 在資料表之間切換分割區Switching partitions between tables

下列範例會建立分割區資料表,並假設資料庫中已經建立分割區配置 myRangePS1The following example creates a partitioned table, assuming that partition scheme myRangePS1 is already created in the database. 接著會建立一個非分割區資料表,其結構與分割區資料表相同,並且與資料表 PARTITION 2PartitionTable 位於相同的檔案群組上。Next, a non-partitioned table is created with the same structure as the partitioned table and on the same filegroup as PARTITION 2 of table PartitionTable. 然後,資料表 PARTITION 2PartitionTable 的資料就會切換到資料表 NonPartitionTable 中。The data of PARTITION 2 of table PartitionTable is then switched into table 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  

D.D. 允許對分割區擴大鎖定Allowing lock escalation on partitioned tables

下列範例會在分割區資料表上啟用分割區層級的鎖定擴大。The following example enables lock escalation to the partition level on a partitioned table. 如果資料表不是分割區資料表,則鎖定擴大會在 TABLE 層級設定。If the table is not partitioned, lock escalation is set at the TABLE level.

適用於SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017 以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017 and Azure SQL DatabaseAzure SQL Database.

ALTER TABLE dbo.T1 SET (LOCK_ESCALATION = AUTO);  
GO  

E.E. 設定資料表的變更追蹤Configuring change tracking on a table

下列範例會啟用 Person.Person 資料表上的變更追蹤。The following example enables change tracking on the Person.Person table.

適用於SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017 以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017 and Azure SQL DatabaseAzure SQL Database.

USE AdventureWorks;  
ALTER TABLE Person.Person  
ENABLE CHANGE_TRACKING;  

下列範例會啟用變更追蹤,並為變更期間更新的資料行啟用追蹤。The following example enables change tracking and enables the tracking of the columns that are updated during a change.

適用於SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.

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

下列範例會停用 Person.Person 資料表上的變更追蹤。The following example disables change tracking on the Person.Person table.

適用於SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017 以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017 and Azure SQL DatabaseAzure SQL Database.

USE AdventureWorks;  
Go  
ALTER TABLE Person.Person  
DISABLE CHANGE_TRACKING;  

停用和啟用條件約束與觸發程序Disabling and Enabling Constraints and Triggers

A.A. 停用及重新啟用條件約束Disabling and re-enabling a constraint

下列範例會停用限制資料中所接受之薪資的條件約束。The following example disables a constraint that limits the salaries accepted in the data. NOCHECK CONSTRAINT 若與 ALTER TABLE 搭配使用,可以停用條件約束,並允許插入通常會違反條件約束的值。NOCHECK CONSTRAINT is used with ALTER TABLE to disable the constraint and allow for an insert that would typically violate the constraint. CHECK CONSTRAINT 可重新啟用條件約束。CHECK CONSTRAINT re-enables the 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) ;  

B.B. 停用及重新啟用觸發程序Disabling and re-enabling a trigger

下列範例會使用 DISABLE TRIGGERALTER TABLE 選項停用觸發程序,並允許通常會違反此觸發程序規定的插入作業。The following example uses the DISABLE TRIGGER option of ALTER TABLE to disable the trigger and allow for an insert that would typically violate the trigger. 接著會再使用 ENABLE TRIGGER 重新啟用此觸發程序。ENABLE TRIGGER is then used to re-enable the 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  

線上作業Online Operations

A.A. 使用低優先順序等候選項的線上索引重建Online index rebuild using low priority wait options

下列範例示範如何執行指定低優先順序等候選項的線上索引重建。The following example shows how to perform an online index rebuild specifying the low priority wait options.

適用於SQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017 以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017 and Azure SQL DatabaseAzure SQL Database.

ALTER TABLE T1   
REBUILD WITH   
(  
    PAD_INDEX = ON,  
    ONLINE = ON ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 4 MINUTES, 
                                         ABORT_AFTER_WAIT = BLOCKERS ) )  
)  
;  

B.B. Online Alter ColumnOnline Alter Column

下列範例示範如何使用 ONLINE 選項執行變更資料行作業。The following example shows how to perform an alter column operation with the ONLINE option.

適用於SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017 以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017SQL Server 2017 and Azure SQL DatabaseAzure SQL Database.

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) WITH (ONLINE = ON);  
GO  
sp_help doc_exy;  
DROP TABLE dbo.doc_exy ;  
GO  

版本設定System Versioning

下列四個範例將協助您熟悉使用系統版本設定的語法。The following four examples will help you become familiar with the syntax for using system versioning. 如需其他協助,請參閱開始使用系統建立版本的時態表For additional assistance, see Getting Started with System-Versioned Temporal Tables.

適用於SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017 以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017SQL Server 2017 and Azure SQL DatabaseAzure SQL Database.

A.A. 將系統版本設定加入現有的資料表Add System Versioning to Existing Tables

下列範例會示範如何將系統版本設定加入現有的資料表,並建立未來的記錄資料表。The following example shows how to add system versioning to an existing table and create a future history table. 這個範例假設目前有一個稱為 InsurancePolicy 且已定義主索引鍵的資料表。This example assumes that there is an existing table called InsurancePolicy with a primary key defined. 此範例會為系統版本設定,填入新建立的期間資料行,而且因為開始時間與結束時間不能為空值,因此會使用預設值。This example populates the newly created period columns for system versioning using default values for the start and end times because these values cannot be null. 此範例會使用 HIDDEN 子句,以確保不會影響到與目前資料表互動的現有應用程式。This example uses the HIDDEN clause to ensure no impact on existing applications interacting with the current table. 它也會使用只有 SQL DatabaseSQL Database 才提供的 HISTORY_RETENTION_PERIOD。It also uses HISTORY_RETENTION_PERIOD that is available on SQL DatabaseSQL Database only.

--Alter non-temporal table to define periods for system versioning  
ALTER TABLE InsurancePolicy  
ADD PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime),   
SysStartTime datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL 
    DEFAULT SYSUTCDATETIME(),   
SysEndTime datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL 
    DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.99999999');  

--Enable system versioning with 1 year retention for historical data
ALTER TABLE InsurancePolicy 
SET (SYSTEM_VERSIONING = ON (HISTORY_RETENTION_PERIOD = 1 YEAR));  

B.B. 移轉現有解決方案以使用系統版本設定Migrate An Existing Solution to Use System Versioning

下列範例會示範如何從一種使用觸發程序來模擬時態性支援的解決方案,移轉至系統版本設定。The following example shows how to migrate to system versioning from a solution that uses triggers to mimic temporal support. 這個範例假設有一個解決方案針對其現有解決方案使用 ProjectTask 資料表與 ProjectTaskHistory 資料表,且它的期間是使用 Changed DateRevised Date 資料行,這些期間資料行不使用 datetime2 資料類型,且 ProjectTask 資料表已定義一個主索引鍵。The example assumes there is an existing solution that uses a ProjectTask table and a ProjectTaskHistory table for its existing solution, that is uses the Changed Date and Revised Date columns for its periods, that these period columns do not use the datetime2 datatype and that the ProjectTask table has a primary key defined.

-- Drop existing trigger  
DROP TRIGGER ProjectTask_HistoryTrigger;  

-- Adjust the schema for current and history table  
-- Change data types for existing period columns
ALTER TABLE ProjectTask ALTER COLUMN [Changed Date] datetime2 NOT NULL;   
ALTER TABLE ProjectTask ALTER COLUMN [Revised Date] datetime2 NOT NULL;  
ALTER TABLE ProjectTaskHistory ALTER COLUMN [Changed Date] datetime2 NOT NULL;  
ALTER TABLE ProjectTaskHistory ALTER COLUMN [Revised Date] datetime2 NOT NULL;  

-- Add SYSTEM_TIME period and set system versioning with linking two existing tables  
-- (a certain set of data checks happen in the background)  
ALTER TABLE ProjectTask  
ADD PERIOD FOR SYSTEM_TIME ([Changed Date], [Revised Date])  
  
ALTER TABLE ProjectTask  
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProjectTaskHistory, DATA_CONSISTENCY_CHECK = ON))  

C.C. 停用並重新啟用系統版本設定來變更資料表結構描述Disabling and Re-Enabling System Versioning to Change Table Schema

這個範例示範如何停用 Department 資料表上的系統版本設定、 加入資料行,然後重新啟用系統版本設定。This example shows how to disable system versioning on the Department table, add a column, and re-enable system versioning. 若要修改資料表結構描述,必須停用系統版本設定。Disabling system versioning is required in order to modify the table schema. 在一次交易中執行這些步驟,只更新資料表結構描述而不更新這兩個資料表,這樣可以讓 DBA 在重新啟用系統版本設定時不執行資料一致性檢查,還能改進效能。Perform these steps within a transaction to prevent updates to both tables while updating the table schema, which enables the DBA to skip the data consistency check when re-enabling system versioning and gain a performance benefit. 請注意,執行某些作業時並不需要停用系統版本設定,例如建立統計資料、 切換資料分割或者將壓縮套用到一或兩個資料表。Note that tasks such as creating statistics, switching partitions or applying compression to one or both tables does not require disabling system versioning.

BEGIN TRAN  
/* Takes schema lock on both tables */  
ALTER TABLE Department  
    SET (SYSTEM_VERSIONING = OFF);  
/* expand table schema for temporal table */  
ALTER TABLE Department  
     ADD Col5 int NOT NULL DEFAULT 0;  
/* Expand table schema for history table */  
ALTER TABLE DepartmentHistory  
    ADD Col5 int NOT NULL DEFAULT 0;  
/* Re-establish versioning again  */
ALTER TABLE Department  
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=dbo.DepartmentHistory, 
                                 DATA_CONSISTENCY_CHECK = OFF));  
COMMIT   

D.D. 移除系統版本設定Removing System Versioning

這個範例示範如何從 Department 資料表徹底移除系統版本設定以及卸除 DepartmentHistory 資料表。This example shows how to completely remove system versioning from the Department table and drop the DepartmentHistory table. 您可能也要視情況卸除系統用來記錄系統版本設定資訊的期間資料行。Optionally, you might also want to drop the period columns used by the system to record system versioning information. 請注意,當您啟用系統版本設定時,無法卸除 DepartmentDepartmentHistory 資料表。Note that you cannot drop either the Department or the DepartmentHistory tables while system versioning is enabled.

ALTER TABLE Department  
    SET (SYSTEM_VERSIONING = OFF);  
ALTER TABLE Department  
DROP PERIOD FOR SYSTEM_TIME;  
DROP TABLE DepartmentHistory;  

範例:Azure SQL 資料倉儲Azure SQL Data Warehouse平行處理資料倉儲Parallel Data WarehouseExamples: Azure SQL 資料倉儲Azure SQL Data Warehouse and 平行處理資料倉儲Parallel Data Warehouse

下列範例 A 至 C 都會使用 AdventureWorksPDW2012AdventureWorksPDW2012 資料庫中的 FactResellerSales 資料表。The following examples A through C use the FactResellerSales table in the AdventureWorksPDW2012AdventureWorksPDW2012 database.

A.A. 判斷資料表是否已分割Determining if a table is partitioned

下列查詢會在資料表 FactResellerSales 已分割時,傳回一個或多個資料列。The following query returns one or more rows if the table FactResellerSales is partitioned. 如果資料表未分割,則不會傳回任何資料列。If the table is not partitioned, no rows are returned.

SELECT * FROM sys.partitions AS p  
JOIN sys.tables AS t  
    ON  p.object_id = t.object_id  
WHERE p.partition_id IS NOT NULL  
    AND t.name = 'FactResellerSales';  

B.B. 判斷資料分割資料表的界限值Determining boundary values for a partitioned table

下列查詢會針對 FactResellerSales 資料表中的每一個分割區傳回界限值。The following query returns the boundary values for each partition in the FactResellerSales table.

SELECT t.name AS TableName, i.name AS IndexName, p.partition_number, 
    p.partition_id, i.data_space_id, f.function_id, f.type_desc, 
    r.boundary_id, r.value AS BoundaryValue   
FROM sys.tables AS t  
JOIN sys.indexes AS i  
    ON t.object_id = i.object_id  
JOIN sys.partitions AS p  
    ON i.object_id = p.object_id AND i.index_id = p.index_id   
JOIN  sys.partition_schemes AS s   
    ON i.data_space_id = s.data_space_id  
JOIN sys.partition_functions AS f   
    ON s.function_id = f.function_id  
LEFT JOIN sys.partition_range_values AS r   
    ON f.function_id = r.function_id and r.boundary_id = p.partition_number  
WHERE t.name = 'FactResellerSales' AND i.type <= 1  
ORDER BY p.partition_number;  

C.C. 判斷資料分割資料表的資料分割資料行Determining the partition column for a partitioned table

下列查詢會傳回資料表之分割區資料行的名稱。The following query returns the name of the partitioning column for table. FactResellerSales(採礦模型內容 (Analysis Services - 資料採礦))。FactResellerSales.

SELECT t.object_id AS Object_ID, t.name AS TableName, 
    ic.column_id as PartitioningColumnID, c.name AS PartitioningColumnName   
FROM sys.tables AS t  
JOIN sys.indexes AS i  
    ON t.object_id = i.object_id  
JOIN sys.columns AS c  
    ON t.object_id = c.object_id  
JOIN sys.partition_schemes AS ps  
    ON ps.data_space_id = i.data_space_id  
JOIN sys.index_columns AS ic  
    ON ic.object_id = i.object_id 
    AND ic.index_id = i.index_id AND ic.partition_ordinal > 0  
WHERE t.name = 'FactResellerSales'  
AND i.type <= 1  
AND c.column_id = ic.column_id;  

D.D. 合併兩個資料分割Merging two partitions

下列範例會將兩個資料分割合併到一個資料表。The following example merges two partitions on a table.

Customer 資料表的定義如下:The Customer table has the following definition:

CREATE TABLE Customer (  
    id int NOT NULL,  
    lastName varchar(20),  
    orderCount int,  
    orderDate date)  
WITH   
    ( DISTRIBUTION = HASH(id),  
    PARTITION ( orderCount RANGE LEFT  
    FOR VALUES (1, 5, 10, 25, 50, 100)));  

下列命令會結合 10 到 25 的資料分割界限。The following command combines the 10 and 25 partition boundaries.

ALTER TABLE Customer MERGE RANGE (10);  

資料表的新的 DDL 是:The new DDL for the table is:

CREATE TABLE Customer (  
    id int NOT NULL,  
    lastName varchar(20),  
    orderCount int,  
    orderDate date)  
WITH   
    ( DISTRIBUTION = HASH(id),  
    PARTITION ( orderCount RANGE LEFT  
    FOR VALUES (1, 5, 25, 50, 100)));  

E.E. 分割資料分割Splitting a partition

下列範例會分割資料表上的資料分割。The following example splits a partition on a table.

Customer 資料表有以下 DDL:The Customer table has the following DDL:

DROP TABLE Customer;  
  
CREATE TABLE Customer (  
    id int NOT NULL,  
    lastName varchar(20),  
    orderCount int,  
    orderDate date)  
WITH   
    ( DISTRIBUTION = HASH(id),  
    PARTITION ( orderCount RANGE LEFT  
    FOR VALUES (1, 5, 10, 25, 50, 100 )));  

下列命令會建立新的分割區而且被 75 這個值所限制,介於 50 到 100 之間。The following command creates a new partition bound by the value 75, between 50 and 100.

ALTER TABLE Customer SPLIT RANGE (75);  

資料表的新的 DDL 是:The new DDL for the table is:

CREATE TABLE Customer (  
   id int NOT NULL,  
   lastName varchar(20),  
   orderCount int,  
   orderDate date)  
   WITH DISTRIBUTION = HASH(id),  
   PARTITION ( orderCount (RANGE LEFT  
      FOR VALUES (1, 5, 10, 25, 50, 75, 100 )));  

F.F. 使用 SWITCH 將資料分割移至記錄資料表Using SWITCH to move a partition to a history table

下列範例會將 Orders 資料表某一資料分割中的資料移至 OrdersHistory 資料表的某一資料分割。The following example moves the data in a partition of the Orders table to a partition in the OrdersHistory table.

Orders 資料表有以下的 DDL:The Orders table has the following DDL:

CREATE TABLE Orders (  
    id INT,  
    city VARCHAR (25),  
    lastUpdateDate DATE,  
    orderDate DATE )  
WITH   
    (DISTRIBUTION = HASH ( id ),  
    PARTITION ( orderDate RANGE RIGHT   
    FOR VALUES ('2004-01-01', '2005-01-01', '2006-01-01', '2007-01-01' )));  

在這個範例中,Orders 資料表有下列資料分割。In this example, the Orders table has the following partitions. 每個分割區都包含資料。Each partition contains data.

資料分割Partition 有資料?Has data? 界限範圍Boundary range
11 Yes OrderDate < '2004-01-01'OrderDate < '2004-01-01'
22 Yes '2004-01-01' <= OrderDate < '2005-01-01''2004-01-01' <= OrderDate < '2005-01-01'
33 Yes '2005-01-01' <= OrderDate< '2006-01-01''2005-01-01' <= OrderDate< '2006-01-01'
44 Yes '2006-01-01'<= OrderDate < '2007-01-01''2006-01-01'<= OrderDate < '2007-01-01'
55 Yes '2007-01-01' <= OrderDate'2007-01-01' <= OrderDate
  • 分割區 1 (有資料):OrderDate < '2004-01-01'Partition 1 (has data): OrderDate < '2004-01-01'
  • 分割區 2 (有資料):'2004-01-01' <= OrderDate < '2005-01-01'Partition 2 (has data): '2004-01-01' <= OrderDate < '2005-01-01'
  • 分割區 3 (有資料):'2005-01-01' <= OrderDate< '2006-01-01'Partition 3 (has data): '2005-01-01' <= OrderDate< '2006-01-01'
  • 分割區 4 (有資料):'2006-01-01'<= OrderDate < '2007-01-01'Partition 4 (has data): '2006-01-01'<= OrderDate < '2007-01-01'
  • 分割區 5 (有資料):'2007-01-01' <= OrderDatePartition 5 (has data): '2007-01-01' <= OrderDate

OrdersHistory 資料表有下列 DDL,其中資料行和資料行名稱都與 Orders 資料表相同。The OrdersHistory table has the following DDL, which has identical columns and column names as the Orders table. 二者散列分佈在 id 資料行。Both are hash-distributed on the id column.

CREATE TABLE OrdersHistory (  
   id INT,  
   city VARCHAR (25),  
   lastUpdateDate DATE,  
   orderDate DATE )  
WITH   
    (DISTRIBUTION = HASH ( id ),  
    PARTITION ( orderDate RANGE RIGHT   
    FOR VALUES ( '2004-01-01' )));  

雖然資料行和資料行名稱必須相同,但資料分割界限不需要相同。Although the columns and column names must be the same, the partition boundaries do not need to be the same. 在此範例中,OrdersHistory 資料表有下列兩個資料分割而且都是空的:In this example, the OrdersHistory table has the following two partitions and both partitions are empty:

  • 分割區 1 (無資料):OrderDate < '2004-01-01'Partition 1 (no data): OrderDate < '2004-01-01'
  • 分割區 2 (空白):'2004-01-01' <= OrderDatePartition 2 (empty): '2004-01-01' <= OrderDate

針對前面兩個資料表,下列命令會將所有 OrderDate < '2004-01-01' 的資料列,從 Orders 資料表移至 OrdersHistory 資料表。For the previous two tables, the following command moves all rows with OrderDate < '2004-01-01' from the Orders table to the OrdersHistory table.

ALTER TABLE Orders SWITCH PARTITION 1 TO OrdersHistory PARTITION 1;  

因此,Orders 中的第一個資料分割是空白的,而且 OrdersHistory 中的第一個資料分割包含資料。As a result, the first partition in Orders is empty and the first partition in OrdersHistory contains data. 資料表現在會出現,如下所示:The tables now appear as follows:

Orders 資料表Orders table

  • 分割區 1 (空白):OrderDate < '2004-01-01'Partition 1 (empty): OrderDate < '2004-01-01'
  • 分割區 2 (有資料):'2004-01-01' <= OrderDate < '2005-01-01'Partition 2 (has data): '2004-01-01' <= OrderDate < '2005-01-01'
  • 分割區 3 (有資料):'2005-01-01' <= OrderDate< '2006-01-01'Partition 3 (has data): '2005-01-01' <= OrderDate< '2006-01-01'
  • 分割區 4 (有資料):'2006-01-01'<= OrderDate < '2007-01-01'Partition 4 (has data): '2006-01-01'<= OrderDate < '2007-01-01'
  • 分割區 5 (有資料):'2007-01-01' <= OrderDatePartition 5 (has data): '2007-01-01' <= OrderDate

OrdersHistory 資料表OrdersHistory table

  • 分割區 1 (有資料):OrderDate < '2004-01-01'Partition 1 (has data): OrderDate < '2004-01-01'
  • 分割區 2 (空白):'2004-01-01' <= OrderDatePartition 2 (empty): '2004-01-01' <= OrderDate

若要清除 Orders 資料表,您可以透過合併資料分割 1 和 2 來移除空白的資料分割,如下所示:To clean up the Orders table, you can remove the empty partition by merging partitions 1 and 2 as follows:

ALTER TABLE Orders MERGE RANGE ('2004-01-01');  

合併之後,Orders 資料表有下列資料分割:After the merge, the Orders table has the following partitions:

Orders 資料表Orders table

  • 分割區 1 (有資料):OrderDate < '2005-01-01'Partition 1 (has data): OrderDate < '2005-01-01'
  • 分割區 2 (有資料):'2005-01-01' <= OrderDate< '2006-01-01'Partition 2 (has data): '2005-01-01' <= OrderDate< '2006-01-01'
  • 分割區 3 (有資料):'2006-01-01'<= OrderDate < '2007-01-01'Partition 3 (has data): '2006-01-01'<= OrderDate < '2007-01-01'
  • 分割區 4 (有資料):'2007-01-01' <= OrderDatePartition 4 (has data): '2007-01-01' <= OrderDate

假設又過一年,您準備好封存 2005 年。Suppose another year passes and you are ready to archive the year 2005. 您可以按照以下方法分割空白資料分割,以在 OrdersHistory 資料表為 2005 年配置一個空白資料分割:You can allocate an empty partition for the year 2005 in the OrdersHistory table by splitting the empty partition as follows:

ALTER TABLE OrdersHistory SPLIT RANGE ('2005-01-01');  

分割之後,OrdersHistory 資料表會有以下資料分割:After the split, the OrdersHistory table has the following partitions:

OrdersHistory 資料表OrdersHistory table

  • 分割區 1 (有資料):OrderDate < '2004-01-01'Partition 1 (has data): OrderDate < '2004-01-01'
  • 分割區 2 (空白):'2004-01-01' < '2005-01-01'Partition 2 (empty): '2004-01-01' < '2005-01-01'
  • 分割區 3 (空白):'2005-01-01' <= OrderDatePartition 3 (empty): '2005-01-01' <= OrderDate

另請參閱See Also

sys.tables (Transact-SQL) sys.tables (Transact-SQL)
sp_rename (Transact-SQL) sp_rename (Transact-SQL)
CREATE TABLE (Transact-SQL) CREATE TABLE (Transact-SQL)
DROP TABLE (Transact-SQL) DROP TABLE (Transact-SQL)
sp_help (Transact-SQL) sp_help (Transact-SQL)
ALTER PARTITION SCHEME (Transact-SQL) ALTER PARTITION SCHEME (Transact-SQL)
ALTER PARTITION FUNCTION (Transact-SQL) ALTER PARTITION FUNCTION (Transact-SQL)
EVENTDATA (Transact-SQL)EVENTDATA (Transact-SQL)