Recursion Strategy

[smpa01]

How can I perform recursive queries currently?

I am getting Recursive CTEs are unsupported in this version of Synapse SQL.

Is Recursion on cards?

[AndyDDC · Accepted solution]

Hi   at the moment there is no support for recursion in CTEs in the roadmap.  This was something that was requested in Synapse Serverless (which the lakehouse sql endpoint and warehouse is based on) but never happened.  What's new and planned for Synapse Data Warehouse in Microsoft Fabric - Microsoft Fabric | Microsoft...

In terms of what you can do for recursion, I write a query in which I join the same table back on itself.  E.G here's an example of basic recursion in an employees table.  This example can be used in the Warehouse, but can also be used in the lakehouse sql endpoint if the tables existed (eg created by spark).

create table employees
(
    empid int,
    empname varchar(50),
    managerid int
);

insert into employees
values (1,'andy',null),(2,'dave',1),(3,'alice',1),(4,'glenda',3)

select
    a.empname as EmployeeName,
    b.empname as ManagerName
from employees a
left join employees b on b.empid = a.managerid 

---------------------------------------------------------------

If my reply has been useful please consider providing kudos

and marking as the solution to help others users

---------------------------------------------------------------

[vicenteajm21212]

Hello @smpa01 and @AndyDDC  muy buenos dias , puedes utilizar , esta opcion:

DECLARE @FechaInicio DATE = 'fechainicio';

DECLARE @FechaFin DATE = 'fechafin';

-- 3Insertar datos en la tabla de calendario usando una tabla de números eficiente

WITH Numeros AS (

    SELECT TOP (DATEDIFF(DAY, @FechaInicio, @FechaFin) + 1)

        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS Num

    FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS X(n)       -- 10 filas

    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS Y(n) -- 10*10 = 100 filas

    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS Z(n) -- 100*10 = 1.000 filas

    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS W(n) -- 1.000*10 = 10.000 filas

    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS V(n) -- 10.000*10 = 100.000 filas (máx)

)

INSERT INTO tabladestinocalendario (FechaClave, Fecha, Año, Mes, NombreMes, Trimestre, SemanaAño)

SELECT

    CAST(FORMAT(DATEADD(DAY, Num, @FechaInicio), 'yyyyMMdd') AS INT) AS FechaClave,

    DATEADD(DAY, Num, @FechaInicio) AS Fecha,

    YEAR(DATEADD(DAY, Num, @FechaInicio)) AS Año,

    MONTH(DATEADD(DAY, Num, @FechaInicio)) AS Mes,

    DATENAME(MONTH, DATEADD(DAY, Num, @FechaInicio)) AS NombreMes,

    DATEPART(QUARTER, DATEADD(DAY, Num, @FechaInicio)) AS Trimestre,

    DATEPART(WEEK, DATEADD(DAY, Num, @FechaInicio)) AS SemanaAño

FROM Numeros;

A mi me funciona de manera eficiente para crear la tabla calendario basandome en set-based

[AndyDDC · Accepted solution]

Hi   at the moment there is no support for recursion in CTEs in the roadmap.  This was something that was requested in Synapse Serverless (which the lakehouse sql endpoint and warehouse is based on) but never happened.  What's new and planned for Synapse Data Warehouse in Microsoft Fabric - Microsoft Fabric | Microsoft...

In terms of what you can do for recursion, I write a query in which I join the same table back on itself.  E.G here's an example of basic recursion in an employees table.  This example can be used in the Warehouse, but can also be used in the lakehouse sql endpoint if the tables existed (eg created by spark).

create table employees
(
    empid int,
    empname varchar(50),
    managerid int
);

insert into employees
values (1,'andy',null),(2,'dave',1),(3,'alice',1),(4,'glenda',3)

select
    a.empname as EmployeeName,
    b.empname as ManagerName
from employees a
left join employees b on b.empid = a.managerid 

---------------------------------------------------------------

If my reply has been useful please consider providing kudos

and marking as the solution to help others users

---------------------------------------------------------------

[smpa01]

I don't think it is easy to come up with an alternative of conventional cte recursion for all scenarios. There are situations which needs both recursive ctes and cursors. The funny part is cte recursion is supported in datamart but not in Fabric artifacts.

I am yet to test if cursor is supported.

TBH they are integral part of data engineering and MS must consider adding support for them in the Fabric elemnets that have sql endpoints.

[AndyDDC]

Btw there is an existing Idea for this https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=be2ad68a-2c28-ef11-8ee8-6045bdb5b6f0

[AndyDDC · Accepted solution]

The reason recursion is supported in Datamart is that its backended by Azure SQL Database while the Lakehouse SQL Endpoint and Warehouse are a new MPP engine (started with Synapse Serverless).  With Fabric, MS have built a SQL engine from scratch with fundamentally different architecture to SQL Server/Azure SQL Database so not everything we expect will be there right now, if at all.