Temporal Table: Multiple inserts on each update?

Question

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?

Answer 1

No, there is nothing in SQL Server that will generate multiple history table rows from a single row modification to a temporal table.

The fact you are seeing history rows with zero validity duration means something is performing multiple data changes to that row within a transaction, as documented (emphasis added):

ℹ Note

FOR SYSTEM_TIME filters out rows that have period of validity with zero duration (SysStartTime = SysEndTime).

Those rows will be generated if you perform multiple updates on the same primary key within the same transaction.

In that case, temporal querying surfaces only row versions before the transactions and ones that became actual after the transactions. If you need to include those rows in the analysis, query the history table directly.

In the absence of a trigger, external code is submitting multiple data changes to the SQL Server.

Answer 2

A trigger on the temporal table that updates data can cause this.