I also found the function to be slow in testing. I had an alternate way of generating the number that was much quicker, but for performance, the best solution I've found so far is to prepopulate a table with the appropriate text for a number and then just join to it. Some sample code for testing follows. It only goes to the thousands, but obviously the approach could be extended further.

I have also had it beaten into my head that, WHENEVER possible, character string manipulation should be done on the application side to keep the load off the SQL Server. Much more scalable.

declare @numwords table(number int, ones varchar(15), tens varchar(15), hundreds varchar(15), thousands varchar(15))

insert into @numwords

select 0,NULL,NULL,NULL,NULL union all

select 1,'One','Ten','One Hundred','One Thousand' union all

select 2,'Two','Twenty','Two Hundred','Two Thousand' union all

select 3,'Three','Thirty','Three Hundred','Three Thousand' union all

select 4,'Four','Forty','Four Hundred','Four Thousand' union all

select 5,'Five','Fifty','Five Hundred','Five Thousand' union all

select 6,'Six','Sixty','Six Hundred','Six Thousand' union all

select 7,'Seven','Seventy','Seven Hundred','Seven Thousand' union all

select 8,'Eight','Eighty','Eight Hundred','Eight Thousand' union all

select 9,'Nine','Ninety','Nine Hundred','NineThousand' union all

select 10,'Nineteen',NULL,NULL,NULL union all

select 11,'Eleven',NULL,NULL,NULL union all

select 12,'Twelve',NULL,NULL,NULL union all

select 13,'Thirteen',NULL,NULL,NULL union all

select 14,'Fourteen',NULL,NULL,NULL union all

select 15,'Fifteen',NULL,NULL,NULL union all

select 16,'Sixteen',NULL,NULL,NULL union all

select 17,'Seventeen',NULL,NULL,NULL union all

select 18,'Eighteen',NULL,NULL,NULL union all

select 19,'Nineteen',NULL,NULL,NULL

set statistics time on;

with expanded as

(select N

,max(case when number = substring(reverse(N),4,1) then thousands else '' end) as thousands

,max(case when number = substring(reverse(N),3,1) then hundreds else '' end) as hundreds

,max(case when right(N,2) not between 11 and 19 and number = substring(reverse(N),2,1) then tens else '' end) as tens

,max(case when right(N,2) not between 11 and 19 and number = right(N,1) then ones

when right(N,2) between 11 and 19 and number = right(N,2) then ones

else '' end) as ones

from @Numwords

cross join dbo.Tally

group by N

)

select N,isnull(thousands+' ','')+isnull(hundreds+' ','')+isnull(tens+' ','')+isnull(ones,'')

from expanded

where N <= 9999

set statistics time off;

-- create a temp table to test lookup

create table #numwords (number int primary key, words varchar(200))

;with expanded as

(select N

,max(case when number = substring(reverse(N),4,1) then thousands else '' end) as thousands

,max(case when number = substring(reverse(N),3,1) then hundreds else '' end) as hundreds

,max(case when right(N,2) not between 11 and 19 and number = substring(reverse(N),2,1) then tens else '' end) as tens

,max(case when right(N,2) not between 11 and 19 and number = right(N,1) then ones

when right(N,2) between 11 and 19 and number = right(N,2) then ones

else '' end) as ones

from @Numwords

cross join dbo.Tally

group by N

)

insert into #numwords

select N,isnull(thousands+' ','')+isnull(hundreds+' ','')+isnull(tens+' ','')+isnull(ones,'')

from expanded

where N <= 9999

--select * from #numwords

set statistics time on;

-- test using join to existing table

select t.N,nw.words

from dbo.tally t

join #numwords nw on t.N = nw.Number

set statistics time off;

-- test join with set of 9999 random numbers

create table #testSet (testnum int)

insert into #testset

select ABS(CHECKSUM(NEWID())) % 9998 + 1

from dbo.tally

where N < 10000

set statistics time on;

-- test using join to existing table

select t.testnum,nw.words

from #testSet t

join #numwords nw on t.testNum = nw.Number

set statistics time off;

set statistics time on;

-- test using the user function from the article

select testnum,dbo.fnSpellInteger(testNum)

from #testSet

set statistics time off;

drop table #numwords

drop table #testSet