@sam-3, can you test this one too, just curious, not sure if this helps performance wise..

;with cte1(part,leftover,textid) as

(

select left([text],CHARINDEX(' ',[text])),RIGHT([text],len([text])-CHARINDEX(' ',[text])), id from #Text

union all

select left(leftover,case when CHARINDEX(' ',leftover)=0 then LEN(leftover) else CHARINDEX(' ',leftover) end ),RIGHT(leftover,len(leftover)-case when CHARINDEX(' ',leftover)=0 then len(leftover) else CHARINDEX(' ',leftover) end ),textid from cte1 where leftover!=''

),

cte2(trimpart,id) as

(

select cast(part as varchar(140)),textid from cte1

union all

select cast(replace(trimpart,substring(trimpart,patindex('%[^a-z]%',rtrim(ltrim(trimpart))),1),'') as varchar(140)),id from cte2 where patindex('%[^a-z]%',rtrim(ltrim(trimpart)))!=0

)

select t.id,t.[text],isnull(recurrence,0) from(select id,COUNT(id) as recurrence from(select id,trimpart from cte2 where patindex('%[^a-z]%',rtrim(ltrim(trimpart)))=0 and trimpart in (select [word] from #Words))temp

group by id)temp1

right join

#Text t

on t.id=temp1.id