用户定义函数

用户定义函数分两类，标量函数和表值函数
用户定义函数可以执行复杂的逻辑，可以接受参数并返回数据

模块化设计，只编译一次，减少网络流量

标量函数可能引起性能问题

USE AdventureWorks2014;
GO
CREATE FUNCTION Sales.FetchProductOrderNum
(
@ProuctID INT
) RETURNS INT
BEGIN
DECLARE @SaleOrderNum INT;
SELECT @SaleOrderNum=COUNT(SalesOrderID) FROM Sales.SalesOrderDetail
WHERE ProductID=@ProuctID
GROUP BY ProductID;

RETURN @SaleOrderNum;
END
GO

--执行标量函数
SET STATISTICS TIME ON;

SELECT DISTINCT ProductID, Sales.FetchProductOrderNum(ProductID) FROM Sales.SalesOrderDetail
WHERE ProductID=870 （4688条数据）

SELECT DISTINCT ProductID, Sales.FetchProductOrderNum(ProductID) FROM Sales.SalesOrderDetail
WHERE ProductID=897 （2条数据）


SET STATISTICS TIME OFF;

这两条语句的执行时间差距是几千倍

这是因为标量函数采用了过程的逐行处理方式，性能会明显下降，而sql查询是基于集合的
实际开发 标量的逻辑会更复杂，资源消耗更大，

对上面问题的优化：
不用定义函数或者减少调用次数

--减少调用次数的子查询方式优化
SET STATISTICS TIME ON;

SELECT ProductID, Sales.FetchProductOrderNum(ProductID)
FROM(
SELECT DISTINCT ProductID FROM Sales.SalesOrderDetail
WHERE ProductID=870) T

SET STATISTICS TIME OFF;

--减少调用次数的临时表方式优化
SET STATISTICS TIME ON;


SELECT DISTINCT ProductID INTO #SalesOrderDetail FROM Sales.SalesOrderDetail
WHERE ProductID=870;

SELECT ProductID, Sales.FetchProductOrderNum(ProductID)
FROM #SalesOrderDetail

SET STATISTICS TIME OFF;

 

上面是两种优化方式

但其实表值函数比标量函数性能要好的多

所以用表值函数替换标量函数也是一个可以优化的方案

如果对单列值进行去重复值（使用 distinct） 使用group by 替换 ，性能更好

INNER JOIN @FNCardTable b ON CHARINDEX(b.FN_Card,
a.FN_Card) > 0