Cannot format temporal table syntax

misaksenmisaksen Posts: 1 New member
I get a "Value cannot be null. Parameter name: Start" when trying to format the following code:

CREATE TABLE TemporalTable
(
ID INT PRIMARY KEY
);
GO

-- Error shows up here if formatting each statement separately
ALTER TABLE dbo.TemporalTable
ADD PERIOD FOR SYSTEM_TIME (SysStartDate, SysEndDate)
, SysStartDate DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL DEFAULT SYSUTCDATETIME()
, SysEndDate DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.99999999');

ALTER TABLE dbo.TemporalTable
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.TemporalTableHistory));

-- Clean up
ALTER TABLE dbo.TemporalTable
SET (SYSTEM_VERSIONING = OFF);
DROP TABLE dbo.TemporalTableHistory;
DROP TABLE dbo.TemporalTable;
Tagged:

Comments

  • Hi @misaksen,

    Thanks for reporting this. We've managed to replicate the behaviour and created a Jira ticket (SP-6357).

    We'll let you know as soon as we have more information.

    However, there is a workaround you could try at the moment. If you change the order of the PERIOD FOR SYSTEM_TIME so it's not the first item after the ADD, formatting should work as expected:
    ALTER TABLE dbo.TemporalTable
    ADD SysStartDate DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL DEFAULT SYSUTCDATETIME()
    , SysEndDate DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.99999999')
    , PERIOD FOR SYSTEM_TIME (SysStartDate, SysEndDate)
    

    Best regards,

    Frederico
    Software Engineer
    Redgate Software
Sign In or Register to comment.