en-UShe-IL
You are here:   Blog > new Forums FAQ > Forums FAQ 357
Register   |  Login

Inline Scalar function?!?

/********************************************************************* DDL+DML */
IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL
    DROP TABLE #TestTable;
GO
SELECT TOP 10000000 -- we creat 10 million ros test table
    SomeNumber = IDENTITY(INT,1,1)
    INTO #TestTable
    FROM sys.all_columns ac1
    CROSS JOIN sys.all_columns ac2;
GO;
 
CREATE FUNCTION NyScalarFunction (@SomeINT INT)
RETURNS INT WITH SCHEMABINDING AS
BEGIN
    RETURN @SomeINT * 2
END
GO;
 
CREATE FUNCTION NyInlineScalarFunction (@SomeINT INT)
RETURNS table WITH SCHEMABINDING AS
    RETURN SELECT Doubled = @SomeINT * 2;
GO
 
/********************************************************************* Test time!!! */
-- using scalar function
DECLARE @Result INT;
DECLARE @StartTime DATETIME;
SELECT @StartTime = GETDATE();
SELECT @Result = dbo.NyScalarFunction(SomeNumber)
FROM #TestTable;
PRINT DATEDIFF(ms,@StartTime,GETDATE());
-- go make your self Coffee
-- Result: 16,686
 
-- direct without any function
DECLARE @Result INT;
DECLARE @StartTime DATETIME;
SELECT @StartTime = GETDATE();
SELECT @Result = SomeNumber * 2
FROM #TestTable;
PRINT DATEDIFF(ms,@StartTime,GETDATE());
-- Result: 1,273
 
-- using Inline Table-Valued Functions
DECLARE @Result INT;
DECLARE @StartTime DATETIME;
SELECT @StartTime = GETDATE();
SELECT @Result = ca.Doubled
FROM #TestTable
CROSS APPLY NyInlineScalarFunction(SomeNumber) ca;
PRINT DATEDIFF(ms,@StartTime,GETDATE());
-- Result: 1,276