ALTER FUNCTION (Transact-SQL)ALTER FUNCTION (Transact-SQL)

适用于: SQL Server Azure SQL 数据库 Azure Synapse Analytics 并行数据仓库

更改先前通过执行 CREATE FUNCTION 语句创建的现有 Transact-SQLTransact-SQL 或 CLR 函数,但不更改权限,也不影响任何相关的函数、存储过程或触发器。Alters an existing Transact-SQLTransact-SQL or CLR function that was previously created by executing the CREATE FUNCTION statement, without changing permissions and without affecting any dependent functions, stored procedures, or triggers.

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

语法Syntax

-- Transact-SQL Scalar Function Syntax    
ALTER FUNCTION [ schema_name. ] function_name   
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type   
    [ = default ] }   
    [ ,...n ]  
  ]  
)  
RETURNS return_data_type  
    [ WITH <function_option> [ ,...n ] ]  
    [ AS ]  
    BEGIN   
        function_body   
        RETURN scalar_expression  
    END  
[ ; ]
-- Transact-SQL Inline Table-Valued Function Syntax
ALTER FUNCTION [ schema_name. ] function_name   
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type   
    [ = default ] }   
    [ ,...n ]  
  ]  
)  
RETURNS TABLE  
    [ WITH <function_option> [ ,...n ] ]  
    [ AS ]  
    RETURN [ ( ] select_stmt [ ) ]  
[ ; ]  
-- Transact-SQL Multistatement Table-valued Function Syntax
ALTER FUNCTION [ schema_name. ] function_name   
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type   
    [ = default ] }   
    [ ,...n ]  
  ]  
)  
RETURNS @return_variable TABLE <table_type_definition>  
    [ WITH <function_option> [ ,...n ] ]  
    [ AS ]  
    BEGIN   
        function_body   
        RETURN  
    END  
[ ; ]  
-- Transact-SQL Function Clauses   
<function_option>::=   
{  
    [ ENCRYPTION ]  
  | [ SCHEMABINDING ]  
  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]  
  | [ EXECUTE_AS_Clause ]  
} 

<table_type_definition>:: =   
( { <column_definition> <column_constraint>   
  | <computed_column_definition> }   
    [ <table_constraint> ] [ ,...n ]  
)   
<column_definition>::=  
{  
    { column_name data_type }  
    [ [ DEFAULT constant_expression ]   
      [ COLLATE collation_name ] | [ ROWGUIDCOL ]  
    ]  
    | [ IDENTITY [ (seed , increment ) ] ]  
    [ <column_constraint> [ ...n ] ]   
}  

<column_constraint>::=   
{  
    [ NULL | NOT NULL ]   
    { PRIMARY KEY | UNIQUE }  
      [ CLUSTERED | NONCLUSTERED ]   
        [ WITH FILLFACTOR = fillfactor   
        | WITH ( < index_option > [ , ...n ] )  
      [ ON { filegroup | "default" } ]  
  | [ CHECK ( logical_expression ) ] [ ,...n ]  
}  
  
<computed_column_definition>::=  
column_name AS computed_column_expression   
  
<table_constraint>::=  
{   
    { PRIMARY KEY | UNIQUE }  
      [ CLUSTERED | NONCLUSTERED ]   
      ( column_name [ ASC | DESC ] [ ,...n ] )  
        [ WITH FILLFACTOR = fillfactor   
        | WITH ( <index_option> [ , ...n ] )  
  | [ CHECK ( logical_expression ) ] [ ,...n ]  
}  
  
<index_option>::=  
{   
    PAD_INDEX = { ON | OFF }   
  | FILLFACTOR = fillfactor   
  | IGNORE_DUP_KEY = { ON | OFF }  
  | STATISTICS_NORECOMPUTE = { ON | OFF }   
  | ALLOW_ROW_LOCKS = { ON | OFF }  
  | ALLOW_PAGE_LOCKS ={ ON | OFF }   
}  
-- CLR Scalar and Table-Valued Function Syntax
ALTER FUNCTION [ schema_name. ] function_name   
( { @parameter_name [AS] [ type_schema_name. ] parameter_data_type   
    [ = default ] }   
    [ ,...n ]  
)  
RETURNS { return_data_type | TABLE <clr_table_type_definition> }  
    [ WITH <clr_function_option> [ ,...n ] ]  
    [ AS ] EXTERNAL NAME <method_specifier>  
[ ; ]  
-- CLR Function Clauses
<method_specifier>::=  
    assembly_name.class_name.method_name  
 
  
<clr_function_option>::=  
}  
    [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]  
  | [ EXECUTE_AS_Clause ]  
}  
  
<clr_table_type_definition>::=   
( { column_name data_type } [ ,...n ] )  
  
-- Syntax for In-Memory OLTP: Natively compiled, scalar user-defined function  
ALTER FUNCTION [ schema_name. ] function_name    
 ( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type   
    [ NULL | NOT NULL ] [ = default ] }   
    [ ,...n ]   
  ]   
)   
RETURNS return_data_type  
    [ WITH <function_option> [ ,...n ] ]   
    [ AS ]   
    BEGIN ATOMIC WITH (set_option [ ,... n ])  
        function_body   
        RETURN scalar_expression  
    END  
    
<function_option>::=   
{ |  NATIVE_COMPILATION   
  |  SCHEMABINDING   
  | [ EXECUTE_AS_Clause ]   
  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]   
}  

参数Arguments

schema_nameschema_name
用户定义函数所属的架构的名称。Is the name of the schema to which the user-defined function belongs.

function_namefunction_name
要更改的用户定义函数。Is the user-defined function to be changed.

备注

即使未指定参数,函数名称后也需要加上括号。Parentheses are required after the function name even if a parameter is not specified.

@ parameter_name@ parameter_name
用户定义函数中的参数。Is a parameter in the user-defined function. 可声明一个或多个参数。One or more parameters can be declared.

一个函数最多可以有 2,100 个参数。A function can have a maximum of 2,100 parameters. 执行函数时,如果未定义参数的默认值,则用户必须提供每个已声明参数的值。The value of each declared parameter must be supplied by the user when the function is executed, unless a default for the parameter is defined.

通过将 at 符号 (@) 用作第一个字符来指定参数名称。Specify a parameter name by using an at sign (@) as the first character. 参数名称必须符合标识符规则。The parameter name must comply with the rules for identifiers. 参数是对应于函数的局部参数;其他函数中可使用相同的参数名称。Parameters are local to the function; the same parameter names can be used in other functions. 参数只能代替常量,而不能用于代替表名、列名或其他数据库对象的名称。Parameters can take the place only of constants; they cannot be used instead of table names, column names, or the names of other database objects.

备注

在存储过程和用户定义函数中传递参数,或者在批处理语句中声明和设置变量时,不执行 ANSI_WARNINGS。ANSI_WARNINGS is not honored when passing parameters in a stored procedure, user-defined function, or when declaring and setting variables in a batch statement. 例如,如果将一个变量定义为 char(3),然后将其值设置为大于三个字符,则数据会被截断为定义的大小,并且 INSERT 或 UPDATE 语句可以成功执行。For example, if a variable is defined as char(3), and then set to a value larger than three characters, the data is truncated to the defined size and the INSERT or UPDATE statement succeeds.

[ type_schema_name.[ type_schema_name. ] parameter_data_type] parameter_data_type
参数的数据类型及其所属的架构(可选)。Is the parameter data type and optionally, the schema to which it belongs. 对于 Transact-SQLTransact-SQL 函数,可以使用除 timestamp 数据类型之外的所有数据类型(包括 CLR 用户定义类型)。For Transact-SQLTransact-SQL functions, all data types, including CLR user-defined types, are allowed except the timestamp data type. 对于 CLR 函数,允许使用除 text、ntext、image 和 timestamp 数据类型之外的所有数据类型(包括 CLR 用户定义类型) 。For CLR functions, all data types, including CLR user-defined types, are allowed except text, ntext, image, and timestamp data types. 不能将非标量类型 cursor 和 table 指定为 Transact-SQLTransact-SQL 函数或 CLR 函数中的参数数据类型 。The nonscalar types cursor and table cannot be specified as a parameter data type in either Transact-SQLTransact-SQL or CLR functions.

如果未指定 type_schema_name,则 SQL Server 2005 数据库引擎SQL Server 2005 Database Engine 按照下列顺序查找 parameter_data_type :If type_schema_name is not specified, the SQL Server 2005 数据库引擎SQL Server 2005 Database Engine looks for the parameter_data_type in the following order:

  • 包含 SQL Server 系统数据类型名称的架构。The schema that contains the names of SQL Server system data types.

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

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

[ =default ][ =default ]
参数的默认值。Is a default value for the parameter. 如果定义了 default 值,则无需指定此参数的值即可执行函数。If a default value is defined, the function can be executed without specifying a value for that parameter.

备注

可以为除 varchar(max) 和 varbinary(max) 数据类型之外的 CLR 函数指定默认的参数值 。Default parameter values can be specified for CLR functions except for varchar(max) and varbinary(max) data types.

如果函数的参数有默认值,则调用函数来检索默认值时必须指定 DEFAULT 关键字。When a parameter of the function has a default value, the keyword DEFAULT must be specified when calling the function to retrieve the default value. 此行为与在存储过程中使用具有默认值的参数不同,在后一种情况下,不提供参数同样意味着使用默认值。This behavior is different from using parameters with default values in stored procedures in which omitting the parameter also implies the default value.

return_data_typereturn_data_type
标量用户定义函数的返回值。Is the return value of a scalar user-defined function. 对于 Transact-SQLTransact-SQL 函数,可以使用除 timestamp 数据类型之外的所有数据类型(包括 CLR 用户定义类型)。For Transact-SQLTransact-SQL functions, all data types, including CLR user-defined types, are allowed except the timestamp data type. 对于 CLR 函数,允许使用除 text、ntext、image 和 timestamp 数据类型之外的所有数据类型(包括 CLR 用户定义类型) 。For CLR functions, all data types, including CLR user-defined types, are allowed except text, ntext, image, and timestamp data types. 不能将非标量类型 cursor 和 table 指定为 Transact-SQLTransact-SQL 函数或 CLR 函数中的返回数据类型 。The nonscalar types cursor and table cannot be specified as a return data type in either Transact-SQLTransact-SQL or CLR functions.

function_bodyfunction_body
指定一系列定义函数值的 Transact-SQLTransact-SQL 语句,这些语句在一起使用不会产生负面影响(例如修改表)。Specifies that a series of Transact-SQLTransact-SQL statements, which together do not produce a side effect such as modifying a table, define the value of the function. function_body 仅用于标量函数和多语句表值函数。function_body is used only in scalar functions and multistatement table-valued functions.

在标量函数中,function_body 是一系列 Transact-SQLTransact-SQL 语句,这些语句一起使用可计算出标量值。In scalar functions, function_body is a series of Transact-SQLTransact-SQL statements that together evaluate to a scalar value.

在多语句表值函数中,function_body 是一系列 Transact-SQLTransact-SQL 语句,这些语句将填充 TABLE 返回变量。In multistatement table-valued functions, function_body is a series of Transact-SQLTransact-SQL statements that populate a TABLE return variable.

scalar_expressionscalar_expression
指定标量函数返回标量值。Specifies that the scalar function returns a scalar value.

TABLETABLE
指定表值函数的返回值为表。Specifies that the return value of the table-valued function is a table. 只有常量和 @local\_variables 可以传递到表值函数。Only constants and @local_variables can be passed to table-valued functions.

在内联表值函数中,TABLE 返回值是通过单个 SELECT 语句定义的。In inline table-valued functions, the TABLE return value is defined through a single SELECT statement. 内联函数没有关联的返回变量。Inline functions do not have associated return variables.

在多语句表值函数中,@return_variable 是 TABLE 变量,用于存储和汇总应作为函数值返回的行。In multistatement table-valued functions, @return_variable is a TABLE variable used to store and accumulate the rows that should be returned as the value of the function. 只能将 @return\_variable 指定用于 Transact-SQLTransact-SQL 函数,而不能用于 CLR 函数。@return_variable can be specified only for Transact-SQLTransact-SQL functions and not for CLR functions.

select-stmtselect-stmt
定义内联表值函数返回值的单个 SELECT 语句。Is the single SELECT statement that defines the return value of an inline table-valued function.

EXTERNAL NAME <method_specifier>assembly_name.class_name.method_name EXTERNAL NAME <method_specifier>assembly_name.class_name.method_name
适用于SQL Server 2008SQL Server 2008 及更高版本。Applies to: SQL Server 2008SQL Server 2008 and later.

指定要与函数绑定的程序集的方法。Specifies the method of an assembly to bind with the function. assembly_name 必须与 SQL ServerSQL Server 中当前数据库内具有可见性的现有程序集匹配。assembly_name must match an existing assembly in SQL ServerSQL Server in the current database with visibility on. class_name 必须是有效的 SQL ServerSQL Server 标识符,并且必须作为类存在于程序集中。class_name must be a valid SQL ServerSQL Server identifier and must exist as a class in the assembly. 如果类包含一个使用句点 (.) 分隔命名空间各部分的限定命名空间的名称,则必须使用方括号 ([]) 或引号 ("") 将类名称分隔开 。If the class has a namespace-qualified name that uses a period (.) to separate namespace parts, the class name must be delimited by using brackets ([]) or quotation marks (""). method_name 必须是有效的 SQL ServerSQL Server 标识符,并且必须作为静态方法存在于指定类中。method_name must be a valid SQL ServerSQL Server identifier and must exist as a static method in the specified class.

备注

默认情况下,SQL ServerSQL Server 不能执行 CLR 代码。By default, SQL ServerSQL Server cannot execute CLR code. 可以创建、修改和删除引用公共语言运行时模块的数据库对象;不过,只有在启用 clr enabled 选项之后,才能在 SQL ServerSQL Server 中执行这些引用。You can create, modify, and drop database objects that reference common language runtime modules; however, you cannot execute these references in SQL ServerSQL Server until you enable the clr enabled option. 若要启用该选项,请使用 sp_configureTo enable the option, use sp_configure.

备注

此选项在包含数据库中不可用。This option is not available in a contained database.

<table_type_definition>( { <column_definition> <column_constraint> | <computed_column_definition> } [ <table_constraint> ] [ ,...n ]) <table_type_definition>( { <column_definition> <column_constraint> | <computed_column_definition> } [ <table_constraint> ] [ ,...n ])
定义 Transact-SQLTransact-SQL 函数的表数据类型。Defines the table data type for a Transact-SQLTransact-SQL function. 表声明包含列定义和列约束(或表约束)。The table declaration includes column definitions and column or table constraints.

< clr_table_type_definition > ( { column_name**data_type } [ ,...n ] ) 适用于:SQL Server 2008SQL Server 2008 及更高版本、SQL 数据库SQL Database某些区域为预览版) 。< clr_table_type_definition > ( { column_name**data_type } [ ,...n ] ) Applies to: SQL Server 2008SQL Server 2008 and later, SQL 数据库SQL Database (Preview in some regions).

定义 CLR 函数的表数据类型。Defines the table data types for a CLR function. 表声明仅包含列名称和数据类型。The table declaration includes only column names and data types.

NULL|NOT NULLNULL|NOT NULL
仅本机编译的标量用户定义函数支持该参数。Supported only for natively compiled, scalar user-defined functions. 有关详细信息,请参阅内存中 OLTP 的标量用户定义函数For more information, see Scalar User-Defined Functions for In-Memory OLTP.

NATIVE_COMPILATIONNATIVE_COMPILATION
指示用户定义函数是否已本机编译。Indicates whether a user-defined function is natively compiled. 对于本机编译的标量用户定义函数,此参数是必需的。This argument is required for natively compiled, scalar user-defined functions.

对函数执行 ALTER 操作时需要使用 NATIVE_COMPILATION 参数,并且仅可在该函数是使用 NATIVE_COMPILATION 参数创建时使用。The NATIVE_COMPILATION argument is required when you ALTER the function, and can only be used, if the function was created with the NATIVE_COMPILATION argument.

BEGIN ATOMIC WITHBEGIN ATOMIC WITH
仅本机编译的标量用户定义函数支持该参数,且该参数是必需的。Supported only for natively compiled, scalar user-defined functions, and is required. 有关详细信息,请参阅 Atomic BlocksFor more information, see Atomic Blocks.

SCHEMABINDINGSCHEMABINDING
对于本机编译的标量用户定义函数,SCHEMABINDING 参数是必需的。The SCHEMABINDING argument is required for natively compiled, scalar user-defined functions.

<function_option>::= 和 <clr_function_option>::=<function_option>::= and <clr_function_option>::=

指定函数将具有以下一个或多个选项:Specifies the function will have one or more of the following options.

ENCRYPTIONENCRYPTION
适用于SQL Server 2008SQL Server 2008 及更高版本。Applies to: SQL Server 2008SQL Server 2008 and later.

指示数据库引擎Database Engine对目录视图中包含 ALTER FUNCTION 语句文本的列进行加密。Indicates that the 数据库引擎Database Engine encrypts the catalog view columns that contains the text of the ALTER FUNCTION statement. 使用 ENCRYPTION 可以防止将函数作为 SQL ServerSQL Server 复制的一部分发布。Using ENCRYPTION prevents the function from being published as part of SQL ServerSQL Server replication. 不能为 CLR 函数指定 ENCRYPTION。ENCRYPTION cannot be specified for CLR functions.

SCHEMABINDINGSCHEMABINDING
指定将函数绑定到其引用的数据库对象。Specifies that the function is bound to the database objects that it references. 如果指定了 SCHEMABINDING,则不能按照将影响函数定义的方式修改基对象。When SCHEMABINDING is specified, the base objects cannot be modified in a way that would affect the function definition. 必须首先修改或删除函数定义本身,才能删除将要修改的对象的依赖关系。The function definition itself must first be modified or dropped to remove dependencies on the object that is to be modified..

只有发生下列操作之一时,才会删除函数与其引用对象的绑定:The binding of the function to the objects it references is removed only when one of the following actions occurs:

  • 删除函数。The function is dropped.

  • 在未指定 SCHEMABINDING 选项的情况下,使用 ALTER 语句修改函数。The function is modified by using the ALTER statement with the SCHEMABINDING option not specified.

有关函数绑定到架构前必须满足的条件的列表,请参阅 CREATE FUNCTION (Transact-SQL)For a list of conditions that must be met before a function can be schema bound, see CREATE FUNCTION (Transact-SQL).

RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUTRETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT
指定标量值函数的 OnNULLCall 属性。Specifies the OnNULLCall attribute of a scalar-valued function. 如果未指定,则默认为 CALLED ON NULL INPUT。If not specified, CALLED ON NULL INPUT is implied by default. 这意味着即使传递的参数为 NULL,也将执行函数体。This means that the function body executes even if NULL is passed as an argument.

如果在 CLR 函数中指定了 RETURNS NULL ON NULL INPUT,它指示当 SQL ServerSQL Server 接收到的任何一个参数为 NULL 时,它可以返回 NULL,而无需实际调用函数体。If RETURNS NULL ON NULL INPUT is specified in a CLR function, it indicates that SQL ServerSQL Server can return NULL when any of the arguments it receives is NULL, without actually invoking the body of the function. 如果 <method_specifier> 中指定的方法已具有指示 RETURNS NULL ON NULL INPUT 的自定义属性,但 ALTER FUNCTION 语句指示 CALLED ON NULL INPUT,则优先采用 ALTER FUNCTION 语句指示的属性。If the method specified in <method_specifier> already has a custom attribute that indicates RETURNS NULL ON NULL INPUT, but the ALTER FUNCTION statement indicates CALLED ON NULL INPUT, the ALTER FUNCTION statement takes precedence. 不能为 CLR 表值函数指定 OnNULLCall 属性。The OnNULLCall attribute cannot be specified for CLR table-valued functions.

EXECUTE AS 子句EXECUTE AS Clause
指定用于执行用户定义函数的安全上下文。Specifies the security context under which the user-defined function is executed. 所以,您可以控制 SQL ServerSQL Server 使用哪一用户帐户来验证针对该函数引用的任何数据库对象的权限。Therefore, you can control which user account SQL ServerSQL Server uses to validate permissions on any database objects referenced by the function.

备注

不能为内联用户定义函数指定 EXECUTE AS。EXECUTE AS cannot be specified for inline user-defined functions.

有关详细信息,请参阅 EXECUTE AS 子句 (Transact-SQL)For more information, see EXECUTE AS Clause (Transact-SQL).

< column_definition >::=< column_definition >::=

定义表数据类型。Defines the table data type. 表声明包含列定义和约束。The table declaration includes column definitions and constraints. 对于 CLR 函数,只能指定 column_name 和 data_type 。For CLR functions, only column_name and data_type can be specified.

column_namecolumn_name
表中列的名称。Is the name of a column in the table. 列名称必须遵循标识符的规则,且在表中必须唯一。Column names must comply with the rules for identifiers and must be unique in the table. column_name 可以包含 1 到 128 个字符。column_name can consist of 1 through 128 characters.

data_typedata_type
指定列数据类型。Specifies the column data type. 对于 Transact-SQLTransact-SQL 函数,可以使用除 timestamp 之外的所有数据类型(包括 CLR 用户定义类型)。For Transact-SQLTransact-SQL functions, all data types, including CLR user-defined types, are allowed except timestamp. 对于 CLR 函数,可以使用除 text、ntext、image、char、varchar、varchar(max) 和 timestamp 之外的所有数据类型(包括 CLR 用户定义类型)。在 Transact-SQLTransact-SQL 函数或 CLR 函数中,不能将非标量类型 cursor 指定为列数据类型 。For CLR functions, all data types, including CLR user-defined types, are allowed except text, ntext, image, char, varchar, varchar(max), and timestamp.The nonscalar type cursor cannot be specified as a column data type in either Transact-SQLTransact-SQL or CLR functions.

DEFAULT constant_expressionDEFAULT constant_expression
如果在插入过程中未显式提供值,则指定为列提供的值。Specifies the value provided for the column when a value is not explicitly supplied during an insert. constant_expression 可以是常量、NULL 或系统函数值。constant_expression is a constant, NULL, or a system function value. DEFAULT 定义可以应用于除具有 IDENTITY 属性的列之外的任何列。DEFAULT definitions can be applied to any column except those that have the IDENTITY property. 不能为 CLR 表值函数指定 DEFAULT。DEFAULT cannot be specified for CLR table-valued functions.

COLLATE collation_nameCOLLATE collation_name
指定列的排序规则。Specifies the collation for 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 a SQL collation name. 如需获取列表和详细信息,请参阅 Windows 排序规则名称 (Transact-SQL)SQL Server 排序规则名称 (Transact-SQL)For a list of 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 change the collations only of columns of the char, varchar, nchar, and nvarchar data types.

不能为 CLR 表值函数指定 COLLATE。COLLATE cannot be specified for CLR table-valued functions.

ROWGUIDCOLROWGUIDCOL
指示新列是行的全局唯一标识符列。Indicates that the new column is a row global unique identifier column. 对于每个表,只能将其中的一个 uniqueidentifier 列指定为 ROWGUIDCOL 列。Only one uniqueidentifier column per table can be designated as the ROWGUIDCOL column. ROWGUIDCOL 属性只能分配给 uniqueidentifier 列。The ROWGUIDCOL property can be assigned only to a uniqueidentifier column.

ROWGUIDCOL 属性并不强制列中所存储值的唯一性。The ROWGUIDCOL property does not enforce uniqueness of the values stored in the column. 该属性也不会为插入表的新行自动生成值。It also does not automatically generate values for new rows inserted into the table. 若要为每列生成唯一值,请在 INSERT 语句中使用 NEWID 函数。To generate unique values for each column, use the NEWID function on INSERT statements. 可以指定默认值;但是,不能将 NEWID 指定为默认值。A default value can be specified; however, NEWID cannot be specified as the default.

IDENTITYIDENTITY
指示新列是标识列。Indicates that the new column is an identity column. 在为表添加新行时,SQL ServerSQL Server 将为该列提供唯一的增量值。When a new row is added to the table, SQL ServerSQL Server provides a unique, incremental value for the column. 标识列通常与 PRIMARY KEY 约束一起使用,作为表的唯一行标识符。Identity columns are typically used together with PRIMARY KEY constraints to serve as the unique row identifier for the table. 可以将 IDENTITY 属性分配到 tinyint、smallint、int、decimal(p,0) 或 numeric(p,0) 列 。The IDENTITY property can be assigned to tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0) columns. 每个表只能创建一个标识列。Only one identity column can be created per table. 不能对标识列使用绑定默认值和 DEFAULT 约束。Bound defaults and DEFAULT constraints cannot be used with an identity column. 必须同时指定 seed 和 increment,或者都不指定 。You must specify both the seed and increment or neither. 如果二者都未指定,则取默认值 (1,1)。If neither is specified, the default is (1,1).

不能为 CLR 表值函数指定 IDENTITY。IDENTITY cannot be specified for CLR table-valued functions.

seedseed
要分配给表中第一行的整数值。Is the integer value to be assigned to the first row in the table.

incrementincrement
要加到表中后续行的 seed 值上的整数值。Is the integer value to add to the seed value for successive rows in the table.

< column_constraint >::= 和 < table_constraint>::=< column_constraint >::= and < table_constraint>::=

为指定列或表定义约束。Defines the constraint for a specified column or table. 对于 CLR 函数,允许的唯一约束类型为 NULL。For CLR functions, the only constraint type allowed is NULL. 不允许命名约束。Named constraints are not allowed.

NULL | NOT NULLNULL | NOT NULL
确定列中是否允许空值。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. 不能为 CLR 表值函数指定 NOT NULL。NOT NULL cannot be specified for CLR table-valued functions.

PRIMARY KEYPRIMARY KEY
一个约束,该约束通过唯一索引来强制指定列的实体完整性。Is a constraint that enforces entity integrity for a specified column through a unique index. 在表值用户定义函数中,只能对每个表中的一列创建 PRIMARY KEY 约束。In table-valued user-defined functions, the PRIMARY KEY constraint can be created on only one column per table. 不能为 CLR 表值函数指定 PRIMARY KEY。PRIMARY KEY cannot be specified for CLR table-valued functions.

UNIQUEUNIQUE
一个约束,该约束通过唯一索引为一个或多个指定列提供实体完整性。Is a constraint that provides entity integrity for a specified column or columns through a unique index. 一个表可以有多个 UNIQUE 约束。A table can have multiple UNIQUE constraints. 不能为 CLR 表值函数指定 UNIQUE。UNIQUE cannot be specified for CLR table-valued functions.

CLUSTERED | NONCLUSTEREDCLUSTERED | NONCLUSTERED
指示为 PRIMARY KEY 或 UNIQUE 约束创建聚集索引还是非聚集索引。Indicate that a clustered or a nonclustered index is created for the PRIMARY KEY or UNIQUE constraint. PRIMARY KEY 约束使用 CLUSTERED,而 UNIQUE 约束使用 NONCLUSTERED。PRIMARY KEY constraints use CLUSTERED, and UNIQUE constraints use NONCLUSTERED.

只能为一个约束指定 CLUSTERED。CLUSTERED can be specified for only one constraint. 如果为 UNIQUE 约束指定了 CLUSTERED,并且指定了 PRIMARY KEY 约束,则 PRIMARY KEY 使用 NONCLUSTERED。If CLUSTERED is specified for a UNIQUE constraint and a PRIMARY KEY constraint is also specified, the PRIMARY KEY uses NONCLUSTERED.

不能为 CLR 表值函数指定 CLUSTERED 和 NONCLUSTERED。CLUSTERED and NONCLUSTERED cannot be specified for CLR table-valued functions.

CHECKCHECK
一个约束,该约束通过限制可输入一列或多列中的可能值来强制实现域完整性。Is a constraint that enforces domain integrity by limiting the possible values that can be entered into a column or columns. 不能为 CLR 表值函数指定 CHECK 约束。CHECK constraints cannot be specified for CLR table-valued functions.

logical_expressionlogical_expression
返回 TRUE 或 FALSE 的逻辑表达式。Is a logical expression that returns TRUE or FALSE.

<computed_column_definition>::=<computed_column_definition>::=

指定计算列。Specifies a computed column. 有关计算列的详细信息,请参阅 CREATE TABLE (Transact-SQL)For more information about computed columns, see CREATE TABLE (Transact-SQL).

column_namecolumn_name
计算列的名称。Is the name of the computed column.

computed_column_expressioncomputed_column_expression
定义计算列的值的表达式。Is an expression that defines the value of a computed column.

<index_option>::=<index_option>::=

为 PRIMARY KEY 或 UNIQUE 索引指定索引选项。Specifies the index options for the PRIMARY KEY or UNIQUE index. 有关索引选项的详细信息,请参阅 CREATE INDEX (Transact-SQL)For more information about index options, see CREATE INDEX (Transact-SQL).

PAD_INDEX = { ON | OFF }PAD_INDEX = { ON | OFF }
指定索引填充。Specifies index padding. 默认为 OFF。The default is OFF.

FILLFACTOR = fillfactorFILLFACTOR = fillfactor
指定一个百分比,指示在数据库引擎Database Engine创建或更改索引的过程中,应将每个索引页面的叶级填充到什么程度。Specifies a percentage that indicates how full the 数据库引擎Database Engine should make the leaf level of each index page during index creation or change. fillfactor 必须是 1 到 100 之间的整数。fillfactor must be an integer value from 1 to 100. 默认值为 0。The default is 0.

IGNORE_DUP_KEY = { ON | OFF }IGNORE_DUP_KEY = { ON | OFF }
指定在插入操作尝试向唯一索引插入重复键值时的错误响应。Specifies the error response when an insert operation attempts to insert duplicate key values into a unique index. IGNORE_DUP_KEY 选项仅适用于创建或重新生成索引后发生的插入操作。The IGNORE_DUP_KEY option applies only to insert operations after the index is created or rebuilt. 默认为 OFF。The default is OFF.

STATISTICS_NORECOMPUTE = { ON | OFF }STATISTICS_NORECOMPUTE = { ON | OFF }
指定是否重新计算分布统计信息。Specifies whether distribution statistics are recomputed. 默认为 OFF。The default is OFF.

ALLOW_ROW_LOCKS = { ON | OFF }ALLOW_ROW_LOCKS = { ON | OFF }
指定是否允许行锁。Specifies whether row locks are allowed. 默认值为 ON。The default is ON.

ALLOW_PAGE_LOCKS = { ON | OFF }ALLOW_PAGE_LOCKS = { ON | OFF }
指定是否允许使用页锁。Specifies whether page locks are allowed. 默认值为 ON。The default is ON.

备注Remarks

不能用 ALTER FUNCTION 将标量值函数更改为表值函数,反之亦然。ALTER FUNCTION cannot be used to change a scalar-valued function to a table-valued function, or vice versa. 同样,也不能用 ALTER FUNCTION 将内联函数更改为多语句函数,反之亦然。Also, ALTER FUNCTION cannot be used to change an inline function to a multistatement function, or vice versa. 不能使用 ALTER FUNCTION 将 Transact-SQLTransact-SQL 函数更改为 CLR 函数,反之亦然。ALTER FUNCTION cannot be used to change a Transact-SQLTransact-SQL function to a CLR function or vice-versa.

下列 Service Broker 语句不能包含在 Transact-SQLTransact-SQL 用户定义函数的定义中:The following Service Broker statements cannot be included in the definition of a Transact-SQLTransact-SQL user-defined function:

  • BEGIN DIALOG CONVERSATIONBEGIN DIALOG CONVERSATION
  • END CONVERSATIONEND CONVERSATION
  • GET CONVERSATION GROUPGET CONVERSATION GROUP
  • MOVE CONVERSATIONMOVE CONVERSATION
  • RECEIVERECEIVE
  • SENDSEND

权限Permissions

需要对函数或架构具有 ALTER 权限。Requires ALTER permission on the function or on the schema. 如果函数指定用户定义类型,则需要对该类型具有 EXECUTE 权限。If the function specifies a user-defined type, requires EXECUTE permission on the type.

另请参阅See Also

CREATE FUNCTION (Transact-SQL) CREATE FUNCTION (Transact-SQL)
DROP FUNCTION (Transact-SQL) DROP FUNCTION (Transact-SQL)
对发布数据库进行架构更改 Make Schema Changes on Publication Databases
EVENTDATA (Transact-SQL)EVENTDATA (Transact-SQL)