tsql - Is there a way to detect a cycle in Hierarchical Queries in SQL Server? -

in oracle, can use function connect_by_iscycle detect cycle in hierarchical queries. try same in sql server. there way this?

thanks lot!

concatenate records ids / build bitmap based on row_numbers of records , verify each new record against list/bitmap

create table t (id int,pid int) insert t values (1,3),(2,1),(3,2) 

---

list

identify cycles

with cte (id,pid,list,is_cycle)  (     select      id,pid,',' + cast (id varchar(max))  + ',',0            t           id = 1      union      select      t.id,t.pid,cte.list + cast (t.id varchar(10)) +  ',' ,case when cte.list '%,' + cast (t.id varchar(10)) + ',%' 1 else 0 end            cte join t on t.pid = cte.id           cte.is_cycle = 0 ) select      *        cte       is_cycle = 1 

---

id  pid list        is_cycle --  --- ----        -------- 1   3   ,1,2,3,1,   1 

---

traverse thorough graph cycles

with cte (id,pid,list)  (     select      id,pid,',' + cast (id varchar(max))  + ','            t           id = 1      union      select      t.id,t.pid,cte.list + cast (t.id varchar(10)) +  ','             cte join t on t.pid = cte.id           cte.list not '%,' + cast (t.id varchar(10)) + ',%' ) select      *        cte 

---

id  pid list 1   3   ,1, 2   1   ,1,2, 3   2   ,1,2,3, 

bitmap

id should sequence of numbers starting 1.
if necessary generate using row_number.

identify cycles

with cte (id,pid,bitmap,is_cycle)  (     select      id,pid,cast (power(2,id-1) varbinary(max)) ,0            t           id = 1      union      select      t.id,t.pid,cast (cte.bitmap|power(2,t.id-1) varbinary(max)),case when cte.bitmap & power(2,t.id-1) > 0 1 else 0 end            cte join t on t.pid = cte.id           cte.is_cycle = 0 ) select      *        cte       is_cycle = 1 

---

id  pid bitmap      is_cycle 1   3   0x00000007  1 

traverse thorough graph cycles

with cte (id,pid,bitmap)  (     select      id,pid,cast (power(2,id-1) varbinary(max))            t           id = 1      union      select      t.id,t.pid,cast (cte.bitmap|power(2,t.id-1) varbinary(max))            cte join t on t.pid = cte.id           cte.bitmap & power(2,t.id-1) = 0 ) select      *        cte 

---

id  pid bitmap 1   3   0x00000001 2   1   0x00000003 3   2   0x00000007