View Level Number on Recursive Table SQL

Question

I have the following table:

--------------------------------------------
ID      ParentID     Item  
--------------------------------------------
1                    root
2       1            AA
3       1            BB
4       1            CC
5       1            DD
6       2            A1
7       6            A11
ff.

I want to have the following result:

ID      ParentID     Item         Level
---------------------------------------------
1                    root         0
2       1            AA           1
3       1            BB           1
4       1            CC           1
5       1            DD           1
6       2            A1           2
7       6            A11          3
ff.

1. What is the best idea to create the new column level? Is create a new column and add a formula or something like computed or maybe function?
2. How can I achieve that on t-sql?

Answer 1

You would use a recursive CTE:

with cte as (
      select t.id, t.parentid, t.item, 0 as lvl
      from t
      where parentid is null
      union all
      select t.id, t.parentid, t.item, cte.lvl + 1 as lvl
      from t join
           cte
           on t.parentid = cte.id
     )
select *
from cte;

Storing this data in the table is . . . cumbersome, because you need to keep it updated. You might want to just calculate it on-the-fly when you need it.