As promised, I did some testing (sorry it took this long). This was run from my laptop in a Win2K3 Server VM. The results showed a pretty consistent run time of ~40 ms. Running the script in a loop of 20 times, the max ms were 50 and min was 30. The average was 40.45 and a median of 41. I will note that yes, there is a penalty for the first load of the tally table, which I saw the first time I ran it in tempdb. However, if it's used often, it will probably stay in memory, which it does in our system.

But to be fair, I did a DBCC freeproccache and DBCC dropcleanbuffers between the tests below and did experience the occasional doubling of time you experienced but it wasn't consistent.

Other changes made to your test script:

1) The udf takes nvarchar so I changed chars to nchar to remove the implicit conversion

2) Used datetime2

3) DATEDIFF for time difference calculation

4) Added GO 20 to repeat the script 20 times

declare @i int = 26, @x nvarchar(max) = N'', @d nchar(1) = N' ', @j-2 int;

declare @t table (id tinyint primary key, tokens int, which nvarchar(32), start datetime2, finish datetime2);

--Note: this is going to take a while, so if you want to run this more than once, store this data somewhere...

set @j-2 = @i*@i;

while @j-2 > 0 BEGIN

while @i > 0 BEGIN

set @x = @x + @d +NCHAR(91 - @i);

set @i = @i - 1;

END

set @j-2 = @j-2 - 1;

set @i = 26

END

declare @C int;

update @t set tokens = @C, finish = sysdatetime() where id = 1;

insert into @t (id,which,start) values (2,'udf_StrList2Table',getdate());

select @C = COUNT(*) from ..[udf_StrList2Table] (@x,@d)

update @t set tokens = @C, finish = sysdatetime() where id = 2;

select *, DATEDIFF(millisecond, start, finish) as runtime from @t;

GO 20