Updating temporal table history

Question

I'm having a problem updating rows in a temporal history table in MS SQL Server 2016.

From the documentation, the steps that should be needed are as follows:

1. Disable system versioning
2. Modify the history table
3. Enable system versioning again

I tried creating a procedure that does this, but got this error:

Msg 13561, Level 16, State 1, Line 23 Cannot update rows in a temporal history table 'db.dbo.FooHistory'.

Here is my SQL:

CREATE TABLE Foo(
    id int primary key not null
    , title nvarchar(50) not null
    , startTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL
    , endTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL
    , PERIOD FOR SYSTEM_TIME (startTime, endTime) )

ALTER TABLE Foo
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.FooHistory));

GO

CREATE PROCEDURE [dbo].[UpdateFooHistory] AS
  BEGIN
    SET NOCOUNT ON

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    BEGIN TRANSACTION;
    ALTER TABLE dbo.Foo SET (SYSTEM_VERSIONING = OFF);      

    UPDATE dbo.FooHistory
    SET title = 'Foo';

    ALTER TABLE dbo.Foo SET (SYSTEM_VERSIONING = ON (
      HISTORY_TABLE = dbo.FooHistory,
      DATA_CONSISTENCY_CHECK = ON
    ));

    COMMIT TRANSACTION

    RETURN 0
  END
GO

It seems like SQL Server is checking if a table is temporal at "compile time" rather than at runtime. Is this true? Is there a way to work around it?

Answer 1

Change the UPDATE statement to this, and it will let you create the procedure:

EXEC(N'UPDATE dbo.FooHistory SET title = ''Foo''');

Answer 2

With SQL Server 2019 it does not work any more (neither with exec nor with sp_executesql). You need to use a workaround.

In order to prevent any other user to change something while the versioning is off first start a serializable transaction:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; 
BEGIN TRANSACTION;

Temporarily disable SYSTEM_VERSIONING on the table:

ALTER TABLE Foo SET (SYSTEM_VERSIONING = OFF);

Modify the history table:

UPDATE dbo.FooHistory SET title = 'Foo';

Re-enable the versioning:

ALTER TABLE Foo SET (SYSTEM_VERSIONING = ON (  HISTORY_TABLE = dbo.FooHistory, DATA_CONSISTENCY_CHECK = ON));

Commit the transaction:

COMMIT TRANSACTION;