IF OBJECT_ID('tempdb..#T') IS NOT NULL 
DROP TABLE #T;

WITH A AS 
(	
	SELECT RN = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM master..spt_values
), B AS 
(
	SELECT RN = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) 
	FROM A A1, A A2
)


SELECT PARENT, CHILD
INTO #T 
FROM 
(
	SELECT 
		PARENT, CHILD
	FROM 
	(
		SELECT DISTINCT 
			--RN, RN % 9 + 1 AS PARENT, RN % 11 + 1 AS CHILD -- много вариантов 
			--RN, RN % 7 + 1 AS PARENT, RN % 9 + 1 AS CHILD -- средне вариантов 
			RN % 5 + 1 AS PARENT, RN % 7 + 1 AS CHILD -- мало вариантов 
		FROM B
	) C
	WHERE CHILD <> PARENT
) D
GROUP BY PARENT, CHILD
;

WITH T AS 
(
	SELECT 
		PARENT AS Start, 
		PARENT AS Parent, 
		CHILD AS Child, 
		CHAIN = CAST(CONCAT('-', PARENT, '-', CHILD, '-') AS VARCHAR(1024)),
		lvl	  =1
	FROM #T S0
	UNION ALL 
	SELECT 
		S1.Start, 
		S2.PARENT AS Parent, 
		S2.CHILD AS Child, 
		CHAIN = CAST(CONCAT(S1.CHAIN, S2.CHILD, '-') AS VARCHAR(1024)),
		lvl   =lvl+1
	FROM T S1
	INNER JOIN #T S2 ON S1.Child = S2.PARENT
	WHERE CHARINDEX(CONCAT('-', S2.CHILD, '-'), S1.CHAIN) = 0
)

IF OBJECT_ID('tempdb..#T') IS NOT NULL 
DROP TABLE #T;

WITH A AS 
(	
	SELECT RN = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM master..spt_values
), B AS 
(
	SELECT RN = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) 
	FROM A A1, A A2
)


SELECT PARENT, CHILD
INTO #T 
FROM 
(
	SELECT 
		PARENT, CHILD
	FROM 
	(
		SELECT DISTINCT 
			--RN, RN % 9 + 1 AS PARENT, RN % 11 + 1 AS CHILD -- много вариантов 
			--RN, RN % 7 + 1 AS PARENT, RN % 9 + 1 AS CHILD -- средне вариантов 
			RN % 5 + 1 AS PARENT, RN % 7 + 1 AS CHILD -- мало вариантов 
		FROM B
	) C
	WHERE CHILD <> PARENT
) D
GROUP BY PARENT, CHILD
;

use tempdb;
set ansi_nulls, quoted_identifier, xact_abort on;
go

IF OBJECT_ID('tempdb..#T') IS NOT NULL 
DROP TABLE #T;

WITH A AS 
(	
	SELECT RN = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM master..spt_values
), B AS 
(
	SELECT RN = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) 
	FROM A A1, A A2
)
SELECT PARENT, CHILD
INTO #T 
FROM 
(
	SELECT 
		PARENT, CHILD
	FROM 
	(
		SELECT DISTINCT 
			--RN, RN % 9 + 1 AS PARENT, RN % 11 + 1 AS CHILD -- много вариантов 
			--RN, RN % 7 + 1 AS PARENT, RN % 9 + 1 AS CHILD -- средне вариантов 
			RN % 5 + 1 AS PARENT, RN % 7 + 1 AS CHILD -- мало вариантов 
		FROM B
	) C
	WHERE CHILD <> PARENT
) D
GROUP BY PARENT, CHILD;
go

create function dbo.fnSetBit
(
 @b binary(8000),
 @bit int
)
returns table
as
return (
 select
  cast(stuff(@b, a.[byte], 1, c.b) as binary(8000)) as result
 from
  (select @bit / 8 + 1, @bit % 8) a([byte], [bit]) cross apply
  (select substring(@b, a.[byte], 1)) b(v) cross apply
  (select cast(cast(b.v as tinyint) | case a.bit when 0 then 1 when 1 then 2 when 2 then 4 when 3 then 8 when 4 then 16 when 0 then 1 when 5 then 32 when 6 then 64 when 7 then 128 end as binary(1))) c(b)
);
go

create procedure [filtering by xml]
as
begin

 declare  @Start int, @Parent int, @Child int, @Chain varchar(8000);

 WITH T AS 
 (
	SELECT 
		PARENT AS Start, 
		PARENT AS Parent, 
		CHILD AS Child, 
		CHAIN = CAST(CONCAT('<a>', PARENT, '</a><a>', CHILD, '</a>') AS VARCHAR(8000))
	FROM #T S0
	UNION ALL 
	SELECT 
		S1.Start, 
		S2.PARENT AS Parent, 
		S2.CHILD AS Child, 
		CHAIN = CAST(CONCAT(S1.CHAIN, '<a>', S2.CHILD, '</a>') AS VARCHAR(8000)) 
	FROM T S1
	INNER JOIN #T S2 ON S1.Child = S2.PARENT
	WHERE CHARINDEX(CONCAT('<a>', S2.CHILD, '</a>'), S1.CHAIN) = 0
 ),
 x as
 (
  SELECT
   t.*,
   row_number() over (partition by a.chain_ordered order by 1/0) as rn
  FROM T t cross apply
  (select cast(cast(CHAIN as xml).query('for $a in a order by xs:integer($a/text()[1]) return concat("(", $a/text()[1], ")")') as varchar(8000))) a(chain_ordered)
 )
 select
  @Start = Start, @Parent = Parent, @Child = Child, @Chain = CHAIN
 from
  x
 where
  rn = 1
 option
  (maxdop 1);
end;
go

create procedure [filtering by bitmask]
as
begin

 declare  @Start int, @Parent int, @Child int, @Chain varchar(8000);

 WITH T AS 
 (
	SELECT 
		PARENT AS Start, 
		PARENT AS Parent, 
		CHILD AS Child, 
		CHAIN = CAST(CONCAT('-', PARENT, '-', CHILD, '-') AS VARCHAR(1024)),
        b.result as bits
	FROM #T S0 cross apply dbo.fnSetBit(cast(0 as binary(8000)), PARENT) b
	UNION ALL 
	SELECT 
		S1.Start, 
		S2.PARENT AS Parent, 
		S2.CHILD AS Child, 
		CHAIN = CAST(CONCAT(S1.CHAIN, S2.CHILD, '-') AS VARCHAR(1024)),
        b.result
	FROM T S1
	INNER JOIN #T S2 ON S1.Child = S2.PARENT
    cross apply dbo.fnSetBit(s1.bits, S2.CHILD) b
	WHERE CHARINDEX(CONCAT('-', S2.CHILD, '-'), S1.CHAIN) = 0
 ),
 x as
 (
  SELECT
   t.*,
   row_number() over (partition by t.bits order by 1/0) as rn
  FROM T t
 )
 select
  @Start = Start, @Parent = Parent, @Child = Child, @Chain = CHAIN
 from
  x
 where
  rn = 1
 option
  (maxdop 1);
end;
go

create procedure [filtering by exists]
as
begin

 declare  @Start int, @Parent int, @Child int, @Chain varchar(8000);

 WITH T AS 
 (
	SELECT 
		PARENT AS Start, 
		PARENT AS Parent, 
		CHILD AS Child, 
		CHAIN = CAST(CONCAT('-', PARENT, '-', CHILD, '-') AS VARCHAR(1024)),
		lvl	  =1
	FROM #T S0
	UNION ALL 
	SELECT 
		S1.Start, 
		S2.PARENT AS Parent, 
		S2.CHILD AS Child, 
		CHAIN = CAST(CONCAT(S1.CHAIN, S2.CHILD, '-') AS VARCHAR(1024)),
        lvl = lvl + 1
	FROM T S1
	INNER JOIN #T S2 ON S1.Child = S2.PARENT
	WHERE CHARINDEX(CONCAT('-', S2.CHILD, '-'), S1.CHAIN) = 0
 )
 select
  @Start = Start, @Parent = Parent, @Child = Child, @Chain = CHAIN
 from
  T
 where
  not exists (select 1 from T t1 where t.lvl = t1.lvl and  t.CHAIN > t1.CHAIN  
                   and not exists (select value from string_split(t.CHAIN,'-') except select value from string_split(t1.CHAIN,'-') ))
 option
  (maxdop 1);
end;
go

create procedure [filtering in while]
as
begin

	declare  @Start int, @Parent int, @Child int, @Chain varchar(max);
 
	declare @lvl integer =0;
	create table #Result (Start integer, Parent integer, Child integer, CHAIN varchar(1024), LVL integer, chain_ordered varchar(1024));

	---------------------------------------------------------------------------------------
	set @lvl = @lvl + 1;
	--
	insert into #Result 
		(Start, Parent, Child, CHAIN, LVL, chain_ordered)
	SELECT 
		PARENT AS Start, 
		PARENT AS Parent, 
		CHILD AS Child, 
		CHAIN = a.CHAIN,
		@lvl,
		b.chain_ordered
	FROM #T S0
	cross apply (select CAST(CONCAT('<a>', S0.PARENT, '</a><a>', S0.CHILD, '</a>') AS VARCHAR(1024)) as CHAIN) a
	cross apply (select cast(cast(a.CHAIN as xml).query('for $a in a order by xs:integer($a/text()[1]) return concat("(", $a/text()[1], ")")') as varchar(8000))) b(chain_ordered);

	--
	with cte as (
		select row_number()over(partition by /*Child,*/ chain_ordered order by CHAIN) as rn from #Result where LVL = @lvl) -- partition by Child, chain_ordered если граф неполносвязный
	delete from cte where rn > 1;

	--	
	while 1=1
	begin
		set @lvl = @lvl + 1;
		--
		insert into #Result 
			(Start, Parent, Child, CHAIN, LVL, chain_ordered)
		SELECT 
			S1.Start, 
			S2.PARENT AS Parent, 
			S2.CHILD AS Child, 
			CHAIN = a.CHAIN,
			@lvl,
			b.chain_ordered
		FROM #Result S1
		INNER JOIN #T S2 ON S1.Child = S2.PARENT
		cross apply (select CAST(CONCAT(S1.CHAIN, '<a>', S2.CHILD, '</a>') AS VARCHAR(1024)) as CHAIN) a
		cross apply (select cast(cast(a.CHAIN as xml).query('for $a in a order by xs:integer($a/text()[1]) return concat("(", $a/text()[1], ")")') as varchar(8000))) b(chain_ordered)
		WHERE S1.LVL = @lvl-1 and CHARINDEX(CONCAT('<a>', S2.CHILD, '</a>'), S1.CHAIN) = 0
		--
		if @@ROWCOUNT = 0 break;
		--
		with cte as (
			select row_number()over(partition by /*Child,*/ chain_ordered order by CHAIN) as rn from #Result where LVL = @lvl) -- partition by Child, chain_ordered если граф неполносвязный
		delete from cte where rn > 1;
	end

	--
	;with cte as (
		select row_number()over(partition by chain_ordered order by CHAIN) as rn from #Result where LVL = @lvl)
	delete from cte where rn > 1;

	--	
	select 
		@Start = Start, @Parent = Parent, @Child = Child, @Chain = CHAIN 
	from 
		#Result
	option 
		(maxdop 1);
end;
go

declare @c int = 5
while @c > 0
 begin
  exec dbo.[filtering by xml];
  exec dbo.[filtering by bitmask];
  exec dbo.[filtering by exists];
  exec dbo.[filtering in while];
  set @c -= 1;
 end;

select
 p.name, cast(ps.total_elapsed_time as float) / ps.execution_count / 1000
from
 sys.dm_exec_procedure_stats ps join
 (values (N'filtering by xml'), (N'filtering by bitmask'), (N'filtering by exists'), (N'filtering in while')) p(name) on ps.database_id = db_id() and ps.object_id = object_id(p.name, 'P')
order by
 2;
go

drop function dbo.fnSetBit;
drop procedure dbo.[filtering by xml], dbo.[filtering by bitmask], dbo.[filtering by exists], dbo.[filtering in while];
go