• Is everyone running Jeff's original/updated test rig from the article to get these results?

    If so, you might not be testing what you think you are. There is a weakness in the test script that can result in the Csv8K table containing duplicates (i.e. every row is the same). This is related to hiding the NEWID call in the view. In (at least) SQL Server 2012 and 2014, this NEWID value may be cached, resulting in every row being the same.

    It is obviously not a very good test to simply split the string once, cache the results in a table spool, and replay that for each duplicate row from the Csv8K table. This particularly (and unfairly) benefits the non-CLR splitters by the way.

    As a quick hack fix, you can use undocumented trace flag 8690 to prevent caching the NEWID value in this part of the script:



    IF OBJECT_ID(''dbo.Csv8K'',''U'') IS NOT NULL DROP TABLE dbo.Csv8K;

    SELECT *

    INTO dbo.Csv8K

    FROM dbo.CreateCsv8K

    ('+CAST(NumberOfRows AS VARCHAR(10))+', '

    +CAST(NumberOfElements AS VARCHAR(10))+', '

    +CAST(MinElementLength AS VARCHAR(10))+', '

    +CAST(MaxElementLength AS VARCHAR(10))+') OPTION (QUERYTRACEON 8690); --# of Rows, # of Elements, MIN element length, MAX element length

    EXEC dbo.TestEachFunction '+CAST(NumberOfRows AS VARCHAR(10)) +', '

    +CAST(NumberOfElements AS VARCHAR(10))+', '

    +CAST(MinElementLength AS VARCHAR(10))+', '

    +CAST(MaxElementLength AS VARCHAR(10))+';


    FROM cteControl

    Nevertheless, you need to be careful to check the Csv8K table to check the rows are actually different, as intended.

    One more thing. The CLR code isn't fully compiled to native code on creation. The first call to the function may take a full second or more as just-in-time compilation takes place. This also seems unfair, so I add an initial do-nothing split just after the CLR function creation to the test script. This ensures at least most of the CLR is compiled before the main tests start:

    SELECT * FROM dbo.Split(N'A,B,C,D', N',');

    FWIW a test I just ran (on SQL Server 2014 RTM CU1) with the fixes in place produced:


    ¦ SplitterName ¦ TotalDuration ¦ AvgDuration ¦


    ¦ DelimitedSplit8K_LEAD ¦ 19.07600 ¦ 0.389306 ¦

    ¦ Split ¦ 12.35800 ¦ 0.252204 ¦


    BTW, that's CLR Split vs Usman's latest version of DelimitedSplit8K_LEAD.