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?
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!sysutcdatetime()
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!WITH NOCHECK
. It would be quick, but it would not be trusted FWIW.