如何在表值函数(TVF)中对视图使用FOR SYSTEM_TIME子句？

错误原因 #

你遇到的报错：

Temporal FOR SYSTEM_TIME clause can only be used with system-versioned tables. 'dbo.AccountView' is not a system-versioned table.

原因在于：虽然你的视图包含系统版本控制表，但它不符合SQL Server对视图使用FOR SYSTEM_TIME的要求——SQL Server仅允许对仅基于单个系统版本控制表的视图，或者SQL Server 2022+中显式定义的系统版本化视图使用该子句。而你的AccountView关联了非系统版本的Account_Temp表，还直接查询了历史表hst_Account，视图本身也不是系统版本化对象，因此无法直接在视图上应用FOR SYSTEM_TIME。

解决方案 #

方案一：修改表值函数，在单个系统版本表上应用FOR SYSTEM_TIME #

直接在每个系统版本控制表上单独使用FOR SYSTEM_TIME AS OF子句，再进行关联查询，替代对视图使用该子句。这样能确保每个系统版本表都正确回溯到指定时间点的数据。修改后的表值函数代码如下：

CREATE FUNCTION [dbo].[GetAsOfDate_AccountView] (@tblID int, @asOfDate datetime2(2))
RETURNS TABLE
AS RETURN
SELECT 
    a.[Account No]
    ,a.[Global ID]
    ,atemp.[Status]
    ,agen.[Name]
    ,agen.[Main Phone]
    ,agen.[General Email]
    ,agen.[Website]
    ,agen.[Company]
    ,a.[Version No]
    ,atemp.[Locked by]
    ,atemp.[Time Locked]
    ,a.[User] 'Modified By'
    ,CONVERT(datetime2(2), a.[Valid From] AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time') 'Time Modified'
    ,ISNULL(ahist.[User], a.[User]) 'Created By'
    ,CONVERT(datetime2(2), ISNULL(ahist.[Valid From], a.[Valid From]) AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time') 'Time Created'
FROM [dbo].[Account] FOR SYSTEM_TIME AS OF @asOfDate a
LEFT JOIN [dbo].[Account_Temp] atemp
    ON a.[Account No] = atemp.[Account No]
LEFT JOIN [dbo].[Account_General] FOR SYSTEM_TIME AS OF @asOfDate agen
    ON a.[Account No] = agen.[Account No]
LEFT JOIN (
    SELECT 
        ahist.*
        ,rn = ROW_NUMBER() OVER (PARTITION BY ahist.[Account No] ORDER BY ahist.[Valid From])
    FROM [dbo].hst_Account ahist
    WHERE ahist.[Valid To] > @asOfDate AND ahist.[Valid From] <= @asOfDate
) ahist
    ON ahist.[Account No] = a.[Account No] AND ahist.rn = 1
WHERE a.[Account No] = @tblID
GO

方案二（仅适用于SQL Server 2022及以上版本）：将视图改造为系统版本化视图 #

如果你的SQL Server版本是2022或更高，可以将视图定义为系统版本化视图，但需要满足两个前提：

1. 视图的所有基表都是系统版本控制表（你需要先将Account_Temp改造为系统版本表）；
2. 视图定义中包含所有基表的Valid From和Valid To列，并定义视图的PERIOD。

不过由于当前视图包含非系统版本表，这个方案的改造成本较高，因此方案一更适合你的场景。

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