CREATE PROCEDURE CTE$MSTMT_Int_COUNT_test
                 @str    nvarchar(MAX),
                 @retdata  bit = 1,
                 @tookms   int = NULL OUTPUT AS

DECLARE @start datetime2(3)
SELECT @start = sysdatetime()

DECLARE @cnt bigint
SELECT @cnt = SUM(len(word))
FROM   usrdictwords u
JOIN   cte_split_mstmt(@str, DEFAULT) AS a ON u.wordno = convert(int, a.str)

SELECT @tookms = datediff(ms, @start, sysdatetime());


go

CREATE PROCEDURE CTE$MSTMT_Int_EXISTS_test @str nvarchar(MAX), @retdata bit = 1, @tookms int = NULL OUTPUT AS DECLARE @start datetime2(3) SELECT @start = sysdatetime() INSERT #Int_JOIN (word) SELECT u.word FROM usrdictwords u WHERE EXISTS (SELECT * FROM cte_split_mstmt(@str, DEFAULT) AS a WHERE u.wordno = convert(int, a.str)) SELECT @tookms = datediff(ms, @start, sysdatetime()); IF @retdata = 1 SELECT word FROM #Int_JOIN TRUNCATE TABLE #Int_JOIN go
CREATE PROCEDURE CTE$MSTMT_Int_JOIN_test @str nvarchar(MAX), @retdata bit = 1, @tookms int = NULL OUTPUT AS DECLARE @start datetime2(3) SELECT @start = sysdatetime() INSERT #Int_JOIN (word) SELECT u.word FROM usrdictwords u JOIN cte_split_mstmt(@str, DEFAULT) AS a ON u.wordno = convert(int, a.str) SELECT @tookms = datediff(ms, @start, sysdatetime()); IF @retdata = 1 SELECT word FROM #Int_JOIN TRUNCATE TABLE #Int_JOIN go
CREATE PROCEDURE CTE$MSTMT_Int_UNPACK_test @str nvarchar(MAX), @retdata bit = 1, @tookms int = NULL OUTPUT AS DECLARE @start datetime2(3) SELECT @start = sysdatetime() INSERT #Int_UNPACK (number) SELECT number = convert(int, str) FROM cte_split_mstmt(@str, DEFAULT) SELECT @tookms = datediff(ms, @start, sysdatetime()); IF @retdata = 1 SELECT number FROM #Int_UNPACK TRUNCATE TABLE #Int_UNPACK go
CREATE PROCEDURE CTE$MSTMT_Str_COUNT_test @str nvarchar(MAX), @retdata bit = 1, @tookms int = NULL OUTPUT AS DECLARE @start datetime2(3) SELECT @start = sysdatetime() DECLARE @cnt bigint SELECT @cnt = SUM(len(guid)) FROM usrdictwords u JOIN cte_split_mstmt(@str, DEFAULT) AS a ON u.word = a.str SELECT @tookms = datediff(ms, @start, sysdatetime()); go
CREATE PROCEDURE CTE$MSTMT_Str_EXISTS_test @str nvarchar(MAX), @retdata bit = 1, @tookms int = NULL OUTPUT AS DECLARE @start datetime2(3) SELECT @start = sysdatetime() INSERT #Str_JOIN (wordno, guid) SELECT u.wordno, u.guid FROM usrdictwords u WHERE EXISTS (SELECT * FROM cte_split_mstmt(@str, DEFAULT) AS a WHERE u.word = a.str) SELECT @tookms = datediff(ms, @start, sysdatetime()); IF @retdata = 1 SELECT wordno FROM #Str_JOIN TRUNCATE TABLE #Str_JOIN go
CREATE PROCEDURE CTE$MSTMT_Str_JOIN_test @str nvarchar(MAX), @retdata bit = 1, @tookms int = NULL OUTPUT AS DECLARE @start datetime2(3) SELECT @start = sysdatetime() INSERT #Str_JOIN (wordno, guid) SELECT u.wordno, u.guid FROM usrdictwords u JOIN cte_split_mstmt(@str, DEFAULT) AS a ON u.word = a.str SELECT @tookms = datediff(ms, @start, sysdatetime()); IF @retdata = 1 SELECT wordno FROM #Str_JOIN TRUNCATE TABLE #Str_JOIN go
CREATE PROCEDURE CTE$MSTMT_Str_UNPACK_test @str nvarchar(MAX), @retdata bit = 1, @tookms int = NULL OUTPUT AS DECLARE @start datetime2(3) SELECT @start = sysdatetime() INSERT #Str_UNPACK (word) SELECT str FROM cte_split_mstmt(@str, DEFAULT) SELECT @tookms = datediff(ms, @start, sysdatetime()); IF @retdata = 1 SELECT word FROM #Str_UNPACK TRUNCATE TABLE #Str_UNPACK go