Q:

categorize records of table in SQL?

I have a table with increasing records.it contains some columns including id1, id2. I want insert a column to categorize these records in this way:
For example if id1=1 in relate with id2=2 be in one category
If id1=3 in relate with id2=2 all three ids 1, 2, 3 group in same category
Pk      | id1     | id2     | category
--------+---------+---------+-----------
1       | 1111    | 2222    | 1
2       | 2222    | 3333    | 1
3       | 3333    | 1111    | 1
4       | 4444    | 5555    | 1
5       | 2222    | 1111    | 1
6       | 5555    | 1111    | 1
7       | 6666    | 8888    | 2
8       | 7777    | 9999    | 3

And if any new record adds to table it get a group and updates old groups. For example if new record was like below, change the category of 7th row to 1
Pk      | id1     | id2     | category
--------+---------+---------+-----------
7       | 6666    | 8888    | 1
8       | 7777    | 9999    | 3
9       | 8888    | 1111    | 1

or instead of inserting a column in this table, create another table with id and category for realizing each id's category.
By this way, I want understand networks between different ID's.

A:

General graph walking is a bit painful using CTEs -- but possible.  And there really aren't alternatives.
In SQL Server, you can maintain a list of visited nodes.  This prevents infinite recursion.  Unfortunately, this list is stored using a string.
So, this calculates the categories:
with t as (
      select v.*
      from (values (1, 1111, 2222),
                   (2, 2222, 3333),
                   (3, 3333, 1111),
                   (4, 4444, 5555),
                   (5, 2222, 1111),
                   (6, 5555, 1111),
                   (7, 6666, 8888),
                   (8, 7777, 9999)
           ) v(pk, id1, id2)
    ),
    cte as (
     select pk, id1, id1 as id2, convert(varchar(max), concat(',', id1, ',')) as visited
     from t
     union all
     select cte.pk, cte.id1, t.id2, convert(varchar(max), concat(visited, t.id2, ','))
     from cte join
          t
          on cte.id2 = t.id1
     where cte.visited not like concat('%,', t.id2, ',%')  
     union all
     select cte.pk, cte.id1, t.id1, convert(varchar(max), concat(visited, t.id1, ','))
     from cte join
          t
          on cte.id2 = t.id2
     where cte.visited not like concat('%,', t.id1, ',%')  
    )   
select pk, id1, min(id2), dense_rank() over (order by min(id2))
from cte
group by pk, id1;

You can adapt this code to do an update (via a join on the primary key).
You can also incorporate this into a trigger or application to adjust the categories when new edges are added.
However, you should revise your data structure.  You have a graph data structure, so you should have a table of ids and a table of edges.  The categories represent disconnected subgraphs, and should be applied on the nodes not the edges.
Here is a db<>fiddle with the above code.

