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.
*/
