Recursive CTE: Work around

Question

Hello All,
We are in process on migrating from on Prem server o Azure Synapse.
We see that Synapse does not support Recursive CTE.
So is using loop the only option for it because looping is degrading the performance.

Answer 1

Hello Nandan ,

Thanks for the question and nice to be in touch again .

If we know the number of levels, you can do this:

IF OBJECT_ID(N'dbo.RecurseTest') IS NOT NULL DROP TABLE dbo.RecurseTest;  
GO  
CREATE TABLE dbo.RecurseTest (  
    Id BIGINT NOT NULL,  
    EmployeeName VARCHAR(255) NOT NULL,  
    ParentId BIGINT NULL  
)  

insert into dbo.RecurseTest values(1, 'Emp1', 0);  
insert into dbo.RecurseTest values(2, 'Emp2', 1);  
insert into dbo.RecurseTest values(3, 'Emp3', 1);  
insert into dbo.RecurseTest values(4, 'Emp4', 2);  
insert into dbo.RecurseTest values(5, 'Emp5', 2);  
insert into dbo.RecurseTest values(6, 'Emp6', 3);  

Please find the full query attached .

Thanks Himanshu

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members

14060-sqlquery.txt