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