Thanks for the suggestion Jeff.

Below is an updated version using the cteTally table. I tested this against the first function and for a 10,000 row test file the cteTally version was 1200ms faster per run on average than the regular recursive CTE. Not a HUGE difference, but significant. Otherwise, everything about the function still works the same.

I didn't quite understand that even in an iTVF that a recursive CTE is just a loop counter. After studying your article some more and looking at this function I can see the difference now. If nothing else, limiting the number of iterations right up front with the tally table definition at least limits how high the counter can go. The light is coming on slowly!

CREATE FUNCTION [dbo].[itvfFindPosTally]

(

@strInput VARCHAR(8000)

,@delimiter VARCHAR(5)

)

RETURNS TABLE WITH SCHEMABINDING

AS

RETURN

(

WITH E1(N) AS (

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

),

E2(N) AS (SELECT 1 FROM E1 a, E1 b

),

E4(N) AS (SELECT 1 FROM E2 a, E2 b

),

cteTally(N) AS (SELECT 0 UNION ALL

SELECT TOP (DATALENGTH(ISNULL(@strInput,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4

),

findchar (posnum,pos)

AS

(

SELECT

ROW_NUMBER() OVER (ORDER BY t.N) AS posnum

,CHARINDEX(@delimiter,@strInput,t.N) AS pos

FROM

cteTally t

WHERE

(SUBSTRING(@strInput,t.N,1) = @delimiter)

)

SELECT

posnum

,pos

FROM

findchar

WHERE

pos > 0

)