This is what fascinates me whenever working with SQL - there are always dozens of alternatives 😉

I analyzed recently some ways to parse a string to a table for performance - with long strings the methods tend to become quite expensive.

The winner from CPU point of view was the XML method, this goes like this:

declare @strDataSet as varchar(max);

declare @delimiter as varchar(10);

set @strDataSet='A,B,C,D,E';

set @delimiter =',';

declare @xml as xml;

set @xml = cast(('<X>'+replace(@strDataSet,@delimiter ,'</X><X>')+'</X>') as xml);

select N.value('.', 'sysname') as x from @xml.nodes('X') as T(N);

Here some performance results for a string with 2361 delimited elements...

1: XML (see above) - SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 25 ms.

(2361 row(s) affected)

2: #Temp (Bernabe's way) - SQL Server Execution Times:

CPU time = 47 ms, elapsed time = 278 ms.

(2361 row(s) affected)

3: CTE - SQL Server Execution Times:

CPU time = 203 ms, elapsed time = 467 ms.

Msg 530, Level 16, State 1, Line 19

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

As you see, CTE has limitations and is rather expensive - but that XML is that much faster than stirng functions in a loop, was a surprise to me...

Cheers, Richard.