Jeff Moden (5/10/2012)
I did miss that. Thank you for the update.Looking back in this thread and switching gears a bit, the new improved splitter article did come out. An hour after it came out, someone came up with a 20% improvement even to the (significant) improvements that I made. I pretty much left the original article as it was because it showed how I came to making my improvemments. The extra 20% functions are located in the "Resources" link near the end of the article. Here's the link to the article.
I know that particular splitter article as improvement was my own contribution after somone already brought a 10% boost.
And now we switched to the subject of the T-SQL string splitter, you will find great interst in this blog entry from Paul White:
http://sqlblog.com/blogs/paul_white/archive/2012/05/03/parallel-row-goals-gone-rogue.aspx
There is a bug of some sort in 2008 and 2008 SE (I am not sure about 2012) that causes runaway generation of rows by the CTE. The bigger the number of rows the CTE can deliver, the bigger the problem becomes. Lucky for us, the splitter was dedicated to just 8000 rows to support varchar fully and 20*20*20 using cross joins would suffice and limit the performance damage this worst case issue brings.
Due to this bug I don't hold my breath for an T-SQL based varchar(max) anymore as even occasional problems could cause the runtimes to spike unacceptably. Using .NET code for this particular situation seems the reliable way to go, without good T-SQL alternative to fall back on. Unless you found a breaktrough of some sort of course 🙂
Kind regards,
Peter de Heer