patternsqlMinor
Can I enable system versioning (temporal tables) on a large table without scanning the table?
Viewed 0 times
temporalcanenabletableswithoutthesystemlargeversioningscanning
Problem
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:
When I run this statement, I can observe an event
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?
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?
Solution
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
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.
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.
Context
StackExchange Database Administrators Q#330288, answer score: 6
Revisions (0)
No revisions yet.