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?