2

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 "

1
  • 1
    Could it be related to login/user/schema? I'm thinking that perhaps the default schema differs for the users that is executing the SQL, between dev and QA/prod. Another thing thing I'd try is to execute this as a sysadmin in all environments. I don't really have an explanation, but just to rule out these things. Mar 26, 2021 at 9:30

1 Answer 1

1

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
2
  • Yes I do have PK. And why the other query did not work and this would?
    – Akshay
    Mar 26, 2021 at 7:38
  • Did it work? the difference is GENERATED ALWAYS AS ROW START/END in the definition of the DT columns
    – MBuschi
    Mar 26, 2021 at 10:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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