Sql-server – MS SQL temporal tables not properly

sql servertemporal-tables

I have an existing table and wanted to create temporal tables. I used following queries, it worked in local and dev server. But in QA and PROD the same query created some anonymous temporal table not the one I wrote. What could be the issue?

ALTER TABLE dbo.abc
    ADD StartTime DATETIME2
GO
ALTER TABLE dbo.abc
    ADD EndTime DATETIME2
GO
UPDATE dbo.abc SET StartTime = '19000101 00:00:00.0000000', EndTime = '99991231 23:59:59.9999999'
GO
ALTER TABLE dbo.abc
    ALTER COLUMN StartTime DATETIME2 NOT NULL
GO
ALTER TABLE dbo.abc
    ALTER COLUMN EndTime DATETIME2 NOT NULL
GO
ALTER TABLE dbo.abc
    ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime)
GO
ALTER TABLE dbo.abc
    SET(SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.abc_history, DATA_CONSISTENCY_CHECK = ON))
GO

So instead of creating system versioning for abc_history, it created MSSql_TemporalHistoryFor_<some-random-number>.
I see abc_history table in DB, it is just not system versioned.

select @@version gives "Microsoft SQL Azure (RTM) – 12.0.2000.8 Feb 20 2021 17:51:58 Copyright (C) 2019 Microsoft Corporation "

Best Answer

Do you have a PK in the abc table? Try this=

ALTER TABLE dbo.abc
    ADD StartTime DATETIME2(2) GENERATED ALWAYS AS ROW START NOT NULL
GO

ALTER TABLE dbo.abc
    ADD EndTime DATETIME2(2) GENERATED ALWAYS AS ROW END NOT NULL
GO

ALTER TABLE dbo.abc
    ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime)
GO
ALTER TABLE dbo.abc
    SET(SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.abc_history,     DATA_CONSISTENCY_CHECK = ON))
GO
Related Question