How to use FOR SYSTEM_TIME clause on a view in a TVF?

Question

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.View' is not a system-versioned table.

Here is my 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

Answer 1

Hi @Leah Adams

Try this:

CREATE FUNCTION [dbo].[GetAsOfDate_AccountView] (@tblID int, @asOfDate datetime2(2))
RETURNS @Return_Tbl TABLE (col1 INT,col2 INT)
AS 
BEGIN
INSERT @Return_Tbl(col1,col2)
SELECT col1,col2
FROM [dbo].[AccountView] FOR SYSTEM_TIME AS OF @asOfDate 
WHERE [Account No] = @tblID
RETURN
END

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

You were doing wrong as you were adding view for it as view is not used for system version so you can't use SYSTEM_TIME with it.

You can use For SYSTEM_TIME to work with query:

``

CREATE TABLE [dbo].[Account_SystemVersioned]
(
[Account No] INT NOT NULL,
[Name] VARCHAR(50) NOT NULL,
[Balance] MONEY NOT NULL
);
INSERT INTO [dbo].[Account_SystemVersioned]
(Account No, Name, Balance)
SELECT Account No, Name, Balance
FROM [dbo].[AccountView];
ALTER TABLE [dbo].[Account_SystemVersioned]
WITH (SYSTEM_VERSIONING = ON)

CREATE TABLE [dbo].[Account_SystemVersioned]

By using the "OUTPUT" clause to create a temporary table:

``

CREATE FUNCTION [dbo].[GetAsOfDate_AccountView] (@tblID int, @asOfDate datetime2(2))
RETURNS TABLE
AS RETURN
SELECT av.*
FROM [dbo].[AccountView] av
OUTPUT av.* INTO #AccountView
WHERE av.[Account No] = @tblID;
SELECT *
FROM #AccountView
FOR SYSTEM_TIME AS OF @asOfDate;
DROP TABLE #AccountView;

CREATE FUNCTION [dbo].[GetAsOfDate_AccountView] (@tblID int, @asOfDate datetime2(2))

I hope this would help.

``