HoneyHoney

引用 http://sqlblog.com/blogs/hugo_kornelis/archive/2014/10/19/43466.aspx 

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 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;

--Scalar function
CREATE FUNCTION dbo.Triple(@Input int) 
RETURNS int 
AS 
BEGIN; 
DECLARE @Result int; 
SET @Result = @Input * 3; 
RETURN @Result; 
END; 
go 


--inline TVF
CREATE FUNCTION dbo.TripleV2(@Input int)
RETURNS TABLE
AS
RETURN
SELECT 3*@Input AS amt
GO


--Multlple-Line TVF
CREATE FUNCTION dbo.TripleV3(@Input int) 
RETURNS @Result TABLE(amt INT)
AS 
BEGIN; 
INSERT INTO @Result(amt) 
SELECT @Input * 3; 
RETURN; 
END; 
go 

SELECT MAX(dbo.Triple(DataVal)) AS MaxTriple 
FROM   dbo.LargeTable;

SELECT MAX(t.amt)
FROM dbo.LargeTable lt
CROSS APPLY dbo.TripleV3(lt.DataVal) t

SELECT MAX(t.amt)
FROM dbo.LargeTable lt
CROSS APPLY dbo.TripleV2(lt.DataVal) t

直接上结果

1. 最糟糕的是Multiple-Line Table Value Function，一次都要10秒。最主要还是要执行100000次。
2. 次之的是Scalar Value Function。一次大概1.6秒的样子。其实每个function也要执行100000次。
3. 最好的是Inline Table Value Function。无论CPU还是执行时间，都非常得好。