if object_id('dbo.Split1') is not null
    drop function dbo.Split1
go

create function dbo.Split1(@input varchar(max), @delimiter varchar(50))
returns @result table(value bigint)  
as
begin
    declare @str varchar(20)  
    declare @ind int

    if(@input is not null)  
    begin 
        set @ind = charindex(@delimiter, @input)  
        while @ind > 0  
        begin 
            set @str = substring(@input, 1, @ind - 1)
            set @input = substring(@input, @ind + datalength(@delimiter), len(@input) - @ind)  

            insert into @result values (@str)  
            set @ind = charindex(@delimiter, @input)  
        end  

        set @str = @input  
        insert into @result values (@str)  
    end
    return
end

go

if object_id('dbo.Split2') is not null
    drop function dbo.Split2
go

create function dbo.Split2(@strString varchar(max), @delimiter varchar(50))
returns @result table(value bigint)
as
begin  
    with StrCTE(start, stop) as
    (  
        select cast(1 as bigint), charindex(@delimiter, @strString)  
        union all
        select stop + datalength(@delimiter), charindex(@delimiter, @strString, stop + datalength(@delimiter))
        from StrCTE  
        where stop > 0  
    )  

    insert into @result  
    select substring(@strString, start, case when stop > 0 then stop - start else 2147483647 end) as stringValue  
    from StrCTE
    option (maxrecursion 0)

    return  
end

go

if object_id('dbo.Split3') is not null
    drop function dbo.Split3
go

create function dbo.Split3(@strString varchar(max), @delimiter varchar(50))
returns @result table(value bigint)  
as
begin
    declare @x xml
    select @x = cast('<A>' + replace(@strString, @delimiter, '</A><A>') + '</A>' as xml)

    insert into @result              
    select t.value('.', 'int') as inVal  
    from @x.nodes('/A') as x(t)  
    
    return

end

go

if object_id('dbo.Split4') is not null
    drop function dbo.Split4
go

create function dbo.Split4(@strString varchar(max), @delimiter varchar(50))  
returns @result table(value bigint)
as
begin
    insert into @result(value)  
    select substring(@strString, N, case when charindex(@delimiter, @strString, N) - N < 0 then datalength(@delimiter) + N else charindex(@delimiter, @strString, N) - N end)
    from dbo.Tally
    where N <= datalength(@strString) + datalength(@delimiter) and substring(@delimiter + @strString + @delimiter, N, datalength(@delimiter)) = @delimiter
    
    return
end

go

if object_id('dbo.Split5') is not null
    drop function dbo.Split5
go

create function dbo.Split5(@input nvarchar(max), @delimiter nvarchar(255))
returns table
(
    value nvarchar(4000) NULL
) with execute as caller
as 
external name Demo.UserDefinedFunctions.SplitString_Multi
go

if object_id('dbo.table1') is not null
    drop table dbo.table1
create table dbo.table1(id bigint)

create unique index UIX_Table1_Id on dbo.table1(id)

if object_id('dbo.table2') is not null
    drop table dbo.table2
create table dbo.table2(id int primary key, ids nvarchar(max))

/*** TABLE1 ***/
;with cte as
(
    select * 
    from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) a(n)
)
-- Вставляем 10 000 000 записей в dbo.table1
insert into dbo.table1
select top 10000000 t1.n + t2.n * 10 + t3.n * 100 + t4.n * 1000 + t5.n * 10000 + t6.n * 100000 + t7.n * 1000000
from cte t1, cte t2, cte t3, cte t4, cte t5, cte t6, cte t7
order by 1

/*** TABLE2 ***/
declare @delimiter as char(1) = ',' -- Разделитель списка значений
declare @n int
declare numbers cursor fast_forward for

select *
from (values (500), (1000), (5000), (10000), (50000), (100000), (250000), (500000), (1000000)) a(N)

open numbers 
fetch next from numbers into @n

while @@fetch_status = 0
begin
    -- Создаем набор произвольных элементов равный по кол-ву @n
    insert into dbo.table2(id, ids)
    select @n, stuff(
    (
        select top(@n) @delimiter + cast(id as varchar(20))
        from dbo.table1
        order by row_number() over(order by newid())
        for xml path(''), type
    ).value('.', 'varchar(max)'), 1, 1, '')

    fetch next from numbers into @n
end
close numbers
deallocate numbers

/* 1-5 */
select tt.Value
from dbo.table2 t2 
cross apply dbo.SplitN(t2.ids, ',') tt
where t2.id = K

/* где N = 1 – Цикл, 2 – CTE, 3 – XML, 4 – Таблица, 5 – CLR */

/* 6 */
select j.value
from dbo.table2 t2
cross apply openjson('[' + t2.ids + ']') j
where t2.id = K

/* 7 */
select t1.*
from dbo.Tally t1, dbo.table2 t2
where t2.id = K and ',' + t2.ids + ',' like '%,' + cast(t1.N as varchar(20)) + ',%'
-- или
where t2.id = K and charindex(',' + cast(t1.N as varchar(20)) + ',', ',' + t2.ids + ',') > 0
-- или
where t2.id = K and patindex('%,' + cast(t1.N as varchar(20)) + ',%', ',' + t2.ids + ',') > 0

/* 8 */
select j.value
from dbo.table2 t2
cross apply string_split(t2.ids, ',') j
where t2.id = K

/* где K = 500, 1000, 5000, 10000, 50000, 100000, 250000, 500000, 1000000 */