Sample Header Ad - 728x90

Temporal Table: Multiple inserts on each update?

5 votes
2 answers
1226 views
**Actual Question:** Is there anything in sql 2019 server (besides a trigger) that can cause multiple rows to be inserted in the history table when a single row is updated in the temporal table? **Backgound:** I have an existing table called Candidate which has no computed columns and no triggers. I implemented versioning as follows: ALTER TABLE dbo.Candidate ADD BeginDate datetime2 GENERATED ALWAYS AS ROW START NOT NULL DEFAULT SYSUTCDATETIME(), EndDate datetime2 GENERATED ALWAYS AS ROW END NOT NULL DEFAULT CAST('9999-12-31 23:59:59.9999999' AS datetime2), PERIOD FOR SYSTEM_TIME (BeginDate,EndDate) GO ALTER TABLE dbo.Candidate SET(SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Candidate_History, DATA_CONSISTENCY_CHECK = ON)) When I do an update on the table, even a single column ( update candidate set lastContacted = '2021-10-16 23:27:46.927' where id = 44999 ) There are multiple rows added to the history table: | BeginDate | EndDate | | --------- | ------- | | 2022-01-24 08:25:55.3718538 | 2022-01-24 08:30:37.9523200 | | 2022-01-24 08:30:37.9523200 | 2022-01-24 08:30:37.9523200 | | 2022-01-24 08:30:37.9523200 | 2022-01-24 08:30:38.1853066 | | 2022-01-24 08:30:38.1853066 | 2022-01-24 08:30:38.1853066 | | 2022-01-24 08:30:38.1853066 | 2022-01-24 08:30:38.3093140 | | 2022-01-24 08:30:38.3093140 | 2022-01-24 08:30:38.3093140 | | 2022-01-24 08:30:38.3093140 | 2022-01-24 08:30:38.4352868 | | 2022-01-24 08:30:38.4352868 | 2022-01-24 08:30:38.4352868 | (The strangest thing is when the Begindate and EndDate are the same) The issue is probably in code (.net or some library) somewhere and not in SQL Before I go down the rabbit hole, I just wanted to know: **Is there's anything in SQL server (some setting I have missed) that can cause a single update on the table to insert multiple rows into the history besides a trigger?**
Asked by Paul Perrick (153 rep)
Jan 24, 2022, 10:04 AM
Last activity: Jan 28, 2024, 11:01 PM