Hey again Flo,

You might like to also try this with a CLR TVF.

I tried it on your sample and got:

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table '#source'. Scan count 1, logical reads 77, physical reads 0, read-ahead reads 0, lob logical reads 3803, lob physical reads 0, lob read-ahead reads 2381.

SQL Server Execution Times:

CPU time = 1422 ms, elapsed time = 1940 ms.

A real C# dev will be able to improve my attempt at the code:

(the code tags have broken a couple of things 'AddAdd' and a cast, but hopefully you will get the gist...)

public partial class UserDefinedFunctions

{

[Microsoft.SqlServer.Server.SqlFunction(

FillRowMethodName = "FillRow",

DataAccess = DataAccessKind.None,

SystemDataAccess = SystemDataAccessKind.None,

Name = "tfn_Split",

TableDefinition = "row_id int, result nvarchar(max)")]

public static IEnumerable tfn_Split

(

[SqlFacet(IsNullable = false, MaxSize = -1, IsFixedLength = false)]

SqlString StringToSplit,

[SqlFacet(IsNullable = false, MaxSize = 4000, IsFixedLength = false)]

SqlString Delimiter

)

{

string toSplit = StringToSplit.Value;

string delimeter = Delimiter.Value;

string[] items = toSplit.Split(new string[] { delimeter }, StringSplitOptions.RemoveEmptyEntries);

Dictionary (items.Length);

for (int i = 0; i < items.Length; i++)

{

results.Add(i + 1, items);

}

return results;

}

public static void FillRow(Object obj, out SqlInt32 row_id, out SqlString result)

{

KeyValuePair )obj;

row_id = new SqlInt32(kvp.Key);

result = new SqlString(kvp.Value);

}

};

The CLR version runs fastest on my laptop, has the simplest plan (obviously) and starts to return results immediately, which is nice.

For completeness, this is the SQL:

SELECTS.row_id, TVF.row_id, TVF.result

FROM#source AS S

CROSS

APPLYdbo.tfn_Split(S.definition, CHAR(13) + CHAR(10)) AS TVF

ORDERBY

S.row_id, TVF.row_id;

Cheers,

Paul