I am trying to use the FOR SYSTEM_TIME clause on a view, which contains system-versioned tables, in a table-valued function.
Per Microsoft's documentation, using the FOR SYSTEM_TIME clause when querying a view should be possible: https://learn.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table?view=sql-server-ver16
However, I am getting the following error:
Temporal FOR SYSTEM_TIME clause can only be used with system-versioned tables. 'dbo.AccountView' is not a system-versioned table.
Function, view, and table definitions:
--Account Table
CREATE TABLE [dbo].[Account](
[Account No] [int] IDENTITY(1,1) NOT NULL,
[Global ID] AS ('A'+CONVERT([nvarchar](10),[Account No])) PERSISTED,
[User] [varchar](50) NULL,
[Version No] [tinyint] NULL,
[Version Comment] [varchar](280) NULL,
[Valid From] [datetime2](2) GENERATED ALWAYS AS ROW START NOT NULL,
[Valid To] [datetime2](2) GENERATED ALWAYS AS ROW END NOT NULL,
CONSTRAINT [PK_Account] PRIMARY KEY CLUSTERED
(
[Account No] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
PERIOD FOR SYSTEM_TIME ([Valid From], [Valid To])
) ON [PRIMARY]
WITH
(
SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[hst_Account])
)
GO
ALTER TABLE [dbo].[Account] ADD CONSTRAINT [DF_Account_ValidFrom] DEFAULT (sysutcdatetime()) FOR [Valid From]
GO
ALTER TABLE [dbo].[Account] ADD CONSTRAINT [DF_Account_ValidTo] DEFAULT (CONVERT([datetime2](2),'9999-12-31 23:59:59.99')) FOR [Valid To]
GO
--Account_General Table
CREATE TABLE [dbo].[Account_General](
[Account No] [int] NOT NULL,
[Name] [varchar](100) NULL,
[Main Phone] [varchar](12) NULL,
[General Email] [varchar](100) NULL,
[Website] [varchar](100) NULL,
[Company] [varchar](50) NULL,
[Valid From] [datetime2](2) GENERATED ALWAYS AS ROW START NOT NULL,
[Valid To] [datetime2](2) GENERATED ALWAYS AS ROW END NOT NULL,
CONSTRAINT [PK_Account_General] PRIMARY KEY CLUSTERED
(
[Account No] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
PERIOD FOR SYSTEM_TIME ([Valid From], [Valid To])
) ON [PRIMARY]
WITH
(
SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[hst_Account_General])
)
GO
ALTER TABLE [dbo].[Account_General] ADD CONSTRAINT [DF_Account_General_ValidFrom] DEFAULT (sysutcdatetime()) FOR [Valid From]
GO
ALTER TABLE [dbo].[Account_General] ADD CONSTRAINT [DF_Account_General_ValidTo] DEFAULT (CONVERT([datetime2](2),'9999-12-31 23:59:59.99')) FOR [Valid To]
GO
ALTER TABLE [dbo].[Account_General] WITH CHECK ADD CONSTRAINT [FK_Account_General_AccountNo] FOREIGN KEY([Account No])
REFERENCES [dbo].[Account] ([Account No])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Account_General] CHECK CONSTRAINT [FK_Account_General_AccountNo]
GO
--Account_Temp Table
CREATE TABLE [dbo].[Account_Temp](
[Account No] [int] NOT NULL,
[Status] [varchar](30) NULL,
[Locked By] [varchar](50) NULL,
[Time Locked] [datetime2](2) NULL,
CONSTRAINT [PK_Account_Temp] PRIMARY KEY CLUSTERED
(
[Account No] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Account_Temp] WITH CHECK ADD CONSTRAINT [FK_Account_Temp_AccountNo] FOREIGN KEY([Account No])
REFERENCES [dbo].[Account] ([Account No])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Account_Temp] CHECK CONSTRAINT [FK_Account_Temp_AccountNo]
GO
--AccountView
CREATE VIEW [dbo].[AccountView] AS
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] a
LEFT JOIN [dbo].[Account_Temp] atemp
ON a.[Account No] = atemp.[Account No]
LEFT JOIN [dbo].[Account_General] 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) ahist
ON ahist.[Account No] = a.[Account No] AND ahist.rn = 1
GO
--Table-Valued Function
CREATE FUNCTION [dbo].[GetAsOfDate_AccountView] (@tblID int, @asOfDate datetime2(2))
RETURNS TABLE
AS RETURN
SELECT av.*
FROM [dbo].[AccountView] FOR SYSTEM_TIME AS OF @asOfDate AS av
WHERE av.[Account No] = @tblID
GO
*Edited to add script for view and tables.
As have been mentioned in the comments, and after much trial and error by others to create a minimal reproducible example, you can't use a variable/parameter in a (i)TVF in the FOR SYSTEM_TIME AS OF clause against a object that isn't itself isn't a temporal object. As such, even if the VIEW you are referencing does contain temporal objects you'll get a (some what misleading) error:
Temporal FOR SYSTEM_TIME clause can only be used with system-versioned tables. 'Sandbox.dbo.PartiallyTemporalView' is not a system-versioned table.
Why it works this way, honestly only someone from Microsoft could likely answer. You would probably be best opening a ticket (either via your support plan or via the Azure 365 Feedback SQL Community) with them to raise it as a "bug"; they may give some insight or they might even "fix" the problem (in a future version of SQL Server).
As for a solution, that would be to repeat the definition of the VIEW in your FUNCTION instead, and use FOR SYSTEM_TIME AS OF against the relevant table(s). That appears to just be dbo.Account_General in this case:
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] AS [Modified By],
CONVERT(datetime2(2), a.[Valid From] AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time') AS [Time Modified],
ISNULL(ahist.[User], a.[User]) AS [Created By],
CONVERT(datetime2(2), ISNULL(ahist.[Valid From], a.[Valid From])AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time') AS [Time Created]
FROM [dbo].[Account] 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.*,
ROW_NUMBER() OVER (PARTITION BY ahist.[Account No]
ORDER BY ahist.[Valid From]) AS rn
FROM [dbo].hst_Account ahist) ahist ON ahist.[Account No] = a.[Account No]
AND ahist.rn = 1;
GO