Sean Pearce (10/31/2013)

---

It is much faster to apply a table function.

CREATE TABLE Test1 (ID INT);

GO

INSERT INTO Test1

SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY a.object_id)

FROM sys.all_columns a

CROSS JOIN sys.all_columns b

GO

CREATE FUNCTION UDF_INLINE (@Input INT)

RETURNS INT

AS

BEGIN

DECLARE @I INT;

SET @I = @Input * 0.14;

RETURN @I;

END;

GO

CREATE FUNCTION UDF_APPLY (@Input INT)

RETURNS TABLE

AS

RETURN (SELECT @Input * 0.14 AS Result);

GO

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

-- Return the column with no function

SELECT

ID

INTO

#test1

FROM

Test1;

/*

Table 'Test1'. Scan count 1, logical reads 3345

CPU time = 437 ms, elapsed time = 533 ms.

*/

-- Return the inline function

SELECT

dbo.UDF_INLINE(ID) AS RowName

INTO

#test2

FROM

Test1;

/*

Table '#test2'. Scan count 0, logical reads 1001607

Table 'Test1'. Scan count 1, logical reads 3345

CPU time = 10389 ms, elapsed time = 13965 ms.

*/

-- Apply the function

SELECT

b.Result

INTO

#test3

FROM

Test1 t

CROSS APPLY

dbo.UDF_APPLY(t.ID) AS b;

/*

Table 'Test1'. Scan count 1, logical reads 3345

CPU time = 577 ms, elapsed time = 578 ms.

*/