use tempdb;
go

if object_id('dbo.fn_splitter', 'IF') is not null
 drop function dbo.fn_splitter;
go

create FUNCTION [dbo].[fn_splitter]( @String NVARCHAR(4000), @ch char )
RETURNS TABLE 
WITH SCHEMABINDING
AS
RETURN 
(
    WITH Split( stpos, endpos ) 
    AS(
        SELECT 0          AS stpos, CHARINDEX(@ch, @String)              AS endpos
        UNION ALL
        SELECT endpos + 1 AS stpos, CHARINDEX(@ch, @String, endpos + 1 ) AS endpos 
          FROM Split
          WHERE endpos > 0
    )
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS Id,
           LTRIM(RTRIM(SUBSTRING( @String, stpos, COALESCE(NULLIF(endpos, 0), LEN(@String) + 1) - stpos))) AS Item
      FROM Split
)
GO

/************************/
/* Settings of the Test */
/************************/
declare
 @s nvarchar(max),
 @pl int = 30,
 @pc int = 5,
 @rc int = 100000;
/************************/

select
 @s = stuff((
  select top (@pc) ',' + right(replicate('0', @pl) + cast(row_number() over (order by (select 1)) as nvarchar(30)), @pl) from (select a.number from master.dbo.spt_values a cross join master.dbo.spt_values b) t for xml path(''), type
 ).value('.', 'nvarchar(max)'), 1, 1, '');

if object_id('tempdb..#t', 'U') is not null
 drop table #t;

select top(@rc)
 row_number() over (order by (select 1)) as n, @s as s
into
 #t
from
 master.dbo.spt_values a cross join
 master.dbo.spt_values b;
go

if object_id('dbo.t1', 'U') is not null
 drop table dbo.t1;

if object_id('dbo.t2', 'U') is not null
 drop table dbo.t2;

if object_id('dbo.t3', 'U') is not null
 drop table dbo.t3;

if object_id('dbo.t4', 'U') is not null
 drop table dbo.t4;
go

set statistics xml, time on;
go

select t.n, a.Item into dbo.t1 from #t t cross apply dbo.fn_splitter(t.s, ',') a option (querytraceon 8649);
select t.n, a.Item into dbo.t2 from #t t cross apply dbo.fn_splitter(t.s, ',') a option (maxdop 1);

select t.n, a.Value into dbo.t3 from #t t cross apply string_split(t.s, ',') a option (querytraceon 8649);
select t.n, a.Value into dbo.t4 from #t t cross apply string_split(t.s, ',') a option (maxdop 1);
go

set statistics xml, time off;
go

drop function dbo.fn_splitter;
drop table dbo.t1, dbo.t2, dbo.t3, dbo.t4;
go