如何在Synapse无服务器SQL的CTE中使用表值函数输出？

问题背景 #

我有一个名为fnDimDate(startDate, endDate)的表值函数，需要将其输出用于后续数据转换，于是创建了CTE获取日期范围：

WITH CTE_get_dates_between_first_and_last
AS (
    SELECT [Date]
    FROM [dbo].[fnDimDate](@FirstDate, @LastDate)
)

但在关联无服务器视图时执行以下代码：

,CTE_join_together
AS(
    SELECT data.Title,
           dates.[Date]
    FROM [some].[ServerlessView] AS data
    LEFT JOIN CTE_get_dates_between_first_and_last AS dates 
    ON dates.[Date] >= sustainability.DateFrom AND dates.[Date] <= sustainability.DateTo
)

出现错误：

The query references an object that is not supported in distributed processing mode.

需要在无需物理存储函数输出的前提下解决这个问题。

可行解决方案 #

方案1：将多语句表值函数（MS TVF）改为内联表值函数（ITVF） #

Synapse无服务器SQL池对内联表值函数的支持更友好，它能被查询优化器推送到分布式执行环境，而多语句表值函数会被限制在本地执行，导致与分布式视图关联时冲突。

假设原函数是多语句类型，改成内联版本的示例：

CREATE OR ALTER FUNCTION dbo.fnDimDate(@startDate DATE, @endDate DATE)
RETURNS TABLE
AS
RETURN
(
    -- 替换为原函数生成日期范围的核心逻辑，以下是通用日期生成示例
    SELECT DATEADD(DAY, n, @startDate) AS [Date]
    FROM (
        SELECT TOP(DATEDIFF(DAY, @startDate, @endDate) + 1) 
               ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS n
        FROM sys.all_columns ac1
        CROSS JOIN sys.all_columns ac2
    ) AS numbers
    WHERE DATEADD(DAY, n, @startDate) <= @endDate
)

修改后保留原CTE写法即可正常关联无服务器视图。

方案2：直接在CTE中嵌入日期生成逻辑，跳过自定义函数 #

如果无法修改原函数，可直接在CTE中生成日期范围，利用Synapse无服务器支持的GENERATE_SERIES函数：

WITH CTE_get_dates_between_first_and_last
AS (
    SELECT DATEADD(DAY, value, @FirstDate) AS [Date]
    FROM GENERATE_SERIES(0, DATEDIFF(DAY, @FirstDate, @LastDate))
)

用该CTE替代原调用函数的CTE，再与无服务器视图关联即可。

方案3：用OPENROWSET包装函数调用（特定场景适用） #

若必须保留原函数，可尝试用OPENROWSET将函数输出转换为分布式可访问的数据集：

WITH CTE_get_dates_between_first_and_last
AS (
    SELECT [Date]
    FROM OPENROWSET(
        'SQLNCLI',
        'Server=.;Trusted_Connection=yes;',
        'SELECT [Date] FROM dbo.fnDimDate(''' + CONVERT(VARCHAR, @FirstDate, 23) + ''', ''' + CONVERT(VARCHAR, @LastDate, 23) + ''')'
    )
)

注意：此方法需确保连接字符串配置正确，性能通常弱于前两种方案。

内容的提问来源于stack exchange，提问作者Jem