Eralper (7/20/2010)
I remember we have a discussion on splitting function, do you remember too 🙂In fact I could not measure how fast my split functions are.
I can't say I do remember it specifically (sorry) but it is a question that comes up a lot, so I do lose track.
The performance differences may be minimal for a small number of rows (depending on the size of the rows, of course!) but the picture becomes very clear as we give the splitting more work to do. The XML method is the slowest of all the techniques I know, and it also bothers me because it is a misuse of the facility, and may fail for some strings due to illegal XML characters (as far as I remember, anyway).
I have also coded a recursive cte sql split function too. I guess it will not perform very good too. But I enjoy solving a problem using different methods.
Yes the recursive idea is nicer, at least from a theoretical point of view, but it does not perform well, as you say. This is mostly down to the SQL Server engine's implementation of recursion.
By the way thanks for the link. I must say that I did not surprise to see the CLR is performing very well. Because splitting is not actually a database action. That is programming. And I do not expect sql engine to be very fast at row based actions, etc. But I expect it to be good at table level query commands.
I agree with most of that. Set-based solutions will often out-perform even a good SQLCLR solution, but it's a question of picking the right tool for the job at hand.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi