CREATE FUNCTION (Transact-SQL)

创建用户定义函数。这是一个已保存 Transact-SQL 或公共语言运行时 (CLR) 例程，该例程可返回一个值。用户定义函数为标量值函数或表值函数。如果 RETURNS 子句指定了一种标量数据类型，则函数为标量值函数。可以使用多条 Transact-SQL 语句定义标量值函数。 如果 RETURNS 子句指定 TABLE，则函数为表值函数。根据函数主体的定义方式，表值函数可分为内联函数或多语句函数。有关详细信息，请参阅表值用户定义函数。

Transact-SQL 语法约定

语法

Scalar Functions
CREATE 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
[ ; ]

Inline Table-Valued Functions
CREATE 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 [ ) ]
[ ; ]

Multistatement Table-valued Functions
CREATE 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
[ ; ]

CLR Scalar Functions
CREATE 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 Functions
CREATE 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>
[ ; ]

<order_clause> ::= 
{
   <column_name_in_clr_table_type_definition>
   [ ASC | DESC ] 
} [ ,...n] 

Method Specifier<method_specifier>::=    assembly_name.class_name.method_nameFunction Options<function_option>::= 
{
    [ ENCRYPTION ]
  | [ SCHEMABINDING ]
  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
  | [ EXECUTE_AS_Clause ]
}

<clr_function_option>::=
}
    [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
  | [ EXECUTE_AS_Clause ]
}

Table Type Definitions<table_type_definition>:: = ( { <column_definition> <column_constraint> 
  | <computed_column_definition> } 
    [ <table_constraint> ] [ ,...n ]
) <clr_table_type_definition>::= ( { column_name data_type } [ ,...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 } 
}

参数

• schema_name
  用户定义函数所属的架构的名称。

• function_name
  用户定义函数的名称。函数名称必须符合有关标识符的规则，并且在数据库中以及对其架构来说是唯一的。

  注意
  即使未指定参数，函数名称后也需要加上括号。

• @parameter\_name
  用户定义函数中的参数。可声明一个或多个参数。

  一个函数最多可以有 2,100 个参数。执行函数时，如果未定义参数的默认值，则用户必须提供每个已声明参数的值。

  通过将 at 符号 (@) 用作第一个字符来指定参数名称。参数名称必须符合标识符规则。参数是对应于函数的局部参数；其他函数中可使用相同的参数名称。参数只能代替常量，而不能用于代替表名、列名或其他数据库对象的名称。

  注意
  在传递存储过程或用户定义函数中的参数时，或在声明和设置批语句中的变量时，不会遵守 ANSI_WARNINGS。例如，如果将变量定义为 char(3) 类型，然后为它设置一个大于三个字符的值，则该数据会被截断为定义的大小，并且 INSERT 或 UPDATE 语句可以成功执行。

• [ type_schema_name.] parameter_data_type
  参数的数据类型及其所属的架构，后者为可选项。对于 Transact-SQL 函数，允许使用除 timestamp 数据类型之外的所有数据类型（包括 CLR 用户定义类型和用户定义表类型）。对于 CLR 函数，允许使用除 text、ntext、image、用户定义表类型和 timestamp 数据类型之外的所有数据类型（包括 CLR 用户定义类型）。不能将非标量类型 cursor 和 table 指定为 Transact-SQL 函数或 CLR 函数中的参数数据类型。

  如果未指定 type_schema_name，则数据库引擎将按下面的顺序查找 scalar_parameter_data_type：

  • 包含 SQL Server 系统数据类型名称的架构。

  • 当前数据库中当前用户的默认架构。

  • 当前数据库中的 dbo 架构。

• [ =default ]
  参数的默认值。如果定义了 default 值，则无需指定此参数的值即可执行函数。

  注意
  可以为除 varchar(max) 和 varbinary(max) 数据类型之外的 CLR 函数指定默认的参数值。

  如果函数的参数有默认值，则调用该函数以检索默认值时，必须指定关键字 DEFAULT。此行为与在存储过程中使用具有默认值的参数不同，在后一种情况下，不提供参数同样意味着使用默认值。

• READONLY
  指示不能在函数定义中更新或修改参数。如果参数类型为用户定义的表类型，则应指定 READONLY。

• return_data_type
  标量用户定义函数的返回值。对于 Transact-SQL 函数，可以使用除 timestamp 数据类型之外的所有数据类型（包括 CLR 用户定义类型）。对于 CLR 函数，允许使用除 text、ntext、image 和 timestamp 数据类型之外的所有数据类型（包括 CLR 用户定义类型）。不能将非标量类型 cursor 和 table 指定为 Transact-SQL 函数或 CLR 函数中的返回数据类型。 

• function_body
  指定一系列定义函数值的 Transact-SQL 语句，这些语句在一起使用不会产生负面影响（例如修改表）。function_body 仅用于标量函数和多语句表值函数。

  在标量函数中，function_body 是一系列 Transact-SQL 语句，这些语句一起使用的计算结果为标量值。

  在多语句表值函数中，function_body 是一系列 Transact-SQL 语句，这些语句将填充 TABLE 返回变量。

• scalar_expression
  指定标量函数返回的标量值。

• TABLE
  指定表值函数的返回值为表。只有常量和 @local\_variables 可以传递到表值函数。

  在内联表值函数中，TABLE 返回值是通过单个 SELECT 语句定义的。内联函数没有关联的返回变量。

  在多语句表值函数中，@return_variable 是 TABLE 变量，用于存储和汇总应作为函数值返回的行。只能将 @return\_variable 指定用于 Transact-SQL 函数，而不能用于 CLR 函数。

• select_stmt
  定义内联表值函数返回值的单个 SELECT 语句。

• ORDER (<order_clause>)
  指定从表值函数中返回结果的顺序。有关详细信息，请参阅本主题后面的“有关使用排序顺序的指南”部分。

• EXTERNAL NAME <method_specifier> assembly_name.class_name.method_name
  指定将程序集与函数绑定的方法。assembly_name 必须与 SQL Server 中当前数据库内具有可见性的现有程序集匹配。class_name 必须是有效的 SQL Server 标识符，并且必须作为类存在于程序集中。如果类具有以命名空间限定的名称，该名称使用句点 (.) 来分隔命名空间的各部分，则必须使用方括号 ([]) 或引号 ("") 分隔类名称。method_name 必须是有效的 SQL Server 标识符，并且必须作为静态方法存在于指定类中。

  注意
  默认情况下，SQL Server 不能执行 CLR 代码。可以创建、修改和删除引用公共语言运行时模块的数据库对象；不过，只有在启用 clr enabled 选项之后，才能在 SQL Server 中执行这些引用。若要启用此选项，请使用 sp_configure。

• <table_type_definition> ( { <column_definition> <column_constraint>   | <computed_column_definition> }   [ <table_constraint> ] [ ,...n] )
  定义 Transact-SQL 函数的表数据类型。表声明包含列定义和列约束（或表约束）。表始终放在主文件组中。

• < clr_table_type_definition > ( { column_namedata_type } [ ,...n] )
  定义 CLR 函数的表数据类型。表声明仅包含列名称和数据类型。表始终放在主文件组中。

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

指定函数将具有以下一个或多个选项：

• ENCRYPTION
  指示数据库引擎会将 CREATE FUNCTION 语句的原始文本转换为模糊格式。模糊代码的输出在任何目录视图中都不能直接显示。对系统表或数据库文件没有访问权限的用户不能检索模糊文本。但是，可以通过 DAC 端口访问系统表的特权用户或直接访问数据文件的特权用户可以使用此文本。此外，能够向服务器进程附加调试器的用户可在运行时从内存中检索原始过程。有关访问系统元数据的详细信息，请参阅 元数据可见性配置。

  使用此选项可防止将函数作为 SQL Server 复制的一部分发布。不能为 CLR 函数指定此选项。

• SCHEMABINDING
  指定将函数绑定到其引用的数据库对象。如果指定了 SCHEMABINDING，则不能按照将影响函数定义的方式修改基对象。必须首先修改或删除函数定义本身，才能删除将要修改的对象的依赖关系。

  只有发生下列操作之一时，才会删除函数与其引用对象的绑定：

  • 删除函数。

  • 在未指定 SCHEMABINDING 选项的情况下，使用 ALTER 语句修改函数。

  只有满足以下条件时，函数才能绑定到架构：

  • 函数是一个 Transact-SQL 函数。

  • 该函数引用的用户定义函数和视图也绑定到架构。

  • 该函数引用的对象是用由两部分组成的名称引用的。

  • 该函数及其引用的对象属于同一数据库。

  • 执行 CREATE FUNCTION 语句的用户对该函数引用的数据库对象具有 REFERENCES 权限。

• RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT
  指定标量值函数的 OnNULLCall 属性。如果未指定，则默认为 CALLED ON NULL INPUT。这意味着即使传递的参数为 NULL，也将执行函数体。

  如果在 CLR 函数中指定了 RETURNS NULL ON NULL INPUT，它指示当 SQL Server 接收到的任何一个参数为 NULL 时，它可以返回 NULL，而无需实际调用函数体。如果 <method_specifier> 中指定的 CLR 函数的方法已具有指示 RETURNS NULL ON NULL INPUT 的自定义属性，但 CREATE FUNCTION 语句指示 CALLED ON NULL INPUT，则优先采用 CREATE FUNCTION 语句指示的属性。不能为 CLR 表值函数指定 OnNULLCall 属性。

• EXECUTE AS 子句
  指定用于执行用户定义函数的安全上下文。所以，您可以控制 SQL Server 使用哪一个用户帐户来验证针对该函数引用的任何数据库对象的权限。

  注意
  不能为内联用户定义函数指定 EXECUTE AS。

  有关详细信息，请参阅 EXECUTE AS 子句 (Transact-SQL)。

< column_definition >::=

定义表数据类型。表声明包含列定义和约束。对于 CLR 函数，只能指定 column_name 和 data_type。

• column_name
  表中列的名称。列名称必须符合标识符规则，并且在表中必须是唯一的。column_name 可以由 1 至 128 个字符组成。

• data_type
  指定列数据类型。对于 Transact-SQL 函数，可以使用除 timestamp 之外的所有数据类型（包括 CLR 用户定义类型）。对于 CLR 函数，可以使用除 text、ntext、image、char、varchar、varchar(max) 和 timestamp 之外的所有数据类型（包括 CLR 用户定义类型）。在 Transact-SQL 或 CLR 函数中，非标量类型 cursor 不能指定为列数据类型。

• DEFAULT constant_expression
  指定当插入过程中没有显式提供值时为列提供的值。constant_expression 可以是常量、NULL 或系统函数值。DEFAULT 定义可以应用于除具有 IDENTITY 属性的列之外的任何列。不能为 CLR 表值函数指定 DEFAULT。

• COLLATE collation_name
  指定列的排序规则。如果未指定，则为该列分配数据库的默认排序规则。排序规则名称既可以是 Windows 排序规则名称，也可以是 SQL 排序规则名称。有关排序规则的列表及详细信息，请参阅 Windows 排序规则名称 (Transact-SQL)和 SQL Server 排序规则名称 (Transact-SQL)。

  COLLATE 子句只能用来更改数据类型为 char、varchar、nchar 和 nvarchar 的列的排序规则。

  不能为 CLR 表值函数指定 COLLATE。

• ROWGUIDCOL
  指示新列是行的全局唯一标识符列。对于每个表，只能将其中的一个 uniqueidentifier 列指定为 ROWGUIDCOL 列。ROWGUIDCOL 属性只能分配给 uniqueidentifier 列。

  ROWGUIDCOL 属性并不强制列中所存储值的唯一性。该属性也不会为插入表的新行自动生成值。若要为每列生成唯一值，请在 INSERT 语句中使用 NEWID 函数。可以指定默认值；但是，不能将 NEWID 指定为默认值。

• IDENTITY
  指示新列是标识列。在为表添加新行时，SQL Server 将为该列提供唯一的增量值。标识列通常与 PRIMARY KEY 约束一起使用，作为表的唯一行标识符。可以将 IDENTITY 属性分配给 tinyint、smallint、int、bigint、decimal(p,0) 或 numeric(p,0) 列。每个表只能创建一个标识列。不能对标识列使用绑定默认值和 DEFAULT 约束。必须同时指定 seed 和 increment，或者二者都不指定。如果二者都未指定，则取默认值 (1,1)。

  不能为 CLR 表值函数指定 IDENTITY。

  • seed
    要分配给表中第一行的整数值。

  • increment
    要加到表中后续行的 seed 值上的整数值。

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

为指定列或表定义约束。对于 CLR 函数，允许的唯一约束类型为 NULL。不允许命名约束。

• NULL | NOT NULL
  确定列中是否允许空值。严格来讲，NULL 不是约束，但可以像指定 NOT NULL 那样指定它。不能为 CLR 表值函数指定 NOT NULL。

• PRIMARY KEY
  一个约束，该约束通过唯一索引来强制指定列的实体完整性。在表值用户定义函数中，只能对每个表中的一列创建 PRIMARY KEY 约束。不能为 CLR 表值函数指定 PRIMARY KEY。

• UNIQUE
  一个约束，该约束通过唯一索引为一个或多个指定列提供实体完整性。一个表可以有多个 UNIQUE 约束。不能为 CLR 表值函数指定 UNIQUE。

• CLUSTERED | NONCLUSTERED
  指示为 PRIMARY KEY 或 UNIQUE 约束创建聚集索引还是非聚集索引。PRIMARY KEY 约束使用 CLUSTERED，而 UNIQUE 约束使用 NONCLUSTERED。

  只能为一个约束指定 CLUSTERED。如果为 UNIQUE 约束指定了 CLUSTERED，并且指定了 PRIMARY KEY 约束，则 PRIMARY KEY 使用 NONCLUSTERED。

  不能为 CLR 表值函数指定 CLUSTERED 和 NONCLUSTERED。

• CHECK
  一个约束，该约束通过限制可输入一列或多列中的可能值来强制实现域完整性。不能为 CLR 表值函数指定 CHECK 约束。

  • logical_expression
    返回 TRUE 或 FALSE 的逻辑表达式。

<computed_column_definition>::=

指定计算列。有关计算列的详细信息，请参阅 CREATE TABLE (Transact-SQL)。

• column_name
  计算列的名称。

• computed_column_expression
  定义计算列的值的表达式。

<index_option>::=

为 PRIMARY KEY 或 UNIQUE 索引指定索引选项。有关索引选项的详细信息，请参阅 CREATE INDEX (Transact-SQL)。

• PAD_INDEX = { ON | OFF }
  指定索引填充。默认值为 OFF。

• FILLFACTOR = fillfactor
  指定一个百分比，指示在创建或更改索引期间，数据库引擎对各索引页的叶级填充的程度。fillfactor 必须为介于 1 至 100 之间的整数值。默认值为 0。

• IGNORE_DUP_KEY = { ON | OFF }
  指定在插入操作尝试向唯一索引插入重复键值时的错误响应。IGNORE_DUP_KEY 选项仅适用于创建或重新生成索引后发生的插入操作。默认值为 OFF。

• STATISTICS_NORECOMPUTE = { ON | OFF }
  指定是否重新计算分布统计信息。默认值为 OFF。

• ALLOW_ROW_LOCKS = { ON | OFF }
  指定是否允许行锁。默认值为 ON。

• ALLOW_PAGE_LOCKS = { ON | OFF }
  指定是否允许使用页锁。默认值为 ON。

最佳实践

如果用户定义函数不是使用 SCHEMABINDING 子句创建的，则对基础对象进行的任何更改可能会影响函数定义并在调用函数时可能导致意外结果。我们建议您实现以下方法之一，以便确保函数不会由于对于其基础对象的更改而过期：

• 在您创建函数时指定 WITH SCHEMABINDING 子句。这确保除非也修改了函数，否则无法修改在函数定义中引用的对象。

• 在修改在函数定义中指定的任何对象后执行 sp_refreshsqlmodule 存储过程。

数据类型

如果在 CLR 函数中指定了参数，则这些参数应为 SQL Server 类型，即以前为 scalar_parameter_data_type 定义的类型。有关将 SQL Server 系统数据类型与 CLR 集成数据类型或 .NET Framework 公共语言运行时数据类型进行比较的信息，请参阅 映射 CLR 参数数据。

为了使 SQL Server 在类中重载时引用正确方法，<method_specifier> 中指示的方法必须具有下列特征：

• 接收 [ ,...n ] 中指定的参数数量。].

• 通过值而不是引用来接收所有参数。

• 使用与 SQL Server 函数中指定的类型兼容的参数类型。

如果 CLR 函数的返回数据类型指定表类型 (RETURNS TABLE)，则 <method_specifier> 中方法的返回数据类型应为 IEnumerator 或 IEnumerable 类型，且假定由函数创建者来实现接口。与 Transact-SQL 函数不同，CLR 函数不能在 <table_type_definition> 中包含 PRIMARY KEY、UNIQUE 或 CHECK 约束。<table_type_definition> 中指定的列数据类型必须与 <method_specifier> 中的方法在执行时返回的结果集中的对应列的类型相匹配。创建函数时不执行上述类型检查。

有关如何对 CLR 函数编程的详细信息，请参阅 CLR 用户定义函数。

一般备注

可在使用标量表达式的位置调用标量值函数。这包括计算列和 CHECK 约束定义。也可以使用 EXECUTE 语句执行标量值函数。必须使用至少由两部分组成名称的函数来调用标量值函数。有关多部分名称的详细信息，请参阅 Transact-SQL 语法约定 (Transact-SQL)。在允许表表达式的情况下，可在 SELECT、INSERT、UPDATE 或 DELETE 语句的 FROM 子句中调用表值函数。有关详细信息，请参阅执行用户定义函数（数据库引擎）。

互操作性

下列语句在函数内有效：

• 赋值语句。

• TRY...CATCH 语句以外的流控制语句。

• 定义局部数据变量和局部游标的 DECLARE 语句。

• SELECT 语句，其中的选择列表包含为局部变量分配值的表达式。

• 游标操作，该操作引用在函数中声明、打开、关闭和释放的局部游标。只允许使用以 INTO 子句向局部变量赋值的 FETCH 语句；不允许使用将数据返回到客户端的 FETCH 语句。

• 修改本地表变量的 INSERT、UPDATE 和 DELETE 语句。

• 调用扩展存储过程的 EXECUTE 语句。

• 有关详细信息，请参阅创建用户定义函数（数据库引擎）。

计算列互操作性

在 SQL Server 2005 及更高版本中，函数具有下列属性。这些属性的值确定了函数是否可用于持久化计算列或索引计算列。

属性	说明	注释
IsDeterministic	函数是确定性函数还是不确定性函数。	确定性函数中允许本地数据访问。例如，如果每次使用一组特定输入值和相同数据库状态调用函数时，函数都返回相同结果，则该函数将被标记为确定性函数。
IsPrecise	函数是精确函数还是不精确函数。	不精确函数包含浮点运算之类的运算。
IsSystemVerified	SQL Server 可验证函数的精度和确定性属性。
SystemDataAccess	函数可以访问 SQL Server 的本地实例中的系统数据（系统目录或虚拟系统表）。
UserDataAccess	函数可以访问 SQL Server 的本地实例中的用户数据。	包含用户定义表和临时表，但不包含表变量。

Transact-SQL 函数的精度和确定性属性由 SQL Server 自动确定。有关详细信息，请参阅用户定义函数的设计指导原则。CLR 函数的数据访问权限和确定性属性可由用户指定。有关详细信息，请参阅 CLR 集成自定义属性的概览。

若要显示这些属性的当前值，请使用 OBJECTPROPERTYEX。

如果用户定义函数具有下列属性值，则可以在索引中使用调用用户定义函数的计算列：

• IsDeterministic = true

• IsSystemVerified = true（计算列是持久性计算列时除外）

• UserDataAccess = false

• SystemDataAccess = false

有关详细信息，请参阅为计算列创建索引。

从函数中调用扩展存储过程

如果在函数中调用扩展存储过程，则该过程不能向客户端返回结果集。向客户端返回结果集的任何 ODS API 都将返回 FAIL。扩展存储过程可以连接回 SQL Server 的实例；不过，该过程不应尝试与调用扩展存储过程的函数同时联接到同一事务。

与通过批处理或存储过程进行调用相似，扩展存储过程在运行 SQL Server 的 Windows 安全帐户的上下文中执行。存储过程的所有者在授予用户 EXECUTE 权限时应考虑这一点。

限制和局限

用户定义函数不能用于执行修改数据库状态的操作。

用户定义函数不能包含将表作为其目标的 OUTPUT INTO 子句。

下列 Service Broker 语句不能包含在 Transact-SQL 用户定义函数的定义中：

• BEGIN DIALOG CONVERSATION

• END CONVERSATION

• GET CONVERSATION GROUP

• MOVE CONVERSATION

• RECEIVE

• SEND

用户定义函数可以嵌套；也就是说，用户定义函数可相互调用。被调用函数开始执行时，嵌套级别将增加；被调用函数执行结束后，嵌套级别将减少。用户定义函数的嵌套级别最多可达 32 级。如果超出最大嵌套级别数，整个调用函数链将失败。从 Transact-SQL 用户定义函数对托管代码的任何引用都将根据 32 级嵌套限制计入一个级别。从托管代码内部调用的方法不根据此限制进行计数。

在 CLR 表值函数中使用排序顺序

在 CLR 表值函数中使用 ORDER 子句时请遵循以下准则：

• 必须确保始终按指定的顺序对结果进行排序。如果没有按指定的顺序对结果进行排序，则在执行查询时 SQL Server 将生成一条错误消息。

• 如果指定了 ORDER 子句，则必须根据列（显式或隐式）的排序规则对表值函数的输出进行排序。例如，如果列排序规则为中文（在表值函数的 DDL 中指定或从数据库排序规则中获取），则必须根据中文排序规则对返回的结果进行排序。

• 如果指定了 ORDER 子句，则在返回结果时始终由 SQL Server 验证 ORDER 子句，而不管查询处理器是否会使用该子句执行进一步的优化。只有您知道 ORDER 子句对查询处理器有用时才使用它。

• 在以下情况下，SQL Server 查询处理器将自动使用 ORDER 子句：

  • 其中 ORDER 子句与索引兼容的“插入”查询。

  • 与 ORDER 子句兼容的 ORDER BY 子句。

  • 其中 GROUP BY 与 ORDER 子句兼容的聚合。

  • 其中不同列与 ORDER 子句兼容的 DISTINCT 聚合。

除非在查询中还指定了 ORDER BY，否则 ORDER 子句不保证在执行 SELECT 查询时得到有序结果。有关如何查询表值函数排序顺序中所包含的列的信息，请参阅 sys.function_order_columns (Transact-SQL)。

元数据

下表列出可用于返回与用户定义函数有关的元数据的系统目录视图。

系统视图	说明
sys.sql_modules	显示 Transact-SQL 用户定义函数的定义。例如： USE AdventureWorks; GO 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 选项创建的函数定义；不过，可显示有关加密函数的其他信息。
sys.assembly_modules	显示 CLR 用户定义函数的有关信息。
sys.parameters	显示用户定义函数中定义的参数的有关信息。
sys.sql_expression_dependencies	显示函数所引用的基础对象。

权限

需要在数据库中具有 CREATE FUNCTION 权限，并对创建函数时所在的架构具有 ALTER 权限。如果函数指定用户定义类型，则需要对该类型具有 EXECUTE 权限。

示例

A. 使用计算 ISO 周的标量值用户定义函数

下面的示例将创建用户定义函数 ISOweek。此函数使用日期参数来计算 ISO 周数。要使此函数能正确计算，必须在调用该函数前调用 SET DATEFIRST 1。

下面的示例也演示如何使用 EXECUTE AS 子句指定可执行存储过程的安全上下文。在该示例中，CALLER 选项指定该过程将在调用该过程的用户的上下文中执行。您还可以指定 SELF、OWNER 和 user_name 等其他选项。

下面是函数调用。请注意，DATEFIRST 设置为 1。

USE AdventureWorks;
GO
IF OBJECT_ID (N'dbo.ISOweek', N'FN') IS NOT NULL
    DROP FUNCTION dbo.ISOweek;
GO
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';

下面是结果集。

ISO Week

----------------

52

B. 创建内联表值函数

下面的示例将返回内联表值函数。对于销售给商店的每个产品，该函数返回三列，分别为 ProductID、Name 以及各个商店年初至今总数的累计 YTD Total。

USE AdventureWorks;
GO
IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL
    DROP FUNCTION Sales.ufn_SalesByStore;
GO
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN 
(
    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'YTD 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
    WHERE SH.CustomerID = @storeid
    GROUP BY P.ProductID, P.Name
);
GO

若要调用该函数，请运行此查询。

SELECT * FROM Sales.ufn_SalesByStore (602);

C. 创建多语句表值函数

下面的示例创建表值函数 fn_FindReports(InEmpID)。如果提供一个有效雇员 ID，该函数将返回一个表，该表对应于直接或间接向该雇员报告的所有雇员。该函数使用递归公用表表达式 (CTE) 来生成雇员的层次结构列表。有关递归 CTE 的详细信息，请参阅 WITH common_table_expression (Transact-SQL)。

USE AdventureWorks;
GO
IF OBJECT_ID (N'dbo.ufn_FindReports', N'TF') IS NOT NULL
    DROP FUNCTION dbo.ufn_FindReports;
GO
CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER)
RETURNS @retFindReports TABLE 
(
    EmployeeID int primary key NOT NULL,
    Name nvarchar(255) NOT NULL,
    Title nvarchar(50) NOT NULL,
    EmployeeLevel int NOT NULL,
    Sort nvarchar (255) NOT NULL
)
--Returns a result set that lists all the employees who report to the 
--specific employee directly or indirectly.*/
AS
BEGIN
   WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort) AS
    (SELECT CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        1,
        CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName)
     FROM HumanResources.Employee AS e
          JOIN Person.Contact AS c ON e.ContactID = c.ContactID 
     WHERE e.EmployeeID = @InEmpID
   UNION ALL
     SELECT CONVERT(Varchar(255), REPLICATE ('| ' , EmployeeLevel) +
        c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        EmployeeLevel + 1,
        CONVERT (Varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' + 
                 LastName)
     FROM HumanResources.Employee as e
          JOIN Person.Contact AS c ON e.ContactID = c.ContactID
          JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
    )
-- copy the required columns to the result of the function 
   INSERT @retFindReports
   SELECT EmployeeID, Name, Title, EmployeeLevel, Sort
   FROM DirectReports 
   RETURN
END;
GO
-- Example invocation
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM dbo.ufn_FindReports(109)
ORDER BY Sort;
GO

D. 创建 CLR 函数

下面的示例假定在本地计算机的默认位置安装了 SQL Server 数据库引擎示例并编译了 StringManipulate.csproj 示例应用程序。有关详细信息，请参阅Supplementary-Aware String Manipulation。

此示例创建 CLR 函数 len_s。在创建该函数之前，程序集 SurrogateStringFunction.dll 已在本地数据库中注册。

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\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf', 'Microsoft SQL Server\90\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 表值函数。

请参阅

参考

ALTER FUNCTION (Transact-SQL)

DROP FUNCTION (Transact-SQL)

OBJECTPROPERTYEX (Transact-SQL)

sys.sql_modules (Transact-SQL)

sys.assembly_modules (Transact-SQL)

EXECUTE (Transact-SQL)

EVENTDATA (Transact-SQL)

其他资源

用户定义函数（数据库引擎）

CLR 用户定义函数

更改历史记录

更新的内容
在“最佳实践”中添加一些信息，介绍可用于确保函数不会由于对其基础对象的更改而过期的方法。