Posting the updated solution for my reference:
IF OBJECT_ID('TEMPDB..#TBL') IS NOT NULL
DROP TABLE #TBL
--Source
CREATE TABLE #TBL (ID INT IDENTITY(1,1) PRIMARY KEY, ID1 INT, ID2 INT, Grp INT)
INSERT INTO #TBL (ID1,ID2)VALUES (100,110),(20,30),(9,10),(7,8),(8,9),(2,3),(10,20),(3,4),(1,2),(21,1),(4,22),(22,23),
(40,50),(50,60),(3,20),(60,70),(70,80),(5,21),(150,200),(300,400),(200,250),(9,121),(1,1000)
--SELECT * FROM #TBL
DECLARE @LVL INT=1
--CREATE INDEXES-----------------------------------------------------
--ALTER TABLE #TBL ADD CONSTRAINT PK_TMP_CHILD PRIMARY KEY(ID)
CREATE NONCLUSTERED INDEX NCI_TMP_Child ON #TBL (ID1)
INCLUDE (ID2, GRP)
/*
CREATE NONCLUSTERED INDEX NCI_TMP_CHILD_COMPOSITE
ON #TBL ([ID2],[GRP]) INCLUDE(ID1)
CREATE NONCLUSTERED INDEX NCI_TMP_CHILD_GRP
ON #TBL ([GRP])
INCLUDE ([ID1],ID2)
*/
-----------------------------------------------------------------------
--UPDATE VALUES WHICH DON'T HAVE A MATCH
UPDATE T
SET GRP = -1
FROM #TBL T
WHERE NOT EXISTS(SELECT 1 FROM #TBL WHERE ID1 = T.ID2)
AND NOT EXISTS(SELECT 1 FROM #TBL WHERE ID2 = T.ID1)
DECLARE @Rowcount INT = 1
DECLARE @CurrentRowcount INT
WHILE EXISTS(SELECT 1 FROM #TBL WHERE GRP IS NULL)
BEGIN
WHILE @Rowcount > 0
BEGIN
UPDATE T1
SET GRP = T2.GRP
FROM #TBL T1
INNER JOIN #TBL T2 ON T1.ID1=T2.ID1
WHERE T1.GRP IS NULL
AND T2.GRP IS NOT NULL
SET @Rowcount = @@rowcount
UPDATE T1
SET GRP = T2.GRP
FROM #TBL T1
INNER JOIN #TBL T2 ON T1.ID1=T2.ID2
WHERE T1.GRP IS NULL
AND T2.GRP IS NOT NULL
SET @CurrentRowcount = @@rowcount
IF @Rowcount =0
SET @Rowcount = @CurrentRowcount
UPDATE T1
SET GRP = T2.GRP
FROM #TBL T1
INNER JOIN #TBL T2 ON T1.ID2=T2.ID1
WHERE T1.GRP IS NULL
AND T2.GRP IS NOT NULL
SET @CurrentRowcount = @@rowcount
IF @Rowcount =0
SET @Rowcount = @CurrentRowcount
UPDATE T1
SET GRP = T2.GRP
FROM #TBL T1
INNER JOIN #TBL T2 ON T1.ID2=T2.ID2
WHERE T1.GRP IS NULL
AND T2.GRP IS NOT NULL
SET @CurrentRowcount = @@rowcount
IF @Rowcount =0
SET @Rowcount = @CurrentRowcount
END
SET @Rowcount = 1
--STARTING FROM THE FIRST MATCHING VALUE TRAVERSE THE HIERARCHY TO FIND ALL VALUES FROM RIGHT TO LEFT
;WITH CTE
AS
(
SELECT TOP 1 T1.ID, TAB.ID1, TAB.ID2,@LVL AS LEVEL
FROM #TBL T1
CROSS APPLY(SELECT *
FROM #TBL WHERE T1.ID1=ID2
AND GRP IS NULL
)TAB
WHERE T1.GRP IS NULL
ORDER BY ID1
UNION ALL
SELECT T.ID, T.ID1,T.ID2, LEVEL
FROM #TBL T
INNER JOIN CTE C ON C.ID2=T.ID1
)
--TRAVERSE THE HIERARCHY TO FIND ALL VALUES FROM LEFT TO RIGHT
,CTE2
AS
(
SELECT ID,ID1,ID2,LEVEL
FROM CTE T1
WHERE LEVEL=@LVL
UNION ALL
SELECT T.ID,T.ID1,T.ID2, LEVEL
FROM #TBL T
INNER JOIN CTE2 C ON C.ID1=T.ID2
)
--UPDATE THE CHILD ITEMS WITH THE ROOT PARENT LEVEL
UPDATE T1
SET Grp=@LVL
FROM #TBL T1
INNER JOIN CTE2 TAB2 ON T1.ID=TAB2.ID --T1.ID1=TAB2.ID1 AND T1.ID2=TAB2.ID2
WHERE T1.Grp IS NULL
--JUST A CHECK FOR INFINITE LOOP AS EACH ROW SHOULD BE MARKED WITH A GROUP NO.
IF NOT EXISTS(SELECT 1 FROM #TBL WHERE GRP = @LVL)
BREAK
SET @LVL = @LVL + 1
END
--UPDATE GROUP NUMBER FOR VALUES WHICH DON'T HAVE A MATCH
UPDATE #TBL
SET @LVL = Grp = @LVL + 1
WHERE GRP = -1
SELECT ID1,ID2,GRP FROM #TBL
--WHERE GRP>0
ORDER BY GRP,ID1,ID2