Cannot format temporal table syntax

misaksen

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;

FredericoJ

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