You do realize since your routine is a multi-statement TVF that the Quesry Optimizer will always treat the table returned y this function as if it has only one row regardless of the nuumber of rows actually returned?

This means if your function is returning more than a few hundred rows you won't be getting an optimal execution plan.

I discovered this with some AsOF multi-statement TVF's I developed. I had a query linking seven of this TVF together, and after 30 minutes, cancelled the query and modified (actually drop and create) the TVF from multi-statement to in-line TVF. Amazingly, the query then returned in four minutes.

I have to agree with Paul White, here we go with the discussions regarding what is the best string splitting functions. The use of a recursive CTE, however, is not very scalable. If you don't have a CLR TVF, which Paul indicates is the best, the next best is one based on a tally table. I have a function that actually contains its own tally table should you not have one.