The best I could do was simplify part of the query and change it to a table-valued function. Scalar functions are known to be poor executors, and the advantage of the built-in TVF is that the definition of the request is expanded in the main request, like a view.
This greatly reduces the execution time of the tests that I performed.
ALTER FUNCTION dbo.FuzySearchTVF (@Reference VARCHAR(200), @Target VARCHAR(200)) RETURNS TABLE AS RETURN ( WITH N (n) AS ( SELECT TOP (ISNULL(CASE WHEN DATALENGTH(@Reference) > DATALENGTH(@Target) THEN DATALENGTH(@Reference) ELSE DATALENGTH(@Target) END, 0)) ROW_NUMBER() OVER(ORDER BY n1.n) FROM (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) AS N1 (n) CROSS JOIN (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) AS N2 (n) CROSS JOIN (VALUES (1), (1)) AS N3 (n) WHERE @Reference IS NOT NULL AND @Target IS NOT NULL ), Src AS ( SELECT Reference = CASE WHEN DATALENGTH(@Reference) > DATALENGTH(@Target) THEN @Reference ELSE @Reference + REPLICATE('_', DATALENGTH(@Target) - DATALENGTH(@Reference)) END, Target = CASE WHEN DATALENGTH(@Target) > DATALENGTH(@Reference) THEN @Target ELSE @Target + REPLICATE('_', DATALENGTH(@Target) - DATALENGTH(@Reference)) END, WordLength = CASE WHEN DATALENGTH(@Reference) > DATALENGTH(@Target) THEN DATALENGTH(@Reference) ELSE DATALENGTH(@Target) END WHERE @Reference IS NOT NULL AND @Target IS NOT NULL AND @Reference != @Target ), Scores AS ( SELECT seq = t1.n , Letter = SUBSTRING(s.Reference, t1.n, 1), s.WordLength , LetterScore = s.WordLength - ISNULL(MIN(ABS(t1.n - t2.n)), s.WordLength) FROM Src AS s CROSS JOIN N AS t1 INNER JOIN N AS t2 ON SUBSTRING(@Target, t2.n, 1) = SUBSTRING(s.Reference, t1.n, 1) WHERE @Reference IS NOT NULL AND @Target IS NOT NULL AND @Reference != @Target GROUP BY t1.n, SUBSTRING(s.Reference, t1.n, 1), s.WordLength ) SELECT [Score] = 100 WHERE @Reference = @Target UNION ALL SELECT 0 WHERE @Reference IS NULL OR @Target IS NULL UNION ALL SELECT CAST(SUM(LetterScore) * 100.0 / MAX(WordLength * WordLength) AS NUMERIC(5, 2)) FROM Scores WHERE @Reference IS NOT NULL AND @Target IS NOT NULL AND @Reference != @Target GROUP BY WordLength );
And this will be called as:
SELECT f.Score FROM dbo.Customer AS c CROSS APPLY [dbo].[FuzySearch]('First Name Middle Name Last Name', c.FirstName) AS f
This is still a pretty complicated feature, and depending on the number of entries in your clients table, I think getting it up to 1 second will be a bit of a challenge.