另外，我们来看看Performance Considerations of User-Defined Functions in SQL Server 2012这篇文章中，测试UDF的性能案例，本想单独翻译这篇文章，不过结合这篇文章，在此实验验证也是个不错的选择。下面案例全部来自这篇博客。我们先准备测试环境:

CREATE FUNCTION dbo.Triple(@Input INT)        RETURNS INT AS BEGIN;   DECLARE @Result INT;   SET @Result = @Input * 3;   RETURN @Result; END; GO  CREATE TABLE dbo.LargeTable   (KeyVal INT NOT NULL PRIMARY KEY,    DataVal INT NOT NULL CHECK (DataVal BETWEEN 1 AND 10)   ); WITH Digits AS (SELECT d FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS d(d)) INSERT INTO dbo.LargeTable (KeyVal, DataVal) SELECT 1000000 * sm.d      + 100000 * ht.d + 10000 * tt.d + 1000 * st.d      + 100 * h.d + 10 * t.d + s.d + 1,        10 * RAND(CHECKSUM(NEWID())) + 1 FROM   Digits AS s,  Digits AS t,  Digits AS h,        Digits AS st, Digits AS tt, Digits AS ht,        Digits AS sm;GO CREATE INDEX NCL_LargeTable_DataVal ON dbo.LargeTable (DataVal);GO SET STATISTICS TIME ON;  SELECT MAX(dbo.Triple(DataVal)) AS MaxTriple FROM dbo.LargeTable AS d;  SELECT MAX(3 * DataVal) AS MaxTriple FROM dbo.LargeTable AS d;  SET STATISTICS TIME OFF;

如上所示，用户定义的标量函数dbo.Triple，测试用的的一个表dbo.LargeTable ，以及构造了1000000行数据。从下面我们可以看到用户定义标量函数性能确实很糟糕。

下面测试4中写法的性能。相信这个简单的脚本，大家都能看懂，在此不做过多描述、说明：

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;SET NOCOUNT ON;SET STATISTICS TIME ON; SELECT MAX(dbo.Triple(DataVal)) AS MaxTriple FROM dbo.LargeTable AS d;  SET STATISTICS TIME OFF;

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;SET STATISTICS TIME ON; SELECT MAX(dbo.Triple(DataVal)) AS MaxTriple FROM (SELECT DISTINCT DataVal FROM dbo.LargeTable) AS d; SET STATISTICS TIME OFF;

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS; SET STATISTICS TIME ON;  SELECT MAX(3 * DataVal) AS MaxTriple  FROM (SELECT DISTINCT DataVal FROM dbo.LargeTable) AS d;  SET STATISTICS TIME OFF;

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS; SET STATISTICS TIME ON;  SELECT MAX(3 * DataVal) AS MaxTriple  FROM dbo.LargeTable AS d;  SET STATISTICS TIME OFF;

Performance Considerations of User-Defined Functions in SQL Server 2012博客里面统计的数据如下所示

T-SQL Syntax

Avg CPU Time in ms

Avg Elapsed Time in ms

Function, no distinct subquery

12925.0

14247.8

Function, subquery

853.0

853.8

Inline calculation, subquery

853.2

850.4

Inline calculation, no distinct subquery

0.0

0.0