SQL SERVER 用户自定义函数（UDF）深入解析

本文内容概要：

UDF 概念、原理、优缺点、UDF 的分类

详细讲述3种 UDF 的创建、调用方法以及注意事项

UDF 的实践建议

基本原理：

UDF：user-defined functions，用户自定义函数的简称。

UDF 是一个例程，它接受参数、执行操作并返回该操作的结果。根据定义，结果可以是标量值（单个）或表。

UDF 的优点：

UDF 可以把复杂的逻辑嵌入到查询中。UDF 可以为复杂的表达式创建新函数。UDF 可以运用在一个表达式或 SELECT 语句的 FROM 子句中，并且还可以绑定到架构。此外，UDF 还可以接受参数。UDF 有助于实施一致性和可重用性。

UDF 的缺点：

该函数一旦误用会产生潜在的性能问题。必须针对WHERE子句的每一行执行的任何函数，不管是用户定义的函数还是系统函数，都将减慢执行速度。

UDF 的类型：

UDF 主要有 3 种类型（SQL Server Management Studio 把内联表值函数与多语句表值函数放到了一个组中）：

标量函数内联表值函数多语句表值函数

一、标量函数

标量函数是返回一个具体值的函数。函数可以接收多个参数、执行计算然后返回一个值。返回值通过RETURN命令返回。用户定义的函数中的每个可能代码路径都以RETURN命令结尾。

标量函数可以运用于 SQLServer 中的任何表达式，甚至在 CHECK 约束的表达式中也可以使用（但不推荐这种用法）。

函数限制

标量函数必须是确定性的，也就是说标量函数必须反复地为相同的输入参数返回相同的值。因此，如newid()函数和rand()函数不允许出现在标量函数中。不允许用户定义标量函数更新数据库、调用存储过程或调用DBCC命令，唯一的例外是可以更新表变量。用户定义函数不能返回BLOB（二进制大型对象）数据，如text、next、timestamp和image数据类型变量。也不能返回表变量可cursor数据类型。对于错误处理，UDF 也不包含 TRY...CATCH 或 RAISERROR。

UDF 可以调用嵌套深度为 32 层以内的其他用户定义函数，或者递归调用自己到 32 层的深度。当然，这只是理论限制，嵌套函数会严重影响性能，应尽可能避免使用嵌套函数。

创建方法

1 CREATE FUNCTION FunctionName (InputParameters) 2 RETURNS DataType 3 AS4 BEGIN5 Code;6 RETURN Expression;7 END;

InputParameters 输入参数包含数据类型定义。参数可以设置默认值（Parameter = default )，需要注意的是在 UDF 中有默认值的参数并不能成为可选参数，为在调用函数时请求到默认值，需要把关键字 DEFAULT 传递到函数的默认值参数位置。

示例1：下面的 UDF 执行一个简单的数学计算，其中第二个参数带有默认值。

CREATE FUNCTION dbo.ufnCalculate(@Numer_a numeric(5,2),@Numer_b numeric(5,2) = 1.0)RETURNS numeric(5,2)ASBEGINRETURN @Numer_a / @Numer_b ;END;GOselect dbo.ufnCalculate(15.3 , 6.54),dbo.ufnCalculate(9.0 , DEFAULT);结果：------ ------2.389.00

示例2：计算并返回某个时间所在月份的天数。

CREATE FUNCTION [dbo].[GetMonthDay](@date datetime)RETURNS intAS BEGINDECLARE @date1 datetimeSELECT @date1 =Dateadd(MM,1,@date)RETURN day(Dateadd(DD,-day(@date1),@date1))END;

调用方法

在接受单值的表达式中，标量函数可用于任何地方。用户定义的标量函数必须通过一个最少有两部分的名称（所有者.函数名）来调用。

下面的脚本演示了在数据库的订单表中调用示例2中的函数及其返回值。

SELECT S.BIL_DD,dbo.GetMonthDay(BIL_DD) as DAYS_M FROM Orders S结果BIL_DD DAYS_M------ -------01-3131-02-1528

二、内联表值函数

与视图相似，内联表值函数也是为一个存储的SELECT语句封装。内联表值函数保留了视图的优点，还添加了一些参数。

创建方法

内联表值用户定义函数没有BEGIN / END主体。SELECT语句是作为一个虚拟数据表返回的：

CREATE FUNCTION FunctionName （InputParameters）RETURNS Table AS RETURN (Select Statement)；

示例：下面的示例返回某个客户所订购产品的汇总情况。

CREATE FUNCTION dbo.ufnGetProductTotalByCust (@custNo varchar (10))RETURNS Table ASRETURN(SELECT H.CUS_NO,B.PRD_NO,SUM(B.QTY) as TOTAL_PRD FROM TF_POS AS B --订单货品明细表LEFT JOIN MF_POS AS H --订单客户信息表ON H.OS_NO=B.OS_NO WHERE H.CUS_NO=@custNo GROUP BY H.CUS_NO,B.PRD_NO );GO

调用方法

通过dbo.ufnGetProductTotalByCust查询客户代号为"CT060228"的产品汇总数据，函数出现在SELECT语句的FROM部分：

SELECT PRD_NO,TOTAL_PRD FROM dbo.ufnGetProductTotalByCust('CT060228') ORDER BY PRD_NO DESC

返回结果（部分）：

PRD_NO TOTAL_PRD------------ ------------------109100300065792.000000001091004000310776.000000001091206001411442.00000000109130400099276.0000000011410030028900.00000000......

与视图的关系

与视图相比，内联表值函数的优势在于其可以使用参数。而视图不包含参数，而且在运行时想要限制结果需要把 WHERE 子句添加到调用视图的 SELECT 语句中来实现。

示图的调用示例，假设已经存在视图 dbo.vwProductTotalByCust，调用视图时，在 SELECT 语句中添加了一个 WHERE 子句限制：

SELECT * FROM dbo.vwProductTotalByCust WHERE cus_no='CT060228'

关联方法

表值用户定义函数的关联可以使用 APPLY 命令，从而使 UDF 针对由主查询处理的每一行接受一个不同的参数值。

APPLY 命令具有两种形式。最普通的一种形式是 CROSS APPLY，它运行起来更像一个内联接。CROSS APPLY 命令联接主查询的数据与来自用户自定义函数的任意表值数据集。如果未从UDF 返回数据，那么主查询的行也不能返回，如下图的例子所示：

SELECT T.PRD_NO,P.NAME,T.TOTAL_PRD FROM PRDT P --产品资料表CROSS APPLY dbo.ufnGetProductTotalByCust('CT060228') TORDER BY T.PRD_NO DESC 结果:

PRD_NO NAMETOTAL_PRD------------ ------------ ------------------------10910030006 3pcs storage jar 5792.0000000010910040003 2pcs storage jar 10776.0000000010912060014 4pcs spice jar11442.0000000010913040009 6pcs spice jar9276.0000000011410030028 salad dressing900.00000000......

CROSS APPLY 的第2种形式是 OUTER APPLY 命令，操作上与左联接相似。这种形式下，主查询的行将包含在结果集中，而不管 UDF 返回的虚拟表是否为空。

标量函数和内联表值函数可生成完成相同的结果集，那么这两者的区别是什么呢？

标量函数针对每一行运行一次，而内联表值函数由查询优化器处理，非常类似于视图。因为内联表值函数会由查询优化器进行处理，所以建议尽可能优先使用内联表值函数，而非标量函数。

架构绑定

架构绑定阻止更改或删除函数所依赖的任何对象。如果架构绑定函数引用了某个表A，那么表A不可更改或删除，但可以将列添加到表A。

架构绑定的方法：在函数创建语句的 RETURNS 之后和 AS 之前添加选项 WITH SCHEMA BINDING，如下所示：

1 CREATE FUNCTION FunctionName (InputParameters) 2 RETURNS DataType 3 WITH SCHEMA BINDING 4 AS5 BEGIN6 Code;7 RETURN Expression;8 END;

可以使用ALTER修改函数，使其不再包含架构绑定，以便可以修改引用对象。

三、多语句表值函数

将标量函数与内联表值函数的功能结合起来就构成了复杂的多语句表值函数。

特征：这种类型的函数创建了一个表变量，将它置于代码中，然后从函数返回，以便能在SELECT语句中使用。

优点：可以代码内生成复杂结果集，以便在SELECT语句中使用，在查询中构建复杂逻辑，并解决那些没有游标就很难解决的问题。

创建方法

创建多语句表值函数的语法与创建标量函数的语法相似：

CREATE FUNCTION FunctionName （InputParamenters）RETURNS @TableName TABLE (columns)ASBEGIN; Code to populate table variableRETURN;END;

示例：下面的过程构建了一个返回基本结果集的多语句表值用户定义的函数，函数首先在 CREATE FUNCTION 头中创建了一个名为 @PruductList 的表变量，在函数体中，两个 INSERT 语句置于@ProductList 表变更中，如果函数执行完毕，表变更 @ProductList 将作为函数的输出传回。

ufnGetProductsAndOrderTotals函数返回Product表中的每个产品和每个产品的订单总数。

CREATE FUNCTION ufnGetProductsAndOrderTotals() RETURNS @ProductList TABLE (ProductID int,ProductName nvarchar(100),TotalOrders int)ASBEGIN;INSERT @ProductList（ProductID，ProductName） SELECT ProductID，Name FROM Product;UPDATE p1 SET TotalOrders = (SELECT sum(sod.OrderQty) FROM @ProductList ip1 JOIN SalesOrderDetail sod ON ip1.ProductID = sod.ProductIDWHERE ip1.ProductID = p1.ProductID) FROM @ProductList p1 ;RETURN;END;

调用方法

只需要在SELECT语句的FROM部分引用该函数，即可查询到函数的执行结果。下面的代码检索ufnGetProductsAndOrderTotals函数的结果：

SELECT ProductID，ProductName，TotalOrders FROM ufnGetProductsAndOrderTotals（） ORDER BY TotalOrders DESC

结果集如下：

ProductID ProductNameTotalOrders------------ ---------------------------------715 4 PCS Storage Jar 8311780 6 PCS Spice Jar 6800......

四、UDF 的实践建议

无疑 UDF为我们的 T-SQL 选项添加了灵活性，但如果这些函数运用不当，带来的性能缺陷也是很严重的。UDF并不能成为子查询、视图或存储过程的替代物。

从上面的示例，我们不难看出，三种类型函数可以产生基本相同的结果集，实践中可以将自己的函数定义为其种任意一种。

建议一：性能最优化

如果选择 UDF来封装查询逻辑，则建议遵循下面的这些基本原则：

相对于多语句表值函数，尽可能优先选择内联表值函数；尽量避免使用标量函数，尽可能使用内联表值函数取代它；如果需要使用多语句表值函数，则对比一下存储过程是不是更合适的解决文案。虽然需要花更多的时间，但考虑长期的性能影响，还是值得的。

建议二：命名一致性

为方便我们的T-SQL更易于阅读更容易排除故障，我们应该确保为所有的 UDF创建某种统一类型的命名约束。最常用的方法是采用名称前缀，更进一步，可以让前缀表明 UDF是标量函数、内联表值函数还是多语句表值函数。例如，返回每个产品类别的月平均销售额的内联表值函数，可以将其命名为 udfAvgMonSalesPerCategory或ifn_AvgMonSalesPerCategory。