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?