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

适用对象: yesSQL ServeryesAzure SQL 数据库yesAzure SQL 数据仓库yes并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

通过更改、添加或删除列和约束,修改表定义。Modifies a table definition by altering, adding, or dropping columns and constraints. ALTER TABLE 还重新分配和重新生成分区,或禁用和启用约束和触发器。ALTER TABLE also reassigns and rebuilds partitions, or disables and enables 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.table_name | schema_name.table_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.table_name | schema_name.table_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 Analytics Platform System

ALTER TABLE { database_name.schema_name.source_table_name | schema_name.source_table_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 = 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
要在其中创建表的数据库的名称。The name of the database in which the table was created.

schema_nameschema_name
表所属的架构的名称。The name of the schema to which the table belongs.

table_nametable_name
要更改的表的名称。The name of the table to be altered. 如果表不在当前数据库中,或者不包含在当前用户所拥有的架构中,必须显式指定数据库和架构。If the table isn't in the current database or contained by the schema owned by the current user, you must explicitly specify the database and schema.

ALTER COLUMNALTER COLUMN
指定要更改的命名列。Specifies that the named column is to be changed or altered.

修改后的列不得为:The modified column can't be:

  • 数据类型为 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 语句生成的统计信息的列。Used in statistics generated by the CREATE STATISTICS statement. 数据类型不会改变,除非列的数据类型为 varchar 、nvarchar 或 varbinary ,Unless the column is a varchar, nvarchar, or varbinary data type, the data type isn't changed. 且新大小等于或大于旧大小,And, the new size is equal to or greater than the old size. 或要将列从非 NULL 更改为 NULL。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 约束的长度可变列的长度。But, 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 isn't changed.

只能通过下列方式更改数据类型为 text 、ntext 和 image 的列:The data type of text, ntext, and image columns can be changed only in the following ways:

  • text 更改为 varchar(max)、nvarchar(max) 或 xml text to varchar(max), nvarchar(max), or xml
  • ntext 更改为 varchar(max)、nvarchar(max) 或 xml ntext 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. 例如,将 nchar 或 nvarchar 列更改为 char 或 varchar 可能会导致转换扩展字符。For example, changing a nchar or nvarchar column, to char or varchar, might cause the conversion of extended characters. 有关详细信息,请参阅 CAST 和 CONVERTFor more information, see CAST and CONVERT. 减少列的精度或确定位数可能会导致数据截断。Reducing the precision or scale of a column can cause data truncation.

备注

无法更改已分区表的列的数据类型。The data type of a column of a partitioned table can't be changed.

除非列的数据类型为 varchar 、nvarchar 或 varbinary ,且新大小等于或大于旧大小,否则无法更改索引中所含列的数据类型。The data type of columns included in an index can't 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.

无法将主键约束中的列从非 NULL 更改为 NULL 。A column included in a primary key constraint, can't be changed from NOT NULL to NULL.

使用 Always Encrypted(不含安全 enclave)时,如果要修改的列使用“ENCRYPTED WITH”进行加密,可以将数据类型更改为兼容的数据类型(如从 INT 更改为 BIGINT),但不得更改任何加密设置。When using Always Encrypted (without secure enclaves), if the column being modified is encrypted with 'ENCRYPTED WITH', you can change the datatype to a compatible datatype (such as INT to BIGINT), but you can't change any encryption settings.

使用 Always Encrypted(含安全 enclave)时,如果用于保护列的列加密密钥(以及新的列加密密钥,若要更改密钥的话)支持 enclave 计算(使用已启用 enclave 的列主密钥进行加密),就可以更改任何加密设置。When using Always Encrypted with secure enclaves, you can change any encryption setting, if the column encryption key protecting the column (and the new column encryption key, if you're changing the key) support enclave computations (encrypted with enclave-enabled column master keys). 有关详细信息,请参阅具有安全 Enclave 的 Always EncryptedFor details, see Always Encrypted with secure enclaves.

column_name column_name
要更改、添加或删除的列的名称。The name of the column to be altered, added, or dropped. column_name 最多为 128 个字符。The column_name maximum is 128 characters. 对于使用 timestamp 数据类型创建的新列,可以省略 column_name 。For new columns, you can omit column_name for columns created with a timestamp data type. 如果没有为 timestamp 数据类型的列指定 column_name ,便会使用名称 timestamp 。The name timestamp is used if you don't specify column_name for a timestamp data type column.

备注

在更改表中的所有现有列之后,将添加新列。New columns are added after all existing columns in the table being altered.

[ type_schema_name .[ type_schema_name. ] type_name] type_name
更改后的列的新数据类型,或添加的列的数据类型。The new data type for the altered column, or the data type for the added column. 无法为已分区表的现有列指定 type_name 。You can't specify type_name for existing columns of partitioned tables. type_name 可以是下列任一数据类型:type_name can be any one of the following types:

  • SQL ServerSQL Server 系统数据类型。A SQL ServerSQL Server system data type.
  • 基于 SQL ServerSQL Server 系统数据类型的别名数据类型。An alias data type based on a SQL ServerSQL Server system data type. 必须先用 CREATE TYPE 语句创建别名数据类型,然后才能将它们用于表定义。You create alias data types 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. 必须先用 CREATE TYPE 语句创建用户定义类型,然后才能将它们用于表定义。You create user-defined types 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 不得为 timestamp 。type_name can't be timestamp.
  • 对于 ALTER COLUMN,ANSI_NULL 默认值始终为 ON;如果没有指定,列可为空。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. ALTER TABLE 的操作方式与 ARITHABORT 设置为 ON 时相同。ALTER TABLE operates as if ARITHABORT is set to ON.

备注

如果未指定 COLLATE 子句,那么更改列的数据类型会导致排序规则更改为数据库的默认排序规则。If the COLLATE clause isn't specified, changing the data type of a column causes a collation change to the default collation of the database.

精度precision
指定的数据类型的精度。The precision for the specified data type. 有关有效精度值的详细信息,请参阅精度、小数位数和长度For more information about valid precision values, see Precision, Scale, and Length.

scalescale
指定的数据类型的确定位数。The scale for the specified data type. 有关有效小数位数值的详细信息,请参阅精度、小数位数和长度For more information about valid scale values, see Precision, Scale, and Length.

max max
仅应用于 varchar、nvarchar 和 varbinary 数据类型,以便存储 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_collection xml_schema_collection
适用范围SQL ServerSQL ServerSQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL 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, you first create the schema collection 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 排序规则名称SQL Server 排序规则名称For a list and more information, see Windows Collation Name and SQL Server Collation Name.

COLLATE 子句仅更改数据类型为 char 、varchar 、nchar 和 nvarchar 的列的排序规则。The COLLATE clause changes the collations only of columns of the char, varchar, nchar, and nvarchar data types. 若要更改用户定义别名数据类型列的排序规则,请单独使用 ALTER TABLE 语句将列更改为 SQL ServerSQL Server 系统数据类型。To change the collation of a user-defined alias data type column, use separate ALTER TABLE statements to change the column to a SQL ServerSQL Server system data type. 然后,更改它的排序规则,并将列更改回别名数据类型。Then, change its collation and change the column back to an alias data type.

如果存在以下一个或多个条件,ALTER COLUMN 无法更改排序规则:ALTER COLUMN can't 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.

有关详细信息,请参阅 COLLATEFor more information, see COLLATE.

NULL | NOT NULLNULL | NOT NULL
指定列是否可接受空值。Specifies whether the column can accept null values. 只有在默认定义已指定或表为空时,才能用 ALTER TABLE 语句添加不允许 NULL 值的列。Columns that don't allow null values are added with ALTER TABLE only if they have a default specified or if the table is empty. 只有在也指定了 PERSISTED 后,才能为计算列指定 NOT NULL。You can specify NOT NULL for computed columns only if you've also specified PERSISTED. 如果新列允许 NULL 值,但没有指定默认定义,那么新列对表中的每一行都包含 NULL 值。If the new column allows null values and you don't specify a default, the new column contains a null value for each row in the table. 如果新列允许 NULL 值,并且指定了新列的默认定义,可使用 WITH VALUES 将默认值存储到表中每个现有行对应的新列中。If the new column allows null values and you add a default definition with the new column, you can use WITH VALUES to store the default value in the new column for each existing row in the table.

如果新列不允许 NULL 值,且表不为空,必须添加新列的 DEFAULT 定义。If the new column doesn't allow null values and the table isn't empty, you have to add a DEFAULT definition 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 约束中的列除外。You can specify NULL 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。You can specify NOT NULL 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's used in a column definition. 请务必始终将非计算列显式定义为 NULL 或 NOT NULL。Be sure 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, be sure to define the column with the same nullability as the user-defined data type. 此外,指定列的默认值。And, specify a default value for the column. 有关详细信息,请参阅 CREATE TABLEFor more information, see CREATE TABLE.

备注

如果 ALTER COLUMN 被指定为 NULL 或 NOT NULL,则必须同时指定 new_data_type [(precision [, scale ])] 。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 ServerSQL ServerSQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server ( SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database.

指定是在指定列中添加还是删除 ROWGUIDCOL 属性。Specifies that 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 列。You can set only one uniqueidentifier column per table as the ROWGUIDCOL column. 此外,只能将 ROWGUIDCOL 属性分配给 uniqueidentifier 列。And, you can only assign the ROWGUIDCOL property to a uniqueidentifier column. 无法将 ROWGUIDCOL 分配给使用用户定义数据类型的列。You can't assign ROWGUIDCOL to a column of a user-defined data type.

ROWGUIDCOL 不强制要求列中存储的值的唯一性,也不为插入表中的新行自动生成值。ROWGUIDCOL doesn't enforce uniqueness of the values stored in the column and doesn't automatically generate values for new rows that are inserted into the table. 若要为每列生成唯一值,请在 INSERT 语句中使用 NEWID 或 NEWSEQUENTIALID 函数。To generate unique values for each column, either use the NEWID or NEWSEQUENTIALID function on INSERT statements. 或者,将 NEWID 或 NEWSEQUENTIALID 函数指定为列的默认值。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's defined with a deterministic expression. 对于指定为 PERSISTED 的列,数据库引擎Database Engine将以物理方式在表中存储计算值;并且,当更新了计算列依赖的任何其他列时,这些值也将被更新。For columns specified as PERSISTED, the 数据库引擎Database 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's used as a partitioning column of a partitioned table must be explicitly marked PERSISTED.

DROP NOT FOR REPLICATIONDROP NOT FOR REPLICATION
适用范围SQL ServerSQL ServerSQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL 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 carry out insert operations. 只有当 column_name 是标识列时,才能指定此子句。You can specify this clause 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。You can't set sparse columns 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 ServerSQL ServerSQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL 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 ServerSQL ServerSQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL 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 carried out while the table remains available. 默认为 OFF。Default is OFF. 可以联机对列执行与数据类型、列长度或精度、为 Null 性、稀疏性和排序规则相关的列更改。You can run alter column online for column changes related to data type, column length or precision, nullability, sparseness, and collation.

通过联机更改列,用户创建的统计信息和自动统计信息可以在 ALTER COLUMN 操作执行期间引用更改后的列,这会让查询照常运行。Online alter column allows user created and autostatistics to reference the altered column for the duration of the ALTER COLUMN operation, which allows queries to run as usual. 在操作结束时,引用列的自动统计信息遭删除,且用户创建的统计信息失效。At the end of the operation, autostats 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 can't 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, and so on.) block or fail with an appropriate error. 此行为可保证联机更改列操作不会由于在执行操作时引入的依赖项而失败。This behavior guarantees that online alter column won't fail because of dependencies introduced while the operation was running.
  • 如果更改后的列被非聚集索引引用,不支持将列从 NOT NULL 更改为 NULL 视为联机操作。Altering a column from NOT NULL to NULL isn't supported as an online operation when the altered column is referenced by nonclustered indexes.
  • 如果检查约束引用了列,且更改操作在限制列(数值或日期时间)的精度,那么联机更改不受支持。Online alter isn't 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 can't be used with online alter column.
  • 联机更改列不支持 ALTER COLUMN ... ADD/DROP PERSISTEDALTER COLUMN ... ADD/DROP PERSISTED isn't supported for online alter column.
  • ALTER COLUMN ... ADD/DROP ROWGUIDCOL/NOT FOR REPLICATION 不受联机更改列影响。ALTER COLUMN ... ADD/DROP ROWGUIDCOL/NOT FOR REPLICATION isn't affected by online alter column.
  • 如果已启用更改跟踪或作为合并复制的发布者,联机更改列不支持更改表。Online alter column doesn't support altering a table where change tracking is enabled or that's a publisher of merge replication.
  • 联机更改列不支持更改或更改为 CLR 数据类型。Online alter column doesn't support altering from or to CLR data types.
  • 联机更改列不支持更改为,架构集合不同于当前架构集合的 XML 数据类型。Online alter column doesn't support altering to an XML data type that has a schema collection different than the current schema collection.
  • 联机更改列不放宽对可更改列的时间的限制。Online alter column doesn't reduce the restrictions on when a column can be altered. 索引/统计信息等的引用可能会导致更改失败。References by index/stats, and so on, might cause the alter to fail.
  • 联机更改列不支持同时更改多个列。Online alter column doesn't support altering more than one column concurrently.
  • 联机更改列对经系统版本控制的时态表不起任何作用。Online alter column has no effect in a system-versioned temporal table. 无论为 ONLINE 选项指定哪个值,都不会联机运行 ALTER 列。ALTER column isn't run as online regardless of which value was specified for ONLINE option.

联机更改列的要求、限制和功能类似于联机索引重新生成,具体包括:Online alter column has similar requirements, restrictions, and functionality as online index rebuild, which includes:

  • 如果表包含旧 LOB 或文件流列,或如果表有列存储索引,不支持联机索引重新生成。Online index rebuild isn't 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 isn't validated against a newly added or re-enabled FOREIGN KEY or CHECK constraint. 如果未指定,假定对新约束使用 WITH CHECK,对重新启用的约束使用 WITH NOCHECK。If you don't specify, WITH CHECK is assumed for new constraints, and WITH NOCHECK is assumed for re-enabled constraints.

如果不想对现有数据验证新 CHECK 或 FOREIGN KEY 约束,请使用 WITH NOCHECK。If you don't want to verify new CHECK or FOREIGN KEY constraints against existing data, use WITH NOCHECK. 不建议这样做,极少数情况除外。We don't recommend doing this, except in rare cases. 新约束在以后的所有数据更新中都会进行评估。The new constraint is 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 doesn't follow the constraint.

备注

查询优化器不考虑使用 WITH NOCHECK 定义的约束。The query optimizer doesn't 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 ALTER INDEX index_name
指定要变更或更改 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 uses for system versioning are added. 对于内存优化表,可以添加索引。For memory-optimized tables, you can add an index.

备注

在更改表中的所有现有列之后,将添加新列。New columns are added after all existing columns in the table being altered.

重要

如果不使用 ALTER TABLE 语句,内存优化表的索引不支持 CREATE INDEXDROP INDEXALTER INDEXPAD_INDEX 语句。Without using an ALTER TABLE statement, the statements CREATE INDEX, DROP INDEX, ALTER INDEX, and PAD_INDEX aren't 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 ServerSQL ServerSQL Server 2017 (14.x)SQL Server 2017 (14.x)SQL Server 2017SQL Server 2017)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL 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 uses to record the period of time 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。Set up the columns with the datatype of datetime2 and define them as NOT NULL. 如果将时间段列定义为 NULL,便会导致错误生成。If you define a period column as NULL, an error results. 可以为 system_start_time 和 system_end_time 列定义 column_constraint 和/或指定列的默认值You can define a column_constraint 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 following System Versioning examples that demonstrates using a default value for the system_end_time column.

将此参数与 SET SYSTEM_VERSIONING 参数结合使用,可对现有表启用系统版本控制。Use this argument with the SET SYSTEM_VERSIONING argument to enable system versioning on an existing table. 有关详细信息,请参阅时态表Azure SQL 数据库中的时态表入门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 <table_name> 就不会返回这些列的值。As of SQL Server 2017 (14.x)SQL Server 2017 (14.x), users can mark one or both period columns with HIDDEN flag to implicitly hide these columns such that SELECT * FROM <table_name> doesn't return a value for the columns. 默认情况下,时间段列不处于隐藏状态。By default, period columns aren't 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 uses for system versioning.

CONSTRAINT constraint_name CONSTRAINT constraint_name
指定从表中删除 constraint_name 。Specifies that constraint_name is removed from the table. 可以列出多个约束。Multiple constraints can be listed.

通过查询 sys.check_constraint 、sys.default_constraints 、sys.key_constraints 和 sys.foreign_keys 目录视图,可确定用户定义或系统提供的约束名称。You can determine the user-defined or system-supplied name of the constraint 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 can't be dropped if an XML index exists on the table.

INDEX index_name INDEX index_name
指定要从表中删除的 index_name 。Specifies 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_name COLUMN column_name
指定从表中删除 constraint_name 或 column_name 。Specifies that constraint_name or column_name is removed from the table. 可以列出多个列。Multiple columns can be listed.

无法删除以下列:A column can't be dropped when it's:

  • 在索引中作为键列或 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's defined with the DEFAULT keyword, or bound to a default object.
  • 绑定到规则的列。Bound to a rule.

备注

删除列并不回收列所占的磁盘空间。Dropping a column doesn't 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 ServerSQL ServerSQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL 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_parallelism MAXDOP = max_degree_of_parallelism
适用范围SQL ServerSQL ServerSQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL 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 ServerSQL Server 的所有版本。Parallel index operations aren't available in every edition of SQL ServerSQL 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 操作。You can run REBUILD as an ONLINE operation.

ONON
长期表锁在索引操作执行期间不保留。Long-term table locks aren't 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 behavior 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 short time. 在操作结束时,若要创建非聚集索引,便会对源短时间获取 S(共享)锁。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. 或者,如果联机创建或删除聚集索引,且重新生成聚集索引或非聚集索引,便会获取 SCH-M (架构修改)锁。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 can't 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 run 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 transactions from starting and might significantly affect the workload throughput and temporarily delay access to the underlying table.

OFFOFF
表锁在索引操作执行期间应用。Table locks apply 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 lock 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 lock 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 ServerSQL Server的各版本中均不提供联机索引操作。Online index operations are not available in every edition of SQL ServerSQL 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 ServerSQL ServerSQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL 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 isn't 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_IDENTIFIERFor more information, see SET QUOTED_IDENTIFIER.

{ 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 can't 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. 仍可为表定义禁用的触发器。When a trigger is disabled, it's still defined for the table. 不过,如果对表运行 INSERT、UPDATE 或 DELETE 语句,那么在重新启用触发器之前,触发器中的操作不会执行。However, when INSERT, UPDATE, or DELETE statements run against the table, the actions in the trigger aren't carried out until the trigger is re-enabled.

ALLALL
指定启用或禁用表中的所有触发器。Specifies that all triggers in the table are enabled or disabled.

trigger_name trigger_name
指定要启用或禁用的触发器的名称。Specifies the name of the trigger to disable or enable.

{ ENABLE | DISABLE } CHANGE_TRACKING{ ENABLE | DISABLE } CHANGE_TRACKING
适用范围SQL ServerSQL ServerSQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL 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 选项For more information, see ALTER DATABASE SET Options.

若要启用更改跟踪,表必须具有一个主键。To enable change tracking, the table must have a primary key.

WITH ( TRACK_COLUMNS_UPDATED = { ON | OFF } ) WITH ( TRACK_COLUMNS_UPDATED = { ON | OFF } )
适用范围SQL ServerSQL ServerSQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server ( SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database.

指定 数据库引擎Database Engine 是否跟踪哪些更改跟踪列已更新。Specifies whether the 数据库引擎Database 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 ServerSQL ServerSQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL 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_expression 。If table is a partitioned table, you must specify source_partition_number_expression. 如果 target_table 已分区,必须指定 target_partition_number_expression 。If target_table is partitioned, you must specify target_partition_number_expression. 若要将表的数据作为分区重新分配给现有的已分区表,或要将分区由一个已分区表切换到另一个已分区表,那么目标分区必须存在且为空。When 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.

若要重新分配一个分区的数据来形成单个表,那么目标表必须已存在且为空。When reassigning one partition's data to form a single table, the target table must already exist and it must be empty. 源表或分区以及目标表或分区必须位于同一个文件组中。Both the source table or partition, and the target table or partition, must be located in the same filegroup. 相应的索引或索引分区也必须位于同一个文件组中。The corresponding indexes, or index partitions, must also be located in the same filegroup. 切换分区还有许多其他限制。Many additional restrictions apply to switching partitions. table 和 target_table 不得相同。table and target_table can't be the same. target_table 可以是由多个部分组成的标识符 。target_table can be a multi-part identifier.

source_partition_number_expression 和 target_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 can't 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. 不过,在现有唯一索引中添加分区键可能会改变唯一性。But, including the partition key with an existing unique index can change the uniqueness.
  • 所有非聚集索引都必须包含分区键才能切换分区。To switch partitions, all nonclustered indexes must include the partition key.

有关使用复制时的 SWITCH 限制的信息,请参阅复制已分区表和索引For SWITCH restriction when using replication, see Replicate Partitioned Tables and Indexes.

在版本 V12 成为只读格式之前,为 SQL ServerSQL Server 2016 CTP1 和 SQL 数据库生成的非聚集列存储索引。Nonclustered columnstore indexes built for SQL ServerSQL Server 2016 CTP1, and for SQL Database before version V12 were in a read-only format. 必须将非聚集列存储索引重新生成为当前格式(可更新),才能执行任何 PARTITION 操作。You must rebuild Nonclustered columnstore indexes to the current format (which is updatable) before any PARTITION operations can be run.

SET ( FILESTREAM_ON = { partition_scheme_name | filestream_filegroup_name | "default" | "NULL" }) SET ( FILESTREAM_ON = { partition_scheme_name | filestream_filegroup_name | " default " | " NULL " })
适用范围SQL ServerSQL ServerSQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017)。Applies to: SQL ServerSQL Server ( SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017). Azure SQL DatabaseAzure SQL Database 不支持 FILESTREAMdoesn't support FILESTREAM.

指定 FILESTREAM 数据的存储位置。Specifies where FILESTREAM data is stored.

只有在表不包含任何 FILESTREAM 列时,含 SET FILESTREAM_ON 子句的 ALTER TABLE 才能成功运行。ALTER TABLE with the SET FILESTREAM_ON clause succeeds only if the table has no FILESTREAM columns. 可使用第二个 ALTER TABLE 语句来添加 FILESTREAM 列。You can add FILESTREAM columns by using a second ALTER TABLE statement.

如果指定 partition_scheme_name ,则会应用 CREATE TABLE 规则。If you specify partition_scheme_name, the rules for CREATE TABLE apply. 请确保表已针对行数据进行了分区,且它的分区方案使用与 FILESTREAM 分区方案相同的分区功能和分区列。Be sure the table is already partitioned for row data, and its partition scheme uses 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's defined for the filegroup by using a CREATE DATABASE or ALTER DATABASE statement, or an error results.

"default" 指定具有 DEFAULT 属性集的 FILESTREAM 文件组 。" default " specifies the FILESTREAM filegroup with the DEFAULT property set. 如果没有 FILESTREAM 文件组,便会导致错误生成。If there's no FILESTREAM filegroup, an error results.

"NULL" 指定删除对表的 FILESTREAM 文件组的所有引用。" NULL " specifies that all references to FILESTREAM filegroups for the table are removed. 首先必须删除所有 FILESTREAM 列。All FILESTREAM columns must be dropped first. 使用 SET FILESTREAM_ON="NULL" 可删除与表关联的所有 FILESTREAM 数据。Use SET FILESTREAM_ON =" NULL " to delete all FILESTREAM data that's 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 ServerSQL ServerSQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL 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 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 you don't use the HISTORY_TABLE argument, the system generates a new history table matching the schema of the current table, creates 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 you use the HISTORY_TABLE argument to create a link to and use an existing history table, the system creates a link between the current table and the specified table. 关联到现有历史记录表时,可以选择执行数据一致性检查。When creating a link to an existing history table, you can choose to do a data consistency check. 数据一致性检查可确保现有记录不重叠。This data consistency check ensures that existing records don't overlap. 系统默认运行数据一致性检查。Running 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} } HISTORY_RETENTION_PERIOD = { INFINITE | number {DAY | DAYS | WEEK | WEEKS | MONTH | MONTHS | YEAR | YEARS} }
适用范围:SQL Server 2017 (14.x)SQL Server 2017 (14.x)Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server 2017 (14.x)SQL Server 2017 (14.x) and Azure SQL DatabaseAzure SQL Database.

指定时态表中历史数据的有限保留期或无限保留期。Specifies finite or infinite retention for historical data in a temporal table. 如果省略,则假定为无限期保留。If omitted, infinite retention is assumed.

SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } ) SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )
适用范围SQL ServerSQL ServerSQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL 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 数据库引擎SQL Server Database Engine 可选择适合于表架构的锁升级粒度。This option allows SQL Server 数据库引擎SQL Server Database Engine to select the lock escalation granularity that's appropriate for the table schema.

  • 如果表已分区,锁升级到分区级别。If the table is partitioned, lock escalation is allowed to partition. 在锁升级到分区级别后,锁以后就不会升级到 TABLE 粒度。After the lock is escalated to the partition level, the lock won't be escalated later to TABLE granularity.
  • 如果表未分区,锁升级到 TABLE 粒度。If the table isn't partitioned, the lock escalation is done to the TABLE granularity.

TABLETABLE
无论表是否已分区,锁都升级到表级粒度。Lock escalation is done at table-level granularity whether the table is partitioned or not partitioned. 默认值为 TABLE。TABLE is the default value.

DISABLEDISABLE
在大多数情况下禁止锁升级。Prevents lock escalation in most cases. 表级锁并未完全被禁止。Table-level locks aren't completely disallowed. 例如,如果扫描的表在可序列化隔离级别下没有聚集索引,数据库引擎Database Engine 必须使用表锁来保证数据完整性。For example, when you're scanning a table that has no clustered index under the serializable isolation level, 数据库引擎Database 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 run as an ONLINE operation.

使用 REBUILD PARTITION 语法可重新生成分区表中的单个分区。Use the REBUILD PARTITION syntax to rebuild a single partition in a partitioned table.

PARTITION = ALLPARTITION = ALL
适用范围SQL ServerSQL ServerSQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL 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 doesn't have a clustered index, the heap structure is only affected by some of the options.

如果未使用 REBUILD 操作指定具体压缩设置,使用的是分区的当前压缩设置。When a specific compression setting isn't 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_optionFor complete descriptions of the rebuild options, see index_option.

DATA_COMPRESSIONDATA_COMPRESSION
适用范围SQL ServerSQL ServerSQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL 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 不压缩表或指定的分区。NONE Table or specified partitions aren't compressed. 此选项不适用于列存储表。This option doesn't apply to columnstore tables.

ROW 使用行压缩来压缩表或指定的分区。ROW Table or specified partitions are compressed by using row compression. 此选项不适用于列存储表。This option doesn't apply to columnstore tables.

PAGE 使用页压缩来压缩表或指定的分区。PAGE Table or specified partitions are compressed by using page compression. 此选项不适用于列存储表。This option doesn't apply to columnstore tables.

COLUMNSTORECOLUMNSTORE
适用范围SQL ServerSQL ServerSQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL 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 continues to be compressed with the columnstore compression that's used for all columnstore tables.

COLUMNSTORE_ARCHIVECOLUMNSTORE_ARCHIVE
适用范围SQL ServerSQL ServerSQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL 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. 此选项可用于存档,或其他要求更少存储但能花更多时间用于存储和检索的情形。Use this option for archival or other situations that require less storage and can afford more time for storage and retrieval.

若要同时重新生成多个分区,请参阅 index_optionTo rebuild multiple partitions at the same time, see index_option. 如果表没有聚集索引,更改数据压缩会重新生成堆和非聚集索引。If the table doesn't 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 is available for queries and data modification during the index operation. 默认为 OFF。The default is OFF. 可以将 REBUILD 运行为 ONLINE 操作。You can run REBUILD as an ONLINE operation.

ONON
长期表锁在索引操作执行期间不保留。Long-term table locks aren't held for the duration of the index operation. 在索引重新生成开始时,表需要 S 锁;在联机索引重新生成结束时,表需要 Sch-M 锁。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, 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_COLUMNS column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
适用范围SQL ServerSQL ServerSQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server ( SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database.

列集的名称。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 can't 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 ServerSQL ServerSQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017)。Applies to: SQL ServerSQL 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 ServerSQL ServerSQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017Applies to: SQL ServerSQL Server ) SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017. Azure SQL DatabaseAzure SQL Database 不支持 FILETABLEdoesn't 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, despite the 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 ServerSQL ServerSQL Server 2017 (14.x)SQL Server 2017 (14.x)SQL Server 2017SQL Server 2017)。Applies to: SQL ServerSQL 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 information, see Stretch Database.

为表启用 Stretch Database Enabling 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 information 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. 有关详细信息,请参阅为数据库启用 Stretch DatabaseFor more information, 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 Database Disabling Stretch Database for a table

为表禁用 Stretch 后,已迁移到 Azure 的远程数据的处理方式有两种。When you disable Stretch for a table, you have two options for the remote data that's already been migrated to Azure. 有关详细信息,请参阅禁用 Stretch Database 并恢复远程数据For more information, see Disable Stretch Database and bring back remote data.

  • 要禁用表的拉伸或将表中的远程数据从 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 ) ) ;
    

此操作会产生数据传输成本,并且不能取消。This operation incurs data transfer costs, and it can't be canceled. 有关详细信息,请参阅数据传输定价详细信息For more information, see Data Transfers Pricing Details.

当所有远程数据已从 Azure 复制回 SQL Server 后,禁用表的“拉伸”。After all the remote data has been copied from Azure back to SQL Server, Stretch is disabled for the table.

  • 要禁用表的“拉伸”并放弃远程数据,请运行以下命令。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 doesn't remove the remote table. 若要删除远程表,请使用 Azure 门户删除它。If you want to delete the remote table, you drop it by using the Azure portal.

[ FILTER_PREDICATE = { null | predicate } ] [ FILTER_PREDICATE = { null | predicate } ]
适用范围SQL ServerSQL ServerSQL Server 2017 (14.x)SQL Server 2017 (14.x)SQL Server 2017SQL Server 2017)。Applies to: SQL ServerSQL 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使用筛选器函数选择要迁移的行For more information, 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_STATE 。When you specify a filter predicate, you also have to specify MIGRATION_STATE.

MIGRATION_STATE = { OUTBOUND | INBOUND | PAUSED }MIGRATION_STATE = { OUTBOUND | INBOUND | PAUSED }
适用范围SQL ServerSQL ServerSQL Server 2017 (14.x)SQL Server 2017 (14.x)SQL Server 2017SQL Server 2017)。Applies to: SQL ServerSQL 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 ServerSQL ServerSQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL 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 waits for low-priority locks, allowing other operations to carry on 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 the same as WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE).

MAX_DURATION = time [MINUTES ] MAX_DURATION = time [MINUTES ]
适用范围SQL ServerSQL ServerSQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server ( SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database.

SWITCH 或联机索引重新生成锁在运行 DDL 命令时以低优先级等待的等待时间(以分钟为单位指定的整数值)。The wait time, which is an integer value specified in minutes, that the SWITCH or online index rebuild locks wait with low priority when running 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 run. MAX_DURATION 时间始终以分钟为单位,可以省略 MINUTES 一词。MAX_DURATION time is always in minutes, and you can omit the word MINUTES.

ABORT_AFTER_WAIT = [NONE | SELF | BLOCKERS } ] ABORT_AFTER_WAIT = [NONE | SELF | BLOCKERS } ]
适用范围SQL ServerSQL ServerSQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL 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 run without taking any action.

BLOCKERSBLOCKERS
终止所有当前阻止 SWITCH 或联机索引重新生成 DDL 操作的用户事务,以便操作能够继续执行。Kill all user transactions that currently block 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 ServerSQL ServerSQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL 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

可以指定列数据类型的新大小,从而更改列的长度、精度或确定位数。You can change the length, precision, or scale of a column by specifying a new size for the column data type. 使用 ALTER COLUMN 子句。Use the ALTER COLUMN clause. 如果列中有数据,新大小不得小于数据的大小上限。If data exists in the column, the new size can't be smaller than the maximum size of the data. 此外,除非列的数据类型为 varchar 、nvarchar 或 varbinary ,且索引不是 PRIMARY KEY 约束的结果,否则无法在索引中定义列。Also, you can't define the column in an index, unless the column is a varchar, nvarchar, or varbinary data type and the index isn't the result of a PRIMARY KEY constraint. 请参阅标题为更改列定义的小节中的示例。See the example in the short section titled Altering a Column Definition.

锁和 ALTER TABLELocks and ALTER TABLE

在 ALTER TABLE 中指定的更改会立即实现。Changes you specify in ALTER TABLE implement 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 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 ServerSQL Server 的一些版本中添加有默认值的 NOT NULL 列)可能需要较长时间才能完成,并生成大量日志记录。Changes that affect all the rows in large tables, such as dropping a column or, on some editions of SQL ServerSQL 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 语句一样。Run these ALTER TABLE statements 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 despite the number of rows in the table. 这是因为,表中的现有行在操作执行期间不更新。Because, the existing rows in the table aren't 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. 这种行为是自动的。This behavior is automatic. 除了 ADD COLUMN 语法之外,无需其他任何语法,即可实现联机操作。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 despite 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() aren't 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's not a runtime constant is always run 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 don't specify another value for the column. 在行更新(即使未在 UPDATE 语句中指定实际列)或重新生成表或聚集索引时,存储在元数据中的默认值移到现有行中。The default value stored in metadata moves to an existing row when the row is updated (even if the actual column isn't specified in the UPDATE statement), or if the table or clustered index is rebuilt.

无法在联机操作中添加 varchar(max) 、nvarchar(max) 、varbinary(max) 、xml 、text 、ntext 、image 、hierarchyid 、geometry 、geography 或 CLR UDTS 类型的列。Columns of type varchar(max), nvarchar(max), varbinary(max), xml, text, ntext, image, hierarchyid, geometry, geography, or CLR UDTS, can't be added in an online operation. 如果列会导致最大可能行大小超过 8,060 字节限制,无法联机添加列。A column can't 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 语句的处理器数的确定依据为,“最大并行度” 配置选项和当前工作负载。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 Engine检测到系统正忙,则在语句执行开始之前将自动降低操作并行度。If the 数据库引擎Database 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, use ALTER TABLE to change the state of the columns, constraints, and triggers of a partitioned table just like it's used for nonpartitioned tables. 不过,无法使用此语句来更改表本身的分区方式。However, this statement can't 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 can't 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 isn't allowed. 如果 ALTER TABLE 语句要求更改用于架构绑定视图中的列,ALTER TABLE 将失败,并且数据库引擎Database Engine将引发错误消息。If the ALTER TABLE statement requires changing a column used in a schema-bound view, ALTER TABLE fails and the 数据库引擎Database Engine raises an error message. 有关架构绑定和索引视图的详细信息,请参阅 CREATE VIEWFor more information about schema binding and indexed views, see CREATE VIEW.

创建引用表的架构绑定视图不会影响在基表上添加或删除触发器。Adding or removing triggers on base tables isn't 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 来删除和添加约束。Use The ALTER INDEX statement to rebuild an index part of a constraint definition; the constraint doesn't 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 you delete a constraint that created a clustered index, 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 isn't valid for indexed views or nonclustered indexes.
  • 分区方案或文件组必须已经存在。The partition scheme or filegroup must already exist.
  • 如果没有指定 MOVE TO,表位于为聚集索引定义的同一分区方案或文件组中。If MOVE TO isn't specified, the table is 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, specify the ONLINE = ON option so the DROP INDEX transaction doesn't block queries and modifications to the underlying data and associated nonclustered indexes.

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

  • ONLINE = ON 对也遭禁用的聚集索引无效。ONLINE = ON isn't 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 isn't valid for indexed views, nonclustered indexes, or indexes on local temp tables.
  • ONLINE = ON 对列存储索引无效。ONLINE = ON isn't 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 can't be applied to clustered indexes on views or nonclustered indexes.

复制架构更改Replicating Schema Changes

默认情况下,当在 SQL ServerSQL Server 发布服务器中对发布的表运行 ALTER TABLE 时,此更改传播到所有 SQL ServerSQL Server 订阅服务器。When you run ALTER TABLE on a published table at a SQL ServerSQL Server Publisher, by default, that change propagates to all SQL ServerSQL Server Subscribers. 此功能存在一些限制。This functionality has some restrictions. 可以禁用它。You can disable it. 有关详细信息,请参阅对发布数据库进行架构更改For more information, see Make Schema Changes on Publication Databases.

Data CompressionData Compression

无法为系统表启用压缩。System tables can't 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 can't 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. 清理可能需要大量时间才能完成。The cleanup can require a large amount of time. 在有大量行的表中删除 NTEXT 列时,首先将 NTEXT 列更新为 NULL 值,再删除此列。When dropping an NTEXT column in a table with lots of rows, update the NTEXT column to NULL value first, then drop the column. 可以使用并行操作运行此选项,并加快速度。You can run this option with parallel operations and make it much faster.

联机索引重新生成Online Index Rebuild

若要为联机索引重新生成操作运行 DDL 语句,必须完成对特定表运行的所有正在阻塞的事务。To run 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 launches, it blocks all new transactions that are ready to start running on this table. 尽管联机索引重新生成锁的持续时间较短,但等待给定表的所有待处理事务完成,以及阻止新事务启动可能会对吞吐量产生很大影响。Although the duration of the lock for online index rebuild is short, waiting for all open transactions on a given table to complete and blocking the new transactions to start, might significantly affect the throughput. 这可能会导致工作负载变慢或超时,并严重限制对基础表的访问。This can cause a workload slow-down or timeout and significantly limit access to the underlying table. 使用 WAIT_AT_LOW_PRIORITY 选项,DBA 可以管理联机索引重新生成需要的 S 锁和 Sch-M 锁,并能选择三个选项之一。The WAIT_AT_LOW_PRIORITY option allows DBAs to manage the S-lock and Sch-M locks required for online index rebuilds and lets them to select one of three options. 在所有三种情况下,如果等待期间 ((MAX_DURATION =n [minutes])) 没有阻塞活动,联机索引重新生成就会立即运行而不等待,且 DDL 语句会完成。In all three cases, if during the wait time ( (MAX_DURATION =n [minutes]) ) there are no blocking activities, the online index rebuild is run immediately without waiting and the DDL statement is completed.

兼容性支持Compatibility Support

ALTER TABLE 语句只支持包含两部分 (schema.object) 的表名称。The ALTER TABLE statement supports 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.

若要解决此问题,请不要使用包含四部分的前缀。To resolve the problem, remove the use of a four-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's switched inherits the security of the target table.

如果已将 ALTER TABLE 语句中的任何列定义为使用公共语言运行时 (CLR) 用户定义类型或别名数据类型,必须对类型拥有 REFERENCES 权限。If you've defined any columns in the ALTER TABLE statement 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 isn't 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. 在该新列中,每一行都将有 NULL 值。In 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. 添加包含默认值的可为空的列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 isn't 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_INDEX 选项。The 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 2017Azure 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

下面的示例演示如何向表 T2 中添加一列。The following examples show adding a column to table T2. 无法将列集添加到已包含稀疏列的表。A column set can't 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 my_constraint, my_pk_constraint, COLUMN column_b ;
GO

C.C. 在 ONLINE 模式下删除 PRIMARY KEY 约束Dropping a PRIMARY KEY constraint in the ONLINE mode

下面的示例在 ONLINE 选项设置为 ON 的情况下删除 PRIMARY KEY 约束。The 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 isn't 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. 必须有数据类型,即使它未改变。The data type is required, even though it isn't changed.

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

D.D. 加密列Encrypting a column

以下示例演示如何使用具有安全 enclave 的 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

接着,使用列加密密钥(名为 CEK1)和随机加密来加密列“C2”。Next, column 'C2' is encrypted with a column encryption key, named CEK1, and randomized encryption. 若要成功执行下面的语句,必须满足以下条件:For the following statement to succeed:

  • 列加密密钥必须已启用 enclave。The column encryption key must be enclave-enabled. 也就是说,必须使用允许 enclave 计算的列主密钥进行加密。Meaning, it must be encrypted with a column master key that allows enclave computations.
  • 目标 SQL Server 实例必须支持具有安全 enclave 的 Always Encrypted。The target SQL Server instance must support Always Encrypted with secure enclaves.
  • 必须通过为具有安全 enclave 的 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 2017Azure 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 2017Azure 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 compression 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 2017Azure 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's used for all columnstore tables.

适用范围:SQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017Azure 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 2 表的 PartitionTable 所在的同一文件组中,创建与已分区表结构相同的未分区的表。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 2 表的 PartitionTable 中的数据切换到 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 isn't partitioned, lock escalation is set at the TABLE level.

适用范围:SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017Azure 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 2017Azure 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 2017Azure 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 CONSTRAINTALTER 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 2017Azure 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 Column

下面的示例展示了如何使用 ONLINE 选项运行更改列操作。The following example shows how to run an alter column operation with the ONLINE option.

适用范围:SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017Azure 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 2017Azure 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. 此示例假定存在已定义主键的现有表 InsurancePolicyThis example assumes that there's an existing table called InsurancePolicy with a primary key defined. 此示例使用开始时间和结束时间的默认值填充新创建的时间段列,以实现系统版本控制,因为这些值不得为 NULL。This example populates the newly created period columns for system versioning using default values for the start and end times because these values can't be null. 此示例使用 HIDDEN 子句来确保不会影响现有应用程序与当前表之间的交互。This example uses the HIDDEN clause to ensure no impact on existing applications interacting with the current table. 它还使用仅适用于 SQL 数据库SQL Database 的 HISTORY_RETENTION_PERIOD。It also uses HISTORY_RETENTION_PERIOD that's available on SQL 数据库SQL 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's an existing solution that uses a ProjectTask table and a ProjectTaskHistory table for its existing solution, that's uses the Changed Date and Revised Date columns for its periods, that these period columns don't 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 to modify the table schema. 在事务中执行这些步骤可阻止在更新表架构时更新两个表,这样可让 DBA 在重新启用系统版本控制时跳过数据一致性检查,并获得性能优势。Do 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. 创建统计信息、切换分区或压缩一个或两个表等任务不需要禁用系统版本控制。Tasks such as creating statistics, switching partitions, or applying compression to one or both tables doesn't 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 表。You can't 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 isn't 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 列中的一个值匹配。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 don't 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:

OrdersOrders 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

OrdersHistoryOrdersHistory 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:

OrdersOrders 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're 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:

OrdersHistoryOrdersHistory 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