SSC-PRF-TS0001

Performance warning - recursion for CTE not checked. Might require a recursive keyword.

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Description

This warning is added to a Common Table Expression (CTE) when SnowConvert did not check for any recursion inside the CTE query definition.

Snowflake SQL supports the RECURSIVE keyword, intended for use along CTE queries that happen to be recursive. SnowConvert currently does not check for that recursion in order to decide if the RECURSIVE keyword should or should not be added to the result, and this warning is shown to make that clear.

Support for this validation might be done in the future as the requirements expand.

Code Example

Input Code:

IN -> SqlServer_01.sql

WITH Sales_CTE (SalesPersonID, NumberOfOrders)  
AS  
(  
    SELECT SalesPersonID, 2  
    FROM Sales.SalesOrderHeader  
    WHERE SalesPersonID IS NOT NULL  
    GROUP BY SalesPersonID  
)  
SELECT 2 AS "Average Sales Per Person"  
FROM Sales_CTE;

Output Code:

OUT -> SqlServer_01.sql

--** SSC-PRF-TS0001 - PERFORMANCE WARNING - RECURSION FOR CTE NOT CHECKED. MIGHT REQUIRE RECURSIVE KEYWORD **
WITH Sales_CTE (
    SalesPersonID,
    NumberOfOrders
) AS
(
    SELECT
        SalesPersonID, 2
    FROM
        Sales.SalesOrderHeader
    WHERE
        SalesPersonID IS NOT NULL
    GROUP BY
        SalesPersonID
)
SELECT 2 AS "Average Sales Per Person"
FROM
    Sales_CTE;

Recommendations

• Not having the RECURSIVE keyword does not impact the result of the query directly, but may impact the way Snowflake uses resources for its execution. We recommend checking Snowflake's features involving CTEs and contacting us if you would like the RECURSIVE keyword to be automatically added to the CTE queries that support it.

• If you need more support, you can email us at [email protected]