It is unfortunate that this that there are some seemingly inexplicable limitations in what you can do with T-SQL.

For example, I wondered what would happen if we were able to...

I got the results of 11% by comparing the total time to run all the tests.

Note. Some tests had bigger differences than others, and some even had the Delimited8K faster....

OK, I ran the splitter tests.

I had to modify the BetterSplit a little: Switched to varchar(8000) instead of nvarchar(max) for list parameter. This needed to get good comparison between other...

How about this? I'd be curious to see results of performance testing with this.

CREATE FUNCTION [dbo].[BetterSplit]

(

@L NVARCHAR(MAX)

,@D NVARCHAR(100)

)

RETURNS TABLE WITH SCHEMABINDING AS

RETURN

WITH

--A,B,C,D used to generate large number of rows for...

Yes, Thanks for the test. I fixed the problem noted. Had to use DATALENGTH throughout instead of LEN to handle trailing spaces.

SELECT * FROM [dbo].[BetterSplit] ('the; quick; ...

I made a few improvements: handles space delimiters now. Also added DISTINCT so you can use in JOINS

CREATE FUNCTION [dbo].[BetterSplit]

(@L NVARCHAR(MAX),@D NVARCHAR(100))

RETURNS TABLE AS RETURN

WITH A(N)AS(SELECT 1UNION ALL SELECT 1UNION...

Thanks Jeff,

I think you forgot to add the link in your quote.

Thanks for your post. I wonder if you considered using the LEAD function to improve your split. The LEAD function is significantly faster than CHARINDEX. Below is a function that...