2

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.

1
  • 1
    Welcome to stack. Did you get an error? Can you add more information to your question? Nov 6 '20 at 16:23
5

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.