CREATE FUNCTION (Transact-SQL)CREATE FUNCTION (Transact-SQL)

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database

SQL ServerSQL ServerAzure SQL 数据库Azure SQL Database 中创建用户定义函数。Creates a user-defined function in SQL ServerSQL Server and Azure SQL 数据库Azure SQL Database. 用户定义函数是接受参数、执行操作(例如复杂计算)并将操作结果以值的形式返回的 Transact-SQLTransact-SQL 或公共语言运行时 (CLR) 例程。A user-defined function is a Transact-SQLTransact-SQL or common language runtime (CLR) routine that accepts parameters, performs an action, such as a complex calculation, and returns the result of that action as a value. 返回值可以是标量(单个)值或表。The return value can either be a scalar (single) value or a table. 使用此语句可以创建可通过以下方式使用的重复使用的例程:Use this statement to create a reusable routine that can be used in these ways:

  • Transact-SQLTransact-SQL 语句(如 SELECT)中In Transact-SQLTransact-SQL statements such as SELECT

  • 在调用该函数的应用程序中In applications calling the function

  • 在另一个用户定义函数的定义中In the definition of another user-defined function

  • 用于参数化视图或改进索引视图的功能To parameterize a view or improve the functionality of an indexed view

  • 用于在表中定义列To define a column in a table

  • 用于为列定义 CHECK 约束To define a CHECK constraint on a column

  • 用于替换存储过程To replace a stored procedure

  • 使用内联函数作为安全策略的筛选器谓词Use an inline function as a filter predicate for a security policy

备注

  • 本主题讨论 .NET Framework CLR 与 SQL ServerSQL Server 的集成。The integration of .NET Framework CLR into SQL ServerSQL Server is discussed in this topic. CLR 集成不适用于 Azure SQL 数据库Azure SQL DatabaseCLR integration does not apply to Azure SQL 数据库Azure SQL Database.
  • 有关 SQL 数据仓库SQL Data Warehouse,请参阅CREATE FUNCTION(SQL 数据仓库)For SQL 数据仓库SQL Data Warehouse, see CREATE FUNCTION (SQL Data Warehouse).

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

语法Syntax

-- Transact-SQL Scalar Function Syntax
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type
 [ = default ] [ READONLY ] }
    [ ,...n ]
  ]
)
RETURNS return_data_type
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN
        function_body
        RETURN scalar_expression
    END
[ ; ]
-- Transact-SQL Inline Table-Valued Function Syntax
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type
    [ = default ] [ READONLY ] }
    [ ,...n ]
  ]
)
RETURNS TABLE
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]
-- Transact-SQL Multi-Statement Table-Valued Function Syntax
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type
    [ = default ] [READONLY] }
    [ ,...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 ]
  | [ INLINE = { ON | OFF }]
}

<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 Function Syntax
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( { @parameter_name [AS] [ type_schema_name. ] parameter_data_type
    [ = default ] }
    [ ,...n ]
)
RETURNS { return_data_type }
    [ WITH <clr_function_option> [ ,...n ] ]
    [ AS ] EXTERNAL NAME <method_specifier>
[ ; ]
-- CLR Table-Valued Function Syntax
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( { @parameter_name [AS] [ type_schema_name. ] parameter_data_type
    [ = default ] }
    [ ,...n ]
)
RETURNS TABLE <clr_table_type_definition>
    [ WITH <clr_function_option> [ ,...n ] ]
    [ ORDER ( <order_clause> ) ]
    [ AS ] EXTERNAL NAME <method_specifier>
[ ; ]
-- CLR Function Clauses
<order_clause> ::=
{
   <column_name_in_clr_table_type_definition>
   [ ASC | DESC ]
} [ ,...n]

<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 ] )
-- In-Memory OLTP: Syntax for natively compiled, scalar user-defined function
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
 ( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type
    [ NULL | NOT NULL ] [ = default ] [ READONLY ] }
    [ ,...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 ]
}

备注

若要查看 SQL Server 2014 及更早版本的 Transact-SQL 语法,请参阅早期版本文档To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

参数Arguments

OR ALTER 适用于:SQL ServerSQL ServerSQL Server 2016 (13.x)SQL Server 2016 (13.x)SP1 及更高版本)和 Azure SQL 数据库Azure SQL DatabaseOR ALTER Applies to: SQL ServerSQL Server ( SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 and later) and Azure SQL 数据库Azure SQL Database

只有在函数已存在时才对其进行有条件地更改。Conditionally alters the function only if it already exists.

备注

SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 CU1 开始,可以使用 CLR 的可选 [OR ALTER] 语法。Optional [OR ALTER] syntax for CLR is available starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 CU1.

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

function_name 是用户定义函数的名称。function_name Is the name of the user-defined function. 函数名称必须符合标识符规则,并且在数据库中以及对其架构来说是唯一的。Function names must comply with the rules for identifiers and must be unique within the database and to its schema.

备注

即使未指定参数,函数名称后也需要加上括号。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 you pass parameters in a stored procedure, user-defined function, or when you declare and set variables in a batch statement. 例如,如果将一个变量定义为 char(3),然后将其值设置为大于三个字符,则数据会被截断为定义的大小,并且 INSERTUPDATE 语句可以成功执行。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 and user-defined table 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, user-defined table types and timestamp data types. Transact-SQLTransact-SQL 函数或 CLR 函数中,不能将非标量类型 cursortable 指定为参数数据类型。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数据库引擎Database Engine会按以下顺序查找 scalar_parameter_data_typeIf type_schema_name is not specified, the 数据库引擎Database Engine looks for the scalar_parameter_data_type in the following order:

  • 包含 SQL ServerSQL Server 系统数据类型名称的架构。The schema that contains the names of SQL ServerSQL 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 the 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 the function is called 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. 但在通过使用 EXECUTE 语句调用标量函数时,DEFAULT 关键字不是必需的。However, the DEFAULT keyword is not required when invoking a scalar function by using the EXECUTE statement.

READONLY 指示不能在函数定义中更新或修改参数。READONLY Indicates that the parameter cannot be updated or modified within the definition of the function. 用户定义的表类型参数 (TVP) 需要 READONLY,而其他任何参数类型都不能使用 READONLY。READONLY is required for user-defined table type parameters (TVPs), and cannot be used for any other parameter type.

return_data_type 是标量用户定义函数的返回值。return_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 the text, ntext, image, and timestamp data types. Transact-SQLTransact-SQL 函数或 CLR 函数中,不能将非标量类型 cursortable 指定为返回数据类型。The nonscalar types, cursor and table, cannot be specified as a return data type in either Transact-SQLTransact-SQL or CLR functions.

function_body 指定一系列定义函数值的 Transact-SQLTransact-SQL 语句,这些语句在一起使用不会产生负面影响(例如修改表)。function_body 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 仅用于标量函数和多语句表值函数 (MSTVF)。function_body is used only in scalar functions and multi-statement table-valued functions (MSTVFs).

在标量函数中,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.

在 MSTVF 中,function_body 是一系列 Transact-SQLTransact-SQL 语句,这些语句将填充 TABLE 返回变量。In MSTVFs, function_body is a series of Transact-SQLTransact-SQL statements that populate a TABLE return variable.

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

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

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

在 MSTVF 中,@return_variable 是一个 TABLE 变量,用于存储和汇总应作为函数值返回的行。In MSTVFs, @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_stmt 定义内联表值函数 (TVF) 返回值的单个 SELECT 语句。select_stmt Is the single SELECT statement that defines the return value of an inline table-valued function (TVF).

ORDER (<order_clause>) 指定从表值函数中返回结果的顺序。ORDER (<order_clause>) Specifies the order in which results are being returned from the table-valued function. 有关详细信息,请参阅本主题后面的“在 CLR 表值函数中使用排序顺序”部分。For more information, see the section, "Using Sort Order in CLR Table-valued Functions", later in this topic.

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

指定创建的函数名称应引用的程序集和方法。Specifies the assembly and method to which the created function name shall refer.

  • assembly_name - 必须与 SELECT * FROM sys.assemblies;name 列中的值匹配。assembly_name - must match a value in the name column of SELECT * FROM sys.assemblies;.

    这是曾在 CREATE ASSEMBLY 语句中使用的名称。This is the name that was used on the CREATE ASSEMBLY statement.

  • class_name - 必须与 SELECT * FROM sys.assembly_modules;assembly_name 列中的值匹配。class_name - must match a value in the assembly_name column of SELECT * FROM sys.assembly_modules;.

    此值通常包含嵌入的句点或圆点。Often the value contains an embedded period or dot. 在这种情况下,Transact-SQL 语法要求将该值置于一对方括号 [] 或一对双引号 "" 内。In such cases the Transact-SQL syntax requires that the value be bounded with a pair of straight brackets [], or with a pair of double quotation marks "".

  • method_name - 必须与 SELECT * FROM sys.assembly_modules;method_name 列中的值匹配。method_name - must match a value in the method_name column of SELECT * FROM sys.assembly_modules;.

    该方法必须是静态方法。The method must be static.

在典型示例中,对于 MyFood.DLL(其中所有类型都位于 MyFood 命名空间中),EXTERNAL NAME 值可以是:MyFood.[MyFood.MyClass].MyStaticMethodIn a typical example, for MyFood.DLL, in which all types are in the MyFood namespace, the EXTERNAL NAME value could be: MyFood.[MyFood.MyClass].MyStaticMethod

备注

  • 默认情况下,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 this 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 ] ) 为 Transact-SQLTransact-SQL 函数定义表数据类型。<table_type_definition> ( { <column_definition> <column_constraint>| <computed_column_definition> } [ <table_constraint> ] [ ,...n ] ) Defines the table data type for a Transact-SQLTransact-SQL function. 表声明包含列定义和列约束(或表约束)。The table declaration includes column definitions and column or table constraints. 表始终放在主文件组中。The table is always put in the primary filegroup.

< clr_table_type_definition > ( { column_name**data_type } [ ,...n ] ) 适用于:SQL ServerSQL ServerSQL Server 2008SQL Server 2008 SP1 及更高版本)和 Azure SQL 数据库Azure SQL Database在某些区域以预览版提供)。< clr_table_type_definition > ( { column_name**data_type } [ ,...n ] ) Applies to: SQL ServerSQL Server ( SQL Server 2008SQL Server 2008 SP1 and later) and Azure SQL 数据库Azure 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. 表始终放在主文件组中。The table is always put in the primary filegroup.

NULL|NOT NULL 仅本机编译的标量用户定义函数支持该参数。NULL|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_COMPILATION 指示用户定义函数是否已本机编译。NATIVE_COMPILATION Indicates whether a user-defined function is natively compiled. 对于本机编译的标量用户定义函数,此参数是必需的。This argument is required for natively compiled, scalar user-defined functions.

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

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

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

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

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

ENCRYPTION 适用于:SQL ServerSQL ServerSQL Server 2008SQL Server 2008SP1 及更高版本)ENCRYPTION Applies to: SQL ServerSQL Server ( SQL Server 2008SQL Server 2008 SP1 and later)

指示数据库引擎Database Engine会将 CREATE FUNCTION 语句的原始文本转换为模糊格式。Indicates that the 数据库引擎Database Engine will convert the original text of the CREATE FUNCTION statement to an obfuscated format. 模糊代码的输出在任何目录视图中都不能直接显示。The output of the obfuscation is not directly visible in any catalog views. 对系统表或数据库文件没有访问权限的用户不能检索模糊文本。Users that have no access to system tables or database files cannot retrieve the obfuscated text. 但是,可以通过 DAC 端口访问系统表的特权用户或直接访问数据库文件的特权用户可以使用此文本。However, the text will be available to privileged users that can either access system tables over the DAC port or directly access database files. 此外,能够向服务器进程附加调试器的用户可在运行时从内存中检索原始过程。Also, users that can attach a debugger to the server process can retrieve the original procedure from memory at runtime. 有关如何访问系统元数据的详细信息,请参阅元数据可见性配置For more information about accessing system metadata, see Metadata Visibility Configuration.

使用此选项可防止将函数作为 SQL ServerSQL Server 复制的一部分发布。Using this option prevents the function from being published as part of SQL ServerSQL Server replication. 不能为 CLR 函数指定此选项。This option cannot be specified for CLR functions.

SCHEMABINDING 指定将函数绑定到其引用的数据库对象。SCHEMABINDING 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 ne 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.

只有满足以下条件时,函数才能绑定到架构:A function can be schema bound only if the following conditions are true:

  • 函数是一个 Transact-SQLTransact-SQL 函数。The function is a Transact-SQLTransact-SQL function.
  • 该函数引用的用户定义函数和视图也绑定到架构。The user-defined functions and views referenced by the function are also schema-bound.
  • 该函数引用的对象是用由两部分组成的名称引用的。The objects referenced by the function are referenced using a two-part name.
  • 该函数及其引用的对象属于同一数据库。The function and the objects it references belong to the same database.
  • 执行 CREATE FUNCTION 语句的用户对该函数引用的数据库对象具有 REFERENCES 权限。The user who executed the CREATE FUNCTION statement has REFERENCES permission on the database objects that the function references.

RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT 指定标量函数的 OnNULLCall 特性 。RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT Specifies the OnNULLCall attribute of a scalar 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> 中指定的 CLR 函数的方法已具有指示 RETURNS NULL ON NULL INPUT 的自定义属性,但 CREATE FUNCTION 语句指示 CALLED ON NULL INPUT,则优先采用 CREATE FUNCTION 语句指示的属性。If the method of a CLR function specified in <method_specifier> already has a custom attribute that indicates RETURNS NULL ON NULL INPUT, but the CREATE FUNCTION statement indicates CALLED ON NULL INPUT, the CREATE 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 that are referenced by the function.

备注

不能为内联表值函数指定 EXECUTE ASEXECUTE AS cannot be specified for inline table-valued functions.

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

INLINE = { ON | OFF } 适用于:SQL Server 2019 和更高版本。INLINE = { ON | OFF } APPLIES TO: SQL Server 2019 and later.

指定是否应内联此标量 UDF。Specifies whether this scalar UDF should be inlined or not. 此子句仅适用于标量用户定义函数。This clause applies only to scalar user-defined functions. INLINE 子句不是强制性的。The INLINE clause is not mandatory. 如果未指定 INLINE 子句,则会基于 UDF 是否可内联而自动设定为 ON/OFF。If INLINE clause is not specified, it is automatically set to ON/OFF based on whether the UDF is inlineable. 如果指定了 INLINE=ON 但发现 UDF 不可内联,则会引发错误。If INLINE=ON is specified but the UDF is found to be non-inlineable, an error will be thrown. 有关详细信息,请参阅标量 UDF 内联For more information, see Scalar UDF Inlining.

< 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_name 是表中列的名称。column_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_type 指定列数据类型。data_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_expression 如果在插入过程中未显式提供值,则指定为列提供的值。DEFAULT 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_name 指定列的排序规则。COLLATE 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 about collations, 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 表值函数指定 COLLATECOLLATE cannot be specified for CLR table-valued functions.

ROWGUIDCOL 指示新列是行的全局唯一标识符列。ROWGUIDCOL Indicates that the new column is a row globally 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.

IDENTITY 指示新列是标识列。IDENTITY 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.

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

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

< column_constraint >::= and < 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 NULL 确定列中是否允许使用空值。NULL | 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 KEY 是一个约束,该约束通过唯一索引来强制指定列的实体完整性。PRIMARY 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.

UNIQUE 一个约束,该约束通过唯一索引为一个或多个指定列提供实体完整性。UNIQUE 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 | NONCLUSTERED 指示为 PRIMARY KEY 或 UNIQUE 约束创建聚集索引还是非聚集索引。CLUSTERED | NONCLUSTERED 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.

CHECK 一个约束,该约束通过限制可输入一列或多列中的可能值来强制实现域完整性。CHECK 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_expression 返回 TRUE 或 FALSE 的逻辑表达式。logical_expression 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_name 是计算列的名称。column_name Is the name of the computed column.

computed_column_expression 是定义计算列的值的表达式。computed_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 = fillfactor 指定一个百分比,指示在创建或更改索引的过程中,应将每个索引页面的叶级填充到什么程度数据库引擎Database EngineFILLFACTOR = fillfactor 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.

最佳实践Best Practices

如果用户定义函数不是使用 SCHEMABINDING 子句创建的,则对基础对象进行的任何更改可能会影响函数定义并在调用函数时可能导致意外结果。If a user-defined function is not created with the SCHEMABINDING clause, changes that are made to underlying objects can affect the definition of the function and produce unexpected results when it is invoked. 我们建议您实现以下方法之一,以便确保函数不会由于对于其基础对象的更改而过期:We recommend that you implement one of the following methods to ensure that the function does not become outdated because of changes to its underlying objects:

  • 创建函数时指定 WITH SCHEMABINDING 子句。Specify the WITH SCHEMABINDING clause when you are creating the function. 这确保除非也修改了函数,否则无法修改在函数定义中引用的对象。This ensures that the objects referenced in the function definition cannot be modified unless the function is also modified.
  • 在修改在函数定义中指定的任何对象后执行 sp_refreshsqlmodule 存储过程。Execute the sp_refreshsqlmodule stored procedure after modifying any object that is specified in the definition of the function.

重要

有关内联表值函数(内联 TVF)和多语句表值函数 (MSTVF) 的详细信息和性能注意事项,请参阅创建用户定义函数(数据库引擎)For more information and performance considerations about inline table-valued functions (inline TVFs) and multi-statement table-valued functions (MSTVFs), see Create User-defined Functions (Database Engine).

数据类型Data Types

如果在 CLR 函数中指定了参数,则这些参数应为 SQL ServerSQL Server 类型,即以前为 scalar_parameter_data_type 定义的类型。If parameters are specified in a CLR function, they should be SQL ServerSQL Server types as defined previously for scalar_parameter_data_type. 有关将 SQL ServerSQL Server 系统数据类型与 CLR 集成数据类型或 .NET Framework.NET Framework 公共语言运行时数据类型进行比较的信息,请参阅映射 CLR 参数数据For information about comparing SQL ServerSQL Server system data types to CLR integration data types or .NET Framework.NET Framework common language runtime data types, see Mapping CLR Parameter Data.

为了使 SQL ServerSQL Server 在类中重载时引用正确方法,<method_specifier> 中指示的方法必须具有下列特征:For SQL ServerSQL Server to reference the correct method when it is overloaded in a class, the method indicated in <method_specifier> must have the following characteristics:

  • 接收 [ ,...n ] 中指定的参数数量。Receive the same number of parameters as specified in [ ,...n ].
  • 通过值而不是引用来接收所有参数。Receive all the parameters by value, not by reference.
  • 使用与 SQL ServerSQL Server 函数中指定的类型兼容的参数类型。Use parameter types that are compatible with those specified in the SQL ServerSQL Server function.

如果 CLR 函数的返回数据类型指定表类型 (RETURNS TABLE),则 <method_specifier> 中方法的返回数据类型应为 IEnumeratorIEnumerable 类型,且假定由函数创建者来实现接口。If the return data type of the CLR function specifies a table type (RETURNS TABLE), the return data type of the method in <method_specifier> should be of type IEnumerator or IEnumerable, and it is assumed that the interface is implemented by the creator of the function. Transact-SQLTransact-SQL 函数不同,CLR 函数不能在 <table_type_definition> 中包含 PRIMARY KEY、UNIQUE 或 CHECK 约束。Unlike Transact-SQLTransact-SQL functions, CLR functions cannot include PRIMARY KEY, UNIQUE, or CHECK constraints in <table_type_definition>. <table_type_definition> 中指定的列数据类型必须与 <method_specifier> 中的方法在执行时返回的结果集中的对应列的类型相匹配。The data types of columns specified in <table_type_definition> must match the types of the corresponding columns of the result set returned by the method in <method_specifier> at execution time. 创建函数时不执行上述类型检查。This type-checking is not performed at the time the function is created.

有关如何对 CLR 函数编程的详细信息,请参阅 CLR 用户定义函数For more information about how to program CLR functions, see CLR User-Defined Functions.

一般备注General Remarks

可在使用标量表达式的位置调用标量函数。Scalar functions can be invoked where scalar expressions are used. 这包括计算列和 CHECK 约束定义。This includes computed columns and CHECK constraint definitions. 也可以使用 EXECUTE 语句执行标量函数。Scalar functions can also be executed by using the EXECUTE statement. 必须使用至少由两部分组成名称的函数来调用标量函数 ()。Scalar functions must be invoked by using at least the two-part name of the function (.). 有关多部分名称的详细信息,请参阅 Transact-SQL 语法约定 (Transact-SQL)For more information about multipart names, see Transact-SQL Syntax Conventions (Transact-SQL). 在允许表表达式的情况下,可在 SELECTINSERTUPDATEDELETE 语句的 FROM 子句中调用表值函数。Table-valued functions can be invoked where table expressions are allowed in the FROM clause of SELECT, INSERT, UPDATE, or DELETE statements. 有关详细信息,请参阅执行用户定义函数For more information, see Execute User-defined Functions.

互操作性Interoperability

下列语句在函数内有效:The following statements are valid in a function:

  • 赋值语句。Assignment statements.
  • TRY...CATCH 语句以外的控制流语句。Control-of-Flow statements except TRY...CATCH statements.
  • 定义局部数据变量和局部游标的 DECLARE 语句。DECLARE statements defining local data variables and local cursors.
  • SELECT 语句,其中的选择列表包含为局部变量分配值的表达式。SELECT statements that contain select lists with expressions that assign values to local variables.
  • 游标操作,该操作引用在函数中声明、打开、关闭和释放的局部游标。Cursor operations referencing local cursors that are declared, opened, closed, and deallocated in the function. 只允许使用以 INTO 子句向局部变量赋值的 FETCH 语句;不允许使用将数据返回到客户端的 FETCH 语句。Only FETCH statements that assign values to local variables using the INTO clause are allowed; FETCH statements that return data to the client are not allowed.
  • 修改本地表变量的 INSERTUPDATEDELETE 语句。INSERT, UPDATE, and DELETE statements modifying local table variables.
  • 调用扩展存储过程的 EXECUTE 语句。EXECUTE statements calling extended stored procedures.

有关详细信息,请参阅创建用户定义函数(数据库引擎)For more information, see Create User-defined Functions (Database Engine).

计算列互操作性Computed Column Interoperability

函数具有下列属性。Functions have the following properties. 这些属性的值确定了函数是否可用于持久化计算列或索引计算列。The values of these properties determine whether functions can be used in computed columns that can be persisted or indexed.

propertiesProperty 说明Description 说明Notes
IsDeterministicIsDeterministic 函数是确定性函数还是不确定性函数。Function is deterministic or nondeterministic. 确定性函数中允许本地数据访问。Local data access is allowed in deterministic functions. 例如,如果每次使用一组特定输入值和相同数据库状态调用函数时,函数都返回相同结果,则该函数将被标记为确定性函数。For example, functions that always return the same result any time they are called by using a specific set of input values and with the same state of the database would be labeled deterministic.
IsPreciseIsPrecise 函数是精确函数还是不精确函数。Function is precise or imprecise. 不精确函数包含浮点运算之类的运算。Imprecise functions contain operations such as floating point operations.
IsSystemVerifiedIsSystemVerified SQL ServerSQL Server 可验证函数的精度和确定性属性。The precision and determinism properties of the function can be verified by SQL ServerSQL Server.
SystemDataAccessSystemDataAccess 函数可以访问 SQL ServerSQL Server 的本地实例中的系统数据(系统目录或虚拟系统表)。Function accesses system data (system catalogs or virtual system tables) in the local instance of SQL ServerSQL Server.
UserDataAccessUserDataAccess 函数可以访问 SQL ServerSQL Server 的本地实例中的用户数据。Function accesses user data in the local instance of SQL ServerSQL Server. 包含用户定义表和临时表,但不包含表变量。Includes user-defined tables and temp tables, but not table variables.

Transact-SQLTransact-SQL 函数的精度和确定性属性由 SQL ServerSQL Server 自动确定。The precision and determinism properties of Transact-SQLTransact-SQL functions are determined automatically by SQL ServerSQL Server. CLR 函数的数据访问权限和确定性属性可由用户指定。The data access and determinism properties of CLR functions can be specified by the user. 有关详细信息,请参阅 CLR 集成自定义属性概述For more information, see Overview of CLR Integration Custom Attributes.

若要显示这些属性的当前值,请使用 OBJECTPROPERTYEXTo display the current values for these properties, use OBJECTPROPERTYEX.

重要

必须使用确定性的 SCHEMABINDING 创建函数。Functions must be created with SCHEMABINDING to be deterministic.

如果用户定义函数具有下列属性值,则可以在索引中使用调用用户定义函数的计算列:A computed column that invokes a user-defined function can be used in an index when the user-defined function has the following property values:

  • IsDeterministic = trueIsDeterministic = true
  • IsSystemVerified = true(计算列是持久性计算列时除外)IsSystemVerified = true (unless the computed column is persisted)
  • UserDataAccess = falseUserDataAccess = false
  • SystemDataAccess = falseSystemDataAccess = false

有关详细信息,请参阅 计算列上的索引For more information, see Indexes on Computed Columns.

从函数中调用扩展存储过程Calling Extended Stored Procedures from Functions

如果在函数中调用扩展存储过程,则该过程不能向客户端返回结果集。The extended stored procedure, when it is called from inside a function, cannot return result sets to the client. 向客户端返回结果集的任何 ODS API 都将返回 FAIL。Any ODS APIs that return result sets to the client will return FAIL. 扩展存储过程可以连接回 SQL ServerSQL Server 的实例;不过,该过程不应尝试与调用扩展存储过程的函数同时联接到同一事务。The extended stored procedure could connect back to an instance of SQL ServerSQL Server; however, it should not try to join the same transaction as the function that invoked the extended stored procedure.

与通过批处理或存储过程进行调用相似,扩展存储过程在运行 SQL ServerSQL Server 的 Windows 安全帐户的上下文中执行。Similar to invocations from a batch or stored procedure, the extended stored procedure will be executed in the context of the Windows security account under which SQL ServerSQL Server is running. 存储过程的所有者在授予用户 EXECUTE 权限时应考虑这一点。The owner of the stored procedure should consider this when giving EXECUTE permission on it to users.

限制和局限Limitations and Restrictions

用户定义函数不能用于执行修改数据库状态的操作。User-defined functions cannot be used to perform actions that modify the database state.

用户定义函数不能包含将表作为其目标的 OUTPUT INTO 子句。User-defined functions cannot contain an OUTPUT INTO clause that has a table as its target.

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

  • BEGIN DIALOG CONVERSATION
  • END CONVERSATION
  • GET CONVERSATION GROUP
  • MOVE CONVERSATION
  • RECEIVE
  • SEND

用户定义函数可以嵌套;也就是说,用户定义函数可相互调用。User-defined functions can be nested; that is, one user-defined function can call another. 被调用函数开始执行时,嵌套级别将增加;被调用函数执行结束后,嵌套级别将减少。The nesting level is incremented when the called function starts execution, and decremented when the called function finishes execution. 用户定义函数的嵌套级别最多可达 32 级。User-defined functions can be nested up to 32 levels. 如果超出最大嵌套级别数,整个调用函数链将失败。Exceeding the maximum levels of nesting causes the whole calling function chain to fail. Transact-SQLTransact-SQL 用户定义函数对托管代码的任何引用都将根据 32 级嵌套限制计入一个级别。Any reference to managed code from a Transact-SQLTransact-SQL user-defined function counts as one level against the 32-level nesting limit. 从托管代码内部调用的方法不根据此限制进行计数。Methods invoked from within managed code do not count against this limit.

在 CLR 表值函数中使用排序顺序Using Sort Order in CLR Table-valued Functions

在 CLR 表值函数中使用 ORDER 子句时请遵循以下准则:When using the ORDER clause in CLR table-valued functions, follow these guidelines:

  • 必须确保始终按指定的顺序对结果进行排序。You must ensure that results are always ordered in the specified order. 如果没有按指定的顺序对结果进行排序,则在执行查询时 SQL ServerSQL Server 将生成一条错误消息。If the results are not in the specified order, SQL ServerSQL Server will generate an error message when the query is executed.

  • 如果指定了 ORDER 子句,则必须根据列(显式或隐式)的排序规则对表值函数的输出进行排序。If an ORDER clause is specified, the output of the table-valued function must be sorted according to the collation of the column (explicit or implicit). 例如,如果列排序规则为中文(在表值函数的 DDL 中指定或从数据库排序规则中获取),则必须根据中文排序规则对返回的结果进行排序。For example, if the column collation is Chinese (either specified in the DDL for the table-valued function or obtained from the database collation), the returned results must be sorted according to Chinese sorting rules.

  • 如果指定了 ORDER 子句,则在返回结果时始终由 SQL ServerSQL Server 对其验证,而不管查询处理器是否会使用该子句执行进一步的优化。The ORDER clause, if specified, is always verified by SQL ServerSQL Server while returning results, whether or not it is used by the query processor to perform further optimizations. 只有知道 ORDER 子句对查询处理器有用时才使用它。Only use the ORDER clause if you know it is useful to the query processor.

  • 在以下情况下,SQL ServerSQL Server 查询处理器将自动使用 ORDER 子句:The SQL ServerSQL Server query processor takes advantage of the ORDER clause automatically in following cases:

    • “插入”查询,其中 ORDER 子句与索引兼容。Insert queries where the ORDER clause is compatible with an index.
    • ORDER 子句兼容的 ORDER BY 子句。ORDER BY clauses that are compatible with the ORDER clause.
    • 聚合,其中 GROUP BYORDER 子句兼容。Aggregates, where GROUP BY is compatible with ORDER clause.
    • DISTINCT 聚合,其中不同的列与 ORDER 子句兼容。DISTINCT aggregates where the distinct columns are compatible with the ORDER clause.

除非在查询中还指定了 ORDER BY,否则 ORDER 子句不保证在执行 SELECT 查询时得到有序结果。The ORDER clause does not guarantee ordered results when a SELECT query is executed, unless ORDER BY is also specified in the query. 有关如何查询表值函数排序顺序中所包含的列的信息,请参阅 sys.function_order_columns (Transact-SQL)See sys.function_order_columns (Transact-SQL) for information on how to query for columns included in the sort-order for table-valued functions.

元数据Metadata

下表列出可用于返回与用户定义函数有关的元数据的系统目录视图。The following table lists the system catalog views that you can use to return metadata about user-defined functions.

系统视图System View 说明Description
sys.sql_modulessys.sql_modules 请参阅下面“示例”部分中的“示例 E”。See example E in the Examples section below.
sys.assembly_modulessys.assembly_modules 显示 CLR 用户定义函数的有关信息。Displays information about CLR user-defined functions.
sys.parameterssys.parameters 显示用户定义函数中定义的参数的有关信息。Displays information about the parameters defined in user-defined functions.
sys.sql_expression_dependenciessys.sql_expression_dependencies 显示函数所引用的基础对象。Displays the underlying objects referenced by a function.

权限Permissions

需要在数据库中具有 CREATE FUNCTION 权限,并对创建函数时所在的架构具有 ALTER 权限。Requires CREATE FUNCTION permission in the database and ALTER permission on the schema in which the function is being created. 如果函数指定用户定义类型,则需要对该类型具有 EXECUTE 权限。If the function specifies a user-defined type, requires EXECUTE permission on the type.

示例Examples

备注

有关 UDF 的更多示例和性能注意事项,请参阅创建用户定义函数(数据库引擎)For more examples and performance considerations about UDFs, see Create User-defined Functions (Database Engine).

A.A. 使用计算 ISO 周的标量值用户定义函数Using a scalar-valued user-defined function that calculates the ISO week

下面的示例将创建用户定义函数 ISOweekThe following example creates the user-defined function ISOweek. 此函数使用日期参数来计算 ISO 周数。This function takes a date argument and calculates the ISO week number. 要使此函数能正确计算,必须在调用该函数前调用 SET DATEFIRST 1For this function to calculate correctly, SET DATEFIRST 1 must be invoked before the function is called.

该示例还演示了如何使用 EXECUTE AS 子句指定可执行存储过程的安全上下文。The example also shows using the EXECUTE AS clause to specify the security context in which a stored procedure can be executed. 在该示例中,CALLER 选项指定该过程将在调用该过程的用户的上下文中执行。In the example, the option CALLER specifies that the procedure will be executed in the context of the user that calls it. 还可以指定 SELFOWNER 和 user_name 等其他选项。The other options that you can specify are SELF, OWNER, and user_name.

下面是函数调用。Here is the function call. 请注意,DATEFIRST 设置为 1Notice that DATEFIRST is set to 1.

CREATE FUNCTION dbo.ISOweek (@DATE datetime)
RETURNS int
WITH EXECUTE AS CALLER
AS
BEGIN
    DECLARE @ISOweek int;
    SET @ISOweek= DATEPART(wk,@DATE)+1
        -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104');
--Special cases: Jan 1-3 may belong to the previous year
    IF (@ISOweek=0)
        SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1
            AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1;
--Special case: Dec 29-31 may belong to the next year
    IF ((DATEPART(mm,@DATE)=12) AND
        ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
    SET @ISOweek=1;
    RETURN(@ISOweek);
END;
GO
SET DATEFIRST 1;
SELECT dbo.ISOweek(CONVERT(DATETIME,'12/26/2004',101)) AS 'ISO Week';

下面是结果集:Here is the result set.

ISO Week
----------------
52

B.B. 创建内联表值函数Creating an inline table-valued function

下面的示例在 AdventureWorks2012AdventureWorks2012 数据库中返回内联表值函数。The following example returns an inline table-valued function in the AdventureWorks2012AdventureWorks2012 database. 对于销售给商店的每个产品,该函数返回三列,分别为 ProductIDName 以及各个商店年初至今总数的累计 YTD TotalIt returns three columns ProductID, Name and the aggregate of year-to-date totals by store as YTD Total for each product sold to the store.

CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN
(
    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'
    FROM Production.Product AS P
    JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
    JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
    JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID
    WHERE C.StoreID = @storeid
    GROUP BY P.ProductID, P.Name
);
GO

若要调用该函数,请运行此查询。To invoke the function, run this query.

SELECT * FROM Sales.ufn_SalesByStore (602);

C.C. 创建多语句表值函数Creating a multi-statement table-valued function

下面的示例在 AdventureWorks2012 数据库中创建表值函数 fn_FindReports(InEmpID)The following example creates the table-valued function fn_FindReports(InEmpID) in the AdventureWorks2012 database. 如果提供一个有效雇员 ID,该函数将返回一个表,该表对应于直接或间接向该雇员报告的所有雇员。When supplied with a valid employee ID, the function returns a table that corresponds to all the employees that report to the employee either directly or indirectly. 该函数使用递归公用表表达式 (CTE) 来生成雇员的层次结构列表。The function uses a recursive common table expression (CTE) to produce the hierarchical list of employees. 有关递归 CTE 的详细信息,请参阅 WITH common_table_expression (Transact-SQL)For more information about recursive CTEs, see WITH common_table_expression (Transact-SQL).

CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER)
RETURNS @retFindReports TABLE
(
    EmployeeID int primary key NOT NULL,
    FirstName nvarchar(255) NOT NULL,
    LastName nvarchar(255) NOT NULL,
    JobTitle nvarchar(50) NOT NULL,
    RecursionLevel int NOT NULL
)
--Returns a result set that lists all the employees who report to the
--specific employee directly or indirectly.*/
AS
BEGIN
WITH EMP_cte(EmployeeID, OrganizationNode, FirstName, LastName, JobTitle, RecursionLevel) -- CTE name and columns
    AS (
        -- Get the initial list of Employees for Manager n
        SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, 0
        FROM HumanResources.Employee e
INNER JOIN Person.Person p
ON p.BusinessEntityID = e.BusinessEntityID
        WHERE e.BusinessEntityID = @InEmpID
        UNION ALL
        -- Join recursive member to anchor
        SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, RecursionLevel + 1
        FROM HumanResources.Employee e
            INNER JOIN EMP_cte
            ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode
INNER JOIN Person.Person p
ON p.BusinessEntityID = e.BusinessEntityID
        )
-- copy the required columns to the result of the function
    INSERT @retFindReports
    SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel
    FROM EMP_cte
    RETURN
END;
GO
-- Example invocation
SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel
FROM dbo.ufn_FindReports(1);

GO

D.D. 创建 CLR 函数Creating a CLR function

此示例会创建 CLR 函数 len_sThe example creates CLR function len_s. 在创建该函数之前,程序集 SurrogateStringFunction.dll 已在本地数据库中注册。Before the function is created, the assembly SurrogateStringFunction.dll is registered in the local database.

适用对象SQL ServerSQL ServerSQL Server 2008SQL Server 2008SP1 及更高版本)Applies to: SQL ServerSQL Server ( SQL Server 2008SQL Server 2008 SP1 and later)

DECLARE @SamplesPath nvarchar(1024);
-- You may have to modify the value of this variable if you have
-- installed the sample in a location other than the default location.
SELECT @SamplesPath = REPLACE
    (  physical_name
     , 'Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\master.mdf'
     , 'Microsoft SQL Server\130\Samples\Engine\Programmability\CLR\'
    )
  FROM master.sys.database_files
  WHERE name = 'master';

CREATE ASSEMBLY [SurrogateStringFunction]
FROM @SamplesPath + 'StringManipulate\CS\StringManipulate\bin\debug\SurrogateStringFunction.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

CREATE FUNCTION [dbo].[len_s] (@str nvarchar(4000))
RETURNS bigint
AS EXTERNAL NAME [SurrogateStringFunction].[Microsoft.Samples.SqlServer.SurrogateStringFunction].[LenS];
GO

有关如何创建 CLR 表值函数的示例,请参阅 CLR 表值函数For an example of how to create a CLR table-valued function, see CLR Table-Valued Functions.

E.E. 显示用户定义函数的定义Displaying the definition of user-defined functions

SELECT definition, type
FROM sys.sql_modules AS m
JOIN sys.objects AS o ON m.object_id = o.object_id
    AND type IN ('FN', 'IF', 'TF');
GO

不能使用 sys.sql_modules 查看使用 ENCRYPTION 选项创建的函数定义;不过,可显示有关加密函数的其他信息。The definition of functions created by using the ENCRYPTION option cannot be viewed by using sys.sql_modules; however, other information about the encrypted functions is displayed.

另请参阅See Also