Temporal table syntax in a view

Question

Consider the following situation. I have a table "test" which is system versioned. I also have a table containing the last time I checked for modified rows in that table.

CREATE TABLE [Delta]
(
    Tablename NVARCHAR(50) NOT NULL,
    Proccess UNIQUEIDENTIFIER NOT NULL,
    LastDelta DATETIME2 NOT NULL
)

I now want to create a view that does this query

Fake syntax
SELECT * 
FROM [test]
FOR SYSTEM_TIME AS OF (select LastDelta from Delta where TableName = 'Test')

Are the only options to supply a datetime a fixed value or parameter? A parameter won't be usefull with a view and a fixed value doesn't help either.

I could emulate the effect of "for system_time as of" with my own query, but I would like to know if there is a way I missed to do it as shown under "fake syntax" or similar.

Answer 1

Use a table valued function (TVF).

CREATE FUNCTION dbo.TVF_func (@_timestamp DATETIME2)
RETURNS TABLE AS
RETURN
SELECT T.*
  FROM [tests]
  FOR SYSTEM_TIME AS OF @_timestamp T;

The TVF can then be used anywhere a view can be used.

SELECT * 
  FROM dbo.TVF_func('2020-10-31') T 
  JOIN [etc...]

For views with complex logic where you typically select a subset of the output based on a date variable, you can get a massive speed boost from rewriting the view as a TVF instead of SELECT * FROM view V WHERE V.date = @date.