5

Microsoft docs currently give an example of how to enable temporal tables on existing tables at ALTER TABLE, System Versioning examples: A. Add system versioning to existing tables

Using the syntax there but specifying a constant default, I have:

ALTER TABLE InsurancePolicy
ADD PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo),
ValidFrom datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL
--    DEFAULT SYSUTCDATETIME(), /* default specified in the docs */
    DEFAULT CONVERT(DATETIME2, '2023-08-14') /* use a constant default */
ValidTo datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL
    DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.99999999') ;

When I run this statement, I can observe an event SP:StatementStarting with TextData: SELECT [ValidFrom],[ValidTo] FROM [dbo].[InsurancePolicy]

This tells me that SQL Server is looking at that data (probably to determine that ValidTo and ValidFrom conform to some constraints).

The schema modification lock + the scan of the table is giving me grief.

In theory, the scan is unnecessary because the values are constant. In Microsoft's docs, example B, they mention "(a certain set of data checks happen in the background)" But perhaps those checks are unnecessary when the columns are brand new. So:

Is there any way to enable temporal tables online? Without placing a sch-m lock on the table while the table is scanned?

Improve this question
4
  • 1
    The scan is for error messages 13575, 13542. The execution plan has an assert - CASE WHEN [dbo].[InsurancePolicy].[ValidFrom]>sysutcdatetime() THEN (0) ELSE CASE WHEN [dbo].[InsurancePolicy].[ValidTo]<>sysmaxdatetime((7)) THEN (1) ELSE NULL END END - seems like this should be unnecessary though!
    – Martin Smith
    Aug 14 at 20:11
  • 1
    Interesting, I was wondering if SQL Server could be a little smarter if I added such a check constraint that matches the assert (as if SQL Server could recognize it as a filter and use an index or check constraint.) But then I noticed that the expression contains sysutcdatetime()
    – Michael J Swart
    Aug 14 at 20:30
  • 1
    The issue doesn't look specific to temporal tables. Same sort of scan and assert with ALTER TABLE InsurancePolicy ADD Foo INT NOT NULL DEFAULT 12 CHECK (Foo > 10) - so in general it would be nice if they could optimise this type of case!
    – Martin Smith
    Aug 15 at 8:32
  • In your example, I could defer adding the CHECK constraint, then add it WITH NOCHECK. It would be quick, but it would not be trusted FWIW.
    – Michael J Swart
    Aug 15 at 13:25

1 Answer 1

Reset to default
6

Is there any way to enable temporal tables online? Without placing a sch-m lock on the table while the table is scanned?

No. The checks are performed when PERIOD FOR SYSTEM_TIME is added, they can't be avoided, and the schema change is performed under a Sch-M lock.

You'd have to perform a gradual migration to new tables manually or implement your own history tracking solution.

The built-in temporal tables feature appears simple & convenient, but it comes with a lack of control and flexibility, as well as some undesirable behaviours depending on your intended use.

Improve this answer

Your Answer

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

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