SnowConvert: SQL Server Performance Review Messages¶

Description¶

This warning appears when SnowConvert detects a Common Table Expression (CTE) but has not verified whether the CTE contains recursive operations in its query definition.

Snowflake SQL requires the RECURSIVE keyword for recursive Common Table Expressions (CTEs). Currently, SnowConvert does not automatically detect recursive queries to determine whether the RECURSIVE keyword should be included. This warning notifies you that you may need to manually add the RECURSIVE keyword for recursive CTEs.

Support for this validation may be added in future releases as requirements evolve.

Code Example¶

 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;

 --** 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;

Best Practices¶

• The RECURSIVE keyword is optional and won’t affect your query results. However, it may influence how Snowflake allocates resources during execution. We recommend reviewing Snowflake’s CTE documentation and contacting us if you’d like automatic RECURSIVE keyword addition for compatible CTE queries.

• For additional assistance, please email us at snowconvert-support@snowflake.com