CREATE FUNCTION dbo.nsq_iSVF (@int int)RETURNS bigint WITH SCHEMABINDINGASBEGIN RETURN @int/2ENDGOCREATE FUNCTION dbo.nsq_iTVF (@int int)RETURNS @rs TABLE (n bigint) WITH SCHEMABINDINGASBEGIN INSERT @rs SELECT @int/2; RETURN;ENDGO--Create some test dataIF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp;CREATE TABLE #tmp (n bigint);INSERT #tmp SELECT TOP 100000 ABS(CHECKSUM(NEWID())) FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2;SET NOCOUNT ON;GO--Test the Functions (on my local PC)SET STATISTICS TIME ON SELECT x.n n1, s.n n2 FROM #tmp x CROSS APPLY dbo.nsq_iTVF(x.n) sSET STATISTICS TIME OFFGOSET STATISTICS TIME ON SELECT n n1, dbo.nsq_iSVF(n) n2 FROM #tmp xSET STATISTICS TIME OFFGODROP TABLE #tmpGO

SQL Server Execution Times: CPU time = 4977 ms, elapsed time = 5108 ms. SQL Server Execution Times: CPU time = 1497 ms, elapsed time = 1599 ms.

--iTVFCREATE FUNCTION dbo.hd_iTVF (@s1 varchar(8000), @s2 varchar(8000))RETURNS @hd TABLE(hd int)ASBEGIN WITH nums(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM [master].dbo.spt_values Tally), matrix AS (SELECT SUBSTRING(@s1,n,1) s1, SUBSTRING(@s2,n,1) s2 FROM nums WHERE n<=LEN(@s1)) INSERT @hd SELECT CASE WHEN LEN(@s1)<>LEN(@s2) THEN NULL ELSE COUNT(*) END FROM matrix WHERE s1<>s2; RETURN;ENDGO--iSVFCREATE FUNCTION dbo.hd_SVF (@s1 varchar(8000), @s2 varchar(8000))RETURNS intASBEGIN DECLARE @hd int=0; WITH nums(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM [master].dbo.spt_values Tally), matrix AS (SELECT SUBSTRING(@s1,n,1) s1, SUBSTRING(@s2,n,1) s2 FROM nums WHERE n<=LEN(@s1)) SELECT @hd=CASE WHEN LEN(@s1)<>LEN(@s2) THEN NULL ELSE COUNT(*) END FROM matrix WHERE s1<>s2; RETURN @hd;ENDGO--Create some test dataIF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp;CREATE TABLE #tmp (s1 varchar(10), s2 varchar(10))INSERT #tmpSELECT TOP 100000 s1 = ABS(CHECKSUM(NEWID())), s2=ABS(CHECKSUM(NEWID()))FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2;SET NOCOUNT ON;GO--Test the Functions (on my local PC)SET STATISTICS TIME ON SELECT s1, s2, s.hd FROM #tmp x CROSS APPLY dbo.hd_iTVF(x.s1,x.s2) sSET STATISTICS TIME OFFGOSET STATISTICS TIME ON SELECT s1, s2, dbo.hd_SVF(s1,s2) AS hd FROM #tmp xSET STATISTICS TIME OFFGODROP TABLE #tmpGO