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

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

指定使用 ALTER TABLE 添加到表中的列的属性。Specifies the properties of a column that are added to a table by using ALTER TABLE.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions


column_name <data_type>  
[ COLLATE collation_name ]   
    [ CONSTRAINT constraint_name ] DEFAULT constant_expression [ WITH VALUES ]   
    | IDENTITY [ ( seed , increment ) ] [ NOT FOR REPLICATION ]   
[ SPARSE ]   
  ( COLUMN_ENCRYPTION_KEY = key_name ,  
      ALGORITHM =  'AEAD_AES_256_CBC_HMAC_SHA_256'   
  ) ]  
[ MASKED WITH ( FUNCTION = ' mask_function ') ]  
[ <column_constraint> [ ...n ] ]  
<data type> ::=   
[ type_schema_name . ] type_name   
    [ ( precision [ , scale ] | max |   
        [ { CONTENT | DOCUMENT } ] xml_schema_collection ) ]   
<column_constraint> ::=   
[ CONSTRAINT constraint_name ]   
{     { PRIMARY KEY | UNIQUE }   
            WITH FILLFACTOR = fillfactor    
          | WITH ( < index_option > [ , ...n ] )   
        [ ON { partition_scheme_name ( partition_column_name )   
            | filegroup | "default" } ]  
  | [ FOREIGN KEY ]   
        REFERENCES [ schema_name . ] referenced_table_name [ ( ref_column ) ]   
        [ NOT FOR REPLICATION ]   
  | CHECK [ NOT FOR REPLICATION ] ( logical_expression )   


column_name column_name
要更改、添加或删除的列的名称。Is the name of the column to be altered, added, or dropped. column_name 可以包含 1 到 128 个字符 。column_name can consist of 1 through 128 characters. 对于使用 timestamp 数据类型创建的新列,可以省略 column_name 。For new columns, created with a timestamp data type, column_name can be omitted. 如果没有为 timestamp 数据类型的列指定 column_name,则使用名称 timestamp 。If no column_name is specified for a timestamp data type column, the name timestamp is used.

[ type_schema_name. [ type_schema_name. ] type_name ] type_name
是添加的列的数据类型及其所属架构。Is the data type for the column that is added and the schema to which it belongs.

type_name 可以为 :type_name can be:

  • MicrosoftMicrosoftSQL ServerSQL Server 系统数据类型。A MicrosoftMicrosoftSQL ServerSQL Server system data type.

  • 基于 SQL ServerSQL Server 系统数据类型的别名数据类型。An alias data type based on a SQL ServerSQL Server system data type. 别名数据类型必须先使用 CREATE TYPE 进行创建,然后才能在表定义中使用。Alias data types must be created by using CREATE TYPE before they can be used in a table definition.

  • MicrosoftMicrosoft .NET Framework.NET Framework 用户定义类型及其所属架构。A MicrosoftMicrosoft .NET Framework.NET Framework user-defined type and the schema to which it belongs. .NET Framework.NET Framework 用户定义类型必须先使用 CREATE TYPE 进行创建,然后才能在表定义中使用。A .NET Framework.NET Framework user-defined type must be created by using CREATE TYPE before it can be used in a table definition.

如果未指定 type_schema_name,则 MicrosoftMicrosoft 数据库引擎Database Engine 按照下列顺序引用 type_name :If type_schema_name is not specified, the MicrosoftMicrosoft 数据库引擎Database Engine references type_name in the following order:

  • SQL ServerSQL Server 系统数据类型。The SQL ServerSQL Server system data type.

  • 当前数据库中当前用户的默认架构。The default schema of the current user in the current database.

  • 当前数据库中的 dbo 架构。The dbo schema in the current database.

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

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

max max
仅适用于 varchar、nvarchar 和 varbinary 数据类型 。Applies only to the varchar, nvarchar, and varbinary data types. 它们用于存储 2^31 个字节的字符和二进制数据,以及 2^30 个字节的 Unicode 数据。These are used for storing 2^31 bytes of character and binary data, and 2^30 bytes of Unicode data.

指定 column_name 中 xml 数据类型的每个实例都可包含多个顶级元素 。Specifies that each instance of the xml data type in column_name can comprise multiple top-level elements. CONTENT 仅适用于 xml 数据类型,并且只有在同时指定了 xml_schema_collection 时才能指定 CONTENT 。CONTENT applies only to the xml data type and can be specified only if xml_schema_collection is also specified. 如果未指定,则默认行为是 CONTENT。If this is not specified, CONTENT is the default behavior.

指定 column_name 中 xml 数据类型的每个实例只能包含一个顶级元素 。Specifies that each instance of the xml data type in column_name can comprise only one top-level element. DOCUMENT 仅适用于 xml 数据类型,并且只有在同时指定了 xml_schema_collection 时才能指定 DOCUMENT 。DOCUMENT applies only to the xml data type and can be specified only if xml_schema_collection is also specified.

xml_schema_collection xml_schema_collection
适用范围SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.

仅适用于 xml 数据类型,用于将 XML 架构集合与该类型相关联 。Applies only to the xml data type for associating an XML schema collection with the type. 在架构中键入 xml 列之前,须先使用 CREATE XML SCHEMA COLLECTION 在数据库中创建该架构 。Before typing an xml column to a schema, the schema must first be created in the database by using CREATE XML SCHEMA COLLECTION.

还可以为拥有数据类型为 varbinary(max) 的 type_name 的列指定 FILESTREAM 存储属性 。Optionally specifies the FILESTREAM storage attribute for column that has a type_name of varbinary(max).

为列指定了 FILESTREAM 后,该表还必须有一个具有 ROWGUIDCOL 属性且数据类型为 uniqueidentifier 的列 。When FILESTREAM is specified for a column, the table must also have a column of the uniqueidentifier data type that has the ROWGUIDCOL attribute. 此列不得为空值且必须具有 UNIQUE 或 PRIMARY KEY 单列约束。This column must not allow null values and must have either a UNIQUE or PRIMARY KEY single-column constraint. 该列的 GUID 值必须在插入数据时由应用程序提供,或由使用 NEWID () 函数的 DEFAULT 约束提供。The GUID value for the column must be supplied either by an application when data is being inserted, or by a DEFAULT constraint that uses the NEWID () function.

如果为表定义了 FILESTREAM 列,则不能删除 ROWGUIDCOL 列并且不能更改相关的约束。The ROWGUIDCOL column cannot be dropped and the related constraints cannot be changed while there is a FILESTREAM column defined for the table. 仅当删除了最后一个 FILESTREAM 列后,才能删除 ROWGUIDCOL 列。The ROWGUIDCOL column can be dropped only after the last FILESTREAM column is dropped.

当为某个列指定了 FILESTREAM 存储属性时,该列的所有值都将存储在文件系统上的 FILESTREAM 数据容器中。When the FILESTREAM storage attribute is specified for a column, all values for that column are stored in a FILESTREAM data container on the file system.

有关如何使用列定义的示例,请参阅 FILESTREAM (Transact-SQL)For an example that shows how to use column definition, see FILESTREAM (SQL Server).

COLLATE collation_name COLLATE collation_name
指定列的排序规则。Specifies the collation of the 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 an SQL collation name. 如需获取列表和详细信息,请参阅 Windows 排序规则名称 (Transact-SQL)SQL Server 排序规则名称 (Transact-SQL)For a list and more information, see Windows Collation Name (Transact-SQL) and SQL Server Collation Name (Transact-SQL).

COLLATE 子句只能用来指定数据类型为 char、varchar、nchar 和 nvarchar 的列的排序规则 。The COLLATE clause can be used to specify the collations only of columns of the char, varchar, nchar, and nvarchar data types.

有关 COLLATE 子句的详细信息,请参阅 COLLATE (Transact-SQL)For more information about the COLLATE clause, see COLLATE (Transact-SQL).

确定列中是否允许空值。Determines whether null values are allowed in the column. 严格来讲,NULL 不是约束,但可以像指定 NOT NULL 那样指定它。NULL is not strictly a constraint but can be specified just like NOT NULL.

[ CONSTRAINT constraint_name ][ CONSTRAINT constraint_name ]
指定 DEFAULT 值定义的开头。Specifies the start of a DEFAULT value definition. 为了与 SQL ServerSQL Server 的早期版本兼容,可以为 DEFAULT 分配约束名称。To maintain compatibility with earlier versions of SQL ServerSQL Server, a constraint name can be assigned to a DEFAULT. 除了不能以数字符号 (#) 开头以外,约束名称还必须符合标识符规则 。constraint_name must follow the rules for identifiers, except that the name cannot start with a number sign (#). 如果未指定 constraint_name,则 DEFAULT 定义使用系统生成的名称 。If constraint_name is not specified, a system-generated name is assigned to the DEFAULT definition.

指定列的默认值的关键字。Is a keyword that specifies the default value for the column. DEFAULT 定义可用于为表中现有数据行的新列提供值。DEFAULT definitions can be used to provide values for a new column in the existing rows of data. DEFAULT 定义不能应用于 timestamp 列,或具有 IDENTITY 属性的列 。DEFAULT definitions cannot be applied to timestamp columns, or columns with an IDENTITY property. 如果为用户定义类型列指定了默认值,则该类型必须支持从 constant_expression 到用户定义类型的隐式转换 。If a default value is specified for a user-defined type column, the type must support an implicit conversion from constant_expression to the user-defined type.

constant_expression constant_expression
用作默认列值的文字值、NULL 或者系统函数。Is a literal value, a NULL, or a system function used as the default column value. 如果与定义为 .NET Framework.NET Framework 用户定义类型的列结合使用,则该类型的实现必须支持从 constant_expression 到用户定义类型的隐式转换 。If used in conjunction with a column defined to be of a .NET Framework.NET Framework user-defined type, the implementation of the type must support an implicit conversion from the constant_expression to the user-defined type.

添加列和 DEFAULT 约束时,如果列允许为空,那么对于现有行,使用 WITH VALUES 会将新列的值设置为 DEFAULT constant_expression 中给定的值。When adding a column AND a DEFAULT constraint, if the column allows NULLS using WITH VALUES will, for existing rows, set the new column's value to the value given in DEFAULT constant_expression. 如果要添加的列不允许为空,那么对于现有行,列值始终设置为 DEFAULT constant_expression 中给定的值。If the column being added does not allow NULLS, for existing rows, the column's value will always be set to the value given in the DEFAULT constant expression. 自 SQL Server 2012 起,这可能是元数据操作 adding-not-null-columns-as-an-online-operationStarting in SQL Server 2012 this may be a meta data operation adding-not-null-columns-as-an-online-operation. 如果在没有同时添加相关列的情况下使用它,它将不起作用。If this is used when the related column isn't also being added then it has no effect.

指定 DEFAULT constant_expression 中给定的值将存储在添加到现有行的新列中 。Specifies that the value given in DEFAULT constant_expression is stored in a new column that is added to existing rows. 如果所添加的列允许 Null 值且指定了 WITH VALUES,则默认值将存储在添加到现有行的新列中。If the added column allows null values and WITH VALUES is specified, the default value is stored in the new column that is added to existing rows. 如果没有对允许为空的列指定 WITH VALUES,那么现有行对应的新列中存储的是值 NULL。If WITH VALUES is not specified for columns that allow nulls, the value NULL is stored in the new column, in existing rows. 如果新列不允许 Null 值,那么不论是否指定 WITH VALUES,都将在新行中存储默认值。If the new column does not allow nulls, the default value is stored in new rows regardless of whether WITH VALUES is specified.

指定新列为标识列。Specifies that the new column is an identity column. SQL Server 数据库引擎SQL Server Database Engine为该列提供唯一的增量值。The SQL Server 数据库引擎SQL Server Database Engine provides a unique, incremental value for the column. 当您向现有表中添加标识符列时,还会将标识号添加到具有种子值和增量值的现有表行中。When you add identifier columns to existing tables, the identity numbers are added to the existing rows of the table with the seed and increment values. 无法保证行的更新顺序。The order in which the rows are updated is not guaranteed. 也会为添加的任何新行生成标识号。Identity numbers are also generated for any new rows that are added.

标识列通常与 PRIMARY KEY 约束一起使用,作为表的唯一行标识符。Identity columns are commonly used in conjunction with PRIMARY KEY constraints to serve as the unique row identifier for the table. 可以将 IDENTITY 属性分配到 tinyint、smallint、int、bigint、decimal(p,0) 或 numeric(p,0) 列 。The IDENTITY property can be assigned to a tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0) column. 每个表只能创建一个标识列。Only one identity column can be created per table. DEFAULT 关键字和绑定默认值不能用于标识列。The DEFAULT keyword and bound defaults cannot be used with an identity column. 要么同时指定种子和增量,要么都不指定。Either both the seed and increment must be specified, or neither. 如果二者都未指定,则取默认值 (1,1)。If neither are specified, the default is (1,1).


不能修改现有的表列以添加 IDENTITY 属性。You cannot modify an existing table column to add the IDENTITY property.

不支持向已发布的表添加标识列,因为将列复制到订阅服务器时,这会导致无法收敛。Adding an identity column to a published table is not supported because it can result in nonconvergence when the column is replicated to the Subscriber. 发布服务器的标识列中的值取决于受影响的表中行的物理存储顺序。The values in the identity column at the Publisher depend on the order in which the rows for the affected table are physically stored. 行在订阅服务器中的存储顺序可能会有所不同;因此对于相同的行,标识列的值可能会不同。The rows might be stored differently at the Subscriber; therefore, the value for the identity column can be different for the same rows..

若要通过允许显式插入值来禁用某列的 IDENTITY 属性,请使用 SET IDENTITY_INSERTTo disable the IDENTITY property of a column by allowing values to be explicitly inserted, use SET IDENTITY_INSERT.

seed seed
用于表中所加载的第一行的值。Is the value used for the first row loaded into the table.

increment increment
增加到上一个加载行的标识值的增量值。Is the incremental value added to the identity value of the previous row that is loaded.

适用范围SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.

可以为 IDENTITY 属性指定该子句。Can be specified for the IDENTITY property. 如果为 IDENTITY 属性指定了该子句,则当复制代理执行插入操作时,标识列中的值不会增加。If this clause is specified for the IDENTITY property, values are not incremented in identity columns when replication agents perform insert operations.

适用范围SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.

指定该列是一个行全局唯一标识符列。Specifies that the column is a row globally unique identifier column. 只能为 uniqueidentifier 列分配 ROWGUIDCOL,并且每个表中只有一个 uniqueidentifier 列能指定为 ROWGUIDCOL 列 。ROWGUIDCOL can only be assigned to a uniqueidentifier column, and only one uniqueidentifier column per table can be designated as the ROWGUIDCOL column. 不能为用户定义数据类型分配 ROWGUIDCOL。ROWGUIDCOL cannot be assigned to columns of user-defined data types.

ROWGUIDCOL 并不强制列中所存储值的唯一性。ROWGUIDCOL does not enforce uniqueness of the values stored in the column. 另外,该属性也不会为插入到表中的新行自动生成值。Also, ROWGUIDCOL does not automatically generate values for new rows that are inserted into the table. 若要为每列生成唯一值,则可以在 INSERT 语句中使用 NEWID 函数,也可以将 NEWID 函数指定为列的默认值。To generate unique values for each column, either use the NEWID function on INSERT statements or specify the NEWID function as the default for the column. 有关详细信息,请参阅 NEWID (Transact-SQL)INSERT (Transact-SQL)For more information, see NEWID (Transact-SQL)and INSERT (Transact-SQL).

指示列为稀疏列。Indicates that the column is a sparse column. 稀疏列已针对 NULL 值进行了存储优化。The storage of sparse columns is optimized for null values. 不能将稀疏列指定为 NOT NULL。Sparse columns cannot be designated as NOT NULL. 有关稀疏列的其他限制和详细信息,请参阅使用稀疏列For additional restrictions and more information about sparse columns, see Use Sparse Columns.

有关列约束参数的定义,请参阅 column_constraint (Transact-SQL)For the definitions of the column constraint arguments, see column_constraint (Transact-SQL).

使用 Always Encrypted 功能指定加密列。Specifies encrypting columns by using the Always Encrypted feature.

指定列加密密钥。Specifies the column encryption key. 有关详细信息,请参阅 CREATE COLUMN ENCRYPTION KEY (Transact-SQL)For more information, see CREATE COLUMN ENCRYPTION KEY (Transact-SQL).

确定性加密 使用一种对任何给定的纯文本值始终生成相同的加密值的方法。Deterministic encryption uses a method which always generates the same encrypted value for any given plain text value. 使用确定性加密可基于加密值进行下列操作:使用相等性比较进行搜索、分组、使用相等性联接联接各表,此外,还允许未经授权的用户通过检查加密列中的模式来猜测加密值的相关信息。Using deterministic encryption allows searching using equality comparison, grouping, and joining tables using equality joins based on encrypted values, but can also allow unauthorized users to guess information about encrypted values by examining patterns in the encrypted column. 只有使用相同的列加密密钥对两列进行加密,才能在已进行确定性加密的该列上联结两个表。Joining two tables on columns encrypted deterministically is only possible if both columns are encrypted using the same column encryption key. 确定性加密必须使用具有字符列的 binary2 排序顺序的列排序规则。Deterministic encryption must use a column collation with a binary2 sort order for character columns.

随机加密 使用一种以更不可预测地方式加密数据的方法。Randomized encryption uses a method that encrypts data in a less predictable manner. 随机加密更为安全,但会阻止对加密列进行任何计算和索引编制,除非你的 SQL Server 实例支持具有安全 enclave 的 Always EncryptedRandomized encryption is more secure, but it prevents any computations and indexing on encrypted columns, unless your SQL Server instance supports Always Encrypted with secure enclaves.

如果使用的是 Always Encrypted(不带安全 enclave),请对要使用参数或分组参数搜索的列使用确定性加密,例如政府 ID 号。If you are using Always Encrypted (without secure enclaves), use deterministic encryption for columns to be searched with parameters or grouping parameters, for example a government ID number. 对以下数据使用随机加密,即未与其他记录分组的数据、未用于联结表的数据、因使用其他列(例如事务号)查找包含已加密的兴趣列,而不用于搜索的数据。Use randomized encryption, for data such as a credit card number, which is not grouped with other records, or used to join tables, and which is not searched for because you use other columns (such as a transaction number) to find the row which contains the encrypted column of interest.

如果使用的是具有安全 enclave 的 Always Encrypted,则随机加密是推荐的加密类型。If you are using Always Encrypted with secure enclaves, randomized encryption is a recommended encryption type.

列必须是符合条件的数据类型。Columns must be of a qualifying data type.

适用范围:SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017SQL 数据库SQL DatabaseApplies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017SQL Server 2017, SQL 数据库SQL Database.
须是“AEAD_AES_256_CBC_HMAC_SHA_256” 。Must be 'AEAD_AES_256_CBC_HMAC_SHA_256'.

有关包括功能约束在内的详细信息,请参阅 Always Encrypted (Transact-SQL)For more information including feature constraints, see Always Encrypted (Database Engine).

ADD MASKED WITH ( FUNCTION = ' mask_function ') ADD MASKED WITH ( FUNCTION = ' mask_function ')
适用范围:SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017SQL 数据库SQL DatabaseApplies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017SQL Server 2017, SQL 数据库SQL Database.

指定动态数据掩码。Specifies a dynamic data mask. mask_function 是具有相应参数的掩码函数的名称 。mask_function is the name of the masking function with the appropriate parameters. 可用函数包括:The following functions are available:

  • default()default()

  • email()email()

  • partial()partial()

  • random()random()

有关函数参数的信息,请参阅动态数据掩码For function parameters, see Dynamic Data Masking.


如果添加的列具有 uniqueidentifier 数据类型,则可以通过使用一个使用 NEWID() 函数的默认值对该列进行定义,以向表中的每个现有行的新列提供唯一标识符值 。If a column is added having a uniqueidentifier data type, it can be defined with a default that uses the NEWID() function to supply the unique identifier values in the new column for each existing row in the table.

数据库引擎Database Engine 不强制在列定义中指定 DEFAULT、IDENTITY、ROWGUIDCOL 或列约束的顺序。The 数据库引擎Database Engine does not enforce an order for specifying DEFAULT, IDENTITY, ROWGUIDCOL, or column constraints in a column definition.

如果添加列导致数据行大小超过 8060 字节,ALTER TABLE 语句将失败。ALTER TABLE statement will fail if adding the column will cause the data row size to exceed 8060 bytes.


有关示例,请参阅 ALTER TABLE (Transact-SQL)For examples, see ALTER TABLE (Transact-SQL).

另请参阅See Also