USE tempdb;
GO
IF OBJECT_ID('tempdb.dbo.#temp1', 'U') IS NOT NULL DROP TABLE #temp1;
WITH cteSymbols AS
(
SELECT  CharSymbol
FROM    (VALUES ('('), ('('), ('-'), (' '), ('-('), (')-'), ('.'), (','), ('/'), ('@')) dt (CharSymbol)
)
SELECT  TOP (1000000)
        IDENTITY(INTEGER) AS RowID,
        CONVERT(VARCHAR(30), s1.CharSymbol + CONVERT(VARCHAR(8), ABS(so1.object_id) % 10000000,0) +
                             s2.CharSymbol + CONVERT(VARCHAR(8), ABS(so2.object_id) % 10000000,0) +
                             s3.CharSymbol
               ) AS StringOfNumbersWithNonNumbers
INTO    #temp1
FROM    cteSymbols s1
CROSS JOIN cteSymbols s2
CROSS JOIN cteSymbols s3
CROSS JOIN sys.all_objects so1
CROSS JOIN sys.all_objects so2;
 
-- let's look at a few rows to see what we have
SELECT * FROM #temp1 WHERE RowID <= 10;
GO

-- Inline table valued function
IF OBJECT_ID('dbo.ITVF_TEST') IS NOT NULL DROP FUNCTION dbo.ITVF_TEST;
GO
CREATE FUNCTION dbo.ITVF_TEST (@Input VARCHAR(30))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
SELECT (SELECT SUBSTRING(@Input,N,1)
        FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30)) AS x(N)
        WHERE N<=LEN(@Input)
        AND SUBSTRING(@Input,N,1) LIKE ('[0-9]')
        ORDER BY N
        FOR XML PATH(''), TYPE).value('.','VARCHAR(30)') AS StringNumbersOnly;
GO
 
-- Multi-statement table valued function
IF OBJECT_ID('dbo.MSTVF_TEST') IS NOT NULL DROP FUNCTION dbo.MSTVF_TEST;
GO
CREATE FUNCTION dbo.MSTVF_TEST (@Input VARCHAR(30))
RETURNS @Output TABLE (StringNumbersOnly VARCHAR(30))
WITH SCHEMABINDING
AS
BEGIN
    INSERT INTO @Output (StringNumbersOnly)
    SELECT (SELECT SUBSTRING(@Input,N,1)
            FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30)) AS x(N)
            WHERE N<=LEN(@Input)
            AND SUBSTRING(@Input,N,1) LIKE ('[0-9]')
            ORDER BY N
            FOR XML PATH(''), TYPE).value('.','VARCHAR(30)') AS StringNumbersOnly;
    RETURN;
END;
GO
 
-- Scalar function
IF OBJECT_ID('dbo.SF_TEST') IS NOT NULL DROP FUNCTION dbo.SF_TEST;
GO
CREATE FUNCTION dbo.SF_TEST (@Input VARCHAR(30))
RETURNS VARCHAR(30)
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @Output VARCHAR(30);
    SELECT @Output = (SELECT SUBSTRING(@Input,N,1)
            FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30)) AS x(N)
            WHERE N<=LEN(@Input)
            AND SUBSTRING(@Input,N,1) LIKE ('[0-9]')
            ORDER BY N
            FOR XML PATH(''), TYPE).value('.','VARCHAR(30)');
    RETURN @Output;
END;
GO

SELECT  t.RowID,
        t.StringOfNumbersWithNonNumbers AS StringOfNumbers,
        dbo.SF_TEST(t.StringOfNumbersWithNonNumbers) AS ScalarFunction,
        MSTVF.StringNumbersOnly AS MultiStatementTVF,
        ITVF.StringNumbersOnly AS InlineTVF
FROM    #temp1 t
CROSS APPLY dbo.MSTVF_TEST(t.StringOfNumbersWithNonNumbers) MSTVF
CROSS APPLY dbo.ITVF_TEST(t.StringOfNumbersWithNonNumbers) ITVF
WHERE   t.RowID <= 10
ORDER BY t.RowID;

-- PERFORMANCE TESTING TIME!!!
-- table to store the times:
IF OBJECT_ID('tempdb.dbo.#temp2') IS NOT NULL DROP TABLE #temp2;
CREATE TABLE #temp2 (
    RowID INTEGER IDENTITY,
    FunctionName sysname,
    ActionDateTime DATETIME2(7) NOT NULL DEFAULT(SYSDATETIME()));
 
 
--------- INLINE TABLE-VALUED FUNCTION -----------
IF OBJECT_ID('tempdb.dbo.#ITVF_RESULTS') IS NOT NULL DROP TABLE #ITVF_RESULTS;
INSERT INTO #temp2 (FunctionName) VALUES  ('ITVF_TEST');
SELECT  *
INTO    #ITVF_RESULTS
FROM    #temp1
CROSS APPLY dbo.ITVF_TEST(StringOfNumbersWithNonNumbers)
INSERT INTO #temp2 (FunctionName) VALUES  ('ITVF_TEST');
 
 
--------- MULTI_STATEMENT TABLE-VALUED FUNCTION -----------
IF OBJECT_ID('tempdb.dbo.#MSTVF_RESULTS') IS NOT NULL DROP TABLE #MSTVF_RESULTS;
INSERT INTO #temp2 (FunctionName) VALUES  ('MSTVF_TEST');
SELECT  *
INTO    #MSTVF_RESULTS
FROM    #temp1
CROSS APPLY dbo.MSTVF_TEST(StringOfNumbersWithNonNumbers)
INSERT INTO #temp2 (FunctionName) VALUES  ('MSTVF_TEST');
 
 
--------- SCALAR FUNCTION ------------
IF OBJECT_ID('tempdb.dbo.#SF_Results') IS NOT NULL DROP TABLE #SF_Results;
INSERT INTO #temp2 (FunctionName) VALUES  ('SF_TEST');
SELECT  *, dbo.SF_TEST(StringOfNumbersWithNonNumbers) AS StringNumbersOnly
INTO    #SF_Results
FROM    #temp1;
INSERT INTO #temp2 (FunctionName) VALUES  ('SF_TEST');
 
 
 
---------- Show the testing results -----------
WITH cte AS
(
SELECT  t.FunctionName,
        DATEDIFF(MICROSECOND, MIN(t.ActionDateTime), MAX(t.ActionDateTime)) AS [Duration (microseconds)]
FROM    #temp2 t
GROUP BY t.FunctionName
)
SELECT  FunctionName,
        [Duration (microseconds)],
        CONVERT(NUMERIC(5,2), (cte.[Duration (microseconds)] * 1.0 / SUM(cte.[Duration (microseconds)]) OVER () * 1.0) * 100.0) AS PercentOfBatch
FROM    cte
ORDER BY [Duration (microseconds)];

USE tempdb;
GO
IF OBJECT_ID('tempdb.dbo.#temp1') IS NOT NULL DROP TABLE #temp1;
WITH cte (Col) AS
(
-- get random characters
SELECT  CHAR(ABS(CHECKSUM(NEWID())) % 26 + 65)
FROM    sys.all_columns
)
SELECT  TOP (1000000)
        t1.Col + t2.Col + t3.Col + t4.col + t5.Col AS Col1,
        t5.Col + t3.col + t1.col + t2.col + t4.col AS Col2,
        t3.Col + t1.Col + t4.Col + t5.Col + t2.Col AS Col3
INTO    #temp1
FROM    cte t1
CROSS JOIN cte t2
CROSS JOIN cte t3
CROSS JOIN cte t4
CROSS JOIN cte t5;
GO
 
IF OBJECT_ID('dbo.ITVF_Test') IS NOT NULL DROP FUNCTION dbo.ITVF_Test;
IF OBJECT_ID('dbo.MSTVF_Test') IS NOT NULL DROP FUNCTION dbo.MSTVF_Test;
IF OBJECT_ID('dbo.SF_Test') IS NOT NULL DROP FUNCTION dbo.SF_Test;
GO
 
CREATE FUNCTION dbo.ITVF_Test (@Col1 VARCHAR(5), @Col2 VARCHAR(5), @Col3 VARCHAR(5))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT @Col1 + ' ' + @Col2 + ', ' + @Col3 AS ReturnString;
GO
 
CREATE FUNCTION dbo.MSTVF_Test (@Col1 VARCHAR(5), @Col2 VARCHAR(5), @Col3 VARCHAR(5))
RETURNS @Results TABLE (ReturnString VARCHAR(20))
WITH SCHEMABINDING
AS
BEGIN
    INSERT INTO @Results (ReturnString)
    SELECT @Col1 + ' ' + @Col2 + ', ' + @Col3 AS ReturnString;
    RETURN
END
GO
 
CREATE FUNCTION dbo.SF_Test (@Col1 VARCHAR(5), @Col2 VARCHAR(5), @Col3 VARCHAR(5))
RETURNS VARCHAR(20)
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @ReturnString VARCHAR(20);
    SET @ReturnString = @Col1 + ' ' + @Col2 + ', ' + @Col3;
    RETURN @ReturnString;
END
GO

-- Compare results
SELECT  TOP (10)
        ITVF.ReturnString AS ITVF,
        MSTVF.ReturnString AS MSTVF,
        dbo.SF_Test(Col1, Col2, Col3) AS SF
FROM    #temp1
CROSS APPLY dbo.ITVF_Test(Col1, Col2, Col3) ITVF
CROSS APPLY dbo.MSTVF_Test(Col1, Col2, Col3) MSTVF;

-- PERFORMANCE TESTING TIME!!!
-- table to store the times:
IF OBJECT_ID('tempdb.dbo.#temp2') IS NOT NULL DROP TABLE #temp2;
CREATE TABLE #temp2 (
    RowID INTEGER IDENTITY,
    FunctionName sysname,
    ActionDateTime DATETIME2(7) NOT NULL DEFAULT(SYSDATETIME()));
 
 
--------- INLINE TABLE-VALUED FUNCTION -----------
IF OBJECT_ID('tempdb.dbo.#ITVF_RESULTS') IS NOT NULL DROP TABLE #ITVF_RESULTS;
INSERT INTO #temp2 (FunctionName) VALUES  ('ITVF_TEST');
SELECT  *
INTO    #ITVF_RESULTS
FROM    #temp1
CROSS APPLY dbo.ITVF_TEST(Col1, Col2, Col3)
INSERT INTO #temp2 (FunctionName) VALUES  ('ITVF_TEST');
 
 
--------- MULTI_STATEMENT TABLE-VALUED FUNCTION -----------
IF OBJECT_ID('tempdb.dbo.#MSTVF_RESULTS') IS NOT NULL DROP TABLE #MSTVF_RESULTS;
INSERT INTO #temp2 (FunctionName) VALUES  ('MSTVF_TEST');
SELECT  *
INTO    #MSTVF_RESULTS
FROM    #temp1
CROSS APPLY dbo.MSTVF_TEST(Col1, Col2, Col3)
INSERT INTO #temp2 (FunctionName) VALUES  ('MSTVF_TEST');
 
 
--------- SCALAR FUNCTION ------------
IF OBJECT_ID('tempdb.dbo.#SF_Results') IS NOT NULL DROP TABLE #SF_Results;
INSERT INTO #temp2 (FunctionName) VALUES  ('SF_TEST');
SELECT  *, dbo.SF_TEST(Col1, Col2, Col3) AS StringNumbersOnly
INTO    #SF_Results
FROM    #temp1;
INSERT INTO #temp2 (FunctionName) VALUES  ('SF_TEST');
 
 
 
---------- Show the testing results -----------
WITH cte AS
(
SELECT  t.FunctionName,
        DATEDIFF(MICROSECOND, MIN(t.ActionDateTime), MAX(t.ActionDateTime)) AS [Duration (microseconds)]
FROM    #temp2 t
GROUP BY t.FunctionName
)
SELECT  FunctionName,
        [Duration (microseconds)],
        CONVERT(NUMERIC(5,2), (cte.[Duration (microseconds)] * 1.0 / SUM(cte.[Duration (microseconds)]) OVER () * 1.0) * 100.0) AS PercentOfBatch
FROM    cte
ORDER BY [Duration (microseconds)];