Skip to main content
Added note with regard to this likely applying to other database systems too.
Source Link
David Spillett
  • 32.4k
  • 3
  • 49
  • 91

Temporal Tables start/end date timestamps are identical for rows that are created inside a … transaction

This is by design and is a SQL Server thing, not EF specific.

All start times for rows in temporal tables will be the same in a given transaction, the time of the start of the transaction. This makes sense from the point of view of transactions being atomic, there would either be ambiguity or excessive locking between overlapping transactions otherwise.¹

If you modify a row multiple times in the same transaction each version therefore has the same start time so those preceding in the same transaction have the property starttime==endtime.

I get back only the current row! No way I can get the old rows too, but they exists! So, the history is all there but I can't get it!

Again this is by design. I'm not entirely sure I agree with this particular design decision but it is what it is! It only affects the standard querying syntax though, as you've identified the intermediate rows do exist so you can read them by querying the history table (union-all-ed with the base table) more manually. If you care about the order of the updates you will need to include your own revision counter in the table and modify it on each change, otherwise you can't tell which revisions came when within the set.


[1] For this reason I expect to see similar behaviour in implementations of the feature in other DBs², not just SQL Server
[2] In fact I'd not be surprised if it isn't in the standard³ given system-versioned tables are sometimes called “transaction time tables”
[3] system-versioned tables and related features were introduced in SQL:2011

Temporal Tables start/end date timestamps are identical for rows that are created inside a … transaction

This is by design and is a SQL Server thing, not EF specific.

All start times for rows in temporal tables will be the same in a given transaction, the time of the start of the transaction. This makes sense from the point of view of transactions being atomic, there would either be ambiguity or excessive locking between overlapping transactions otherwise.

If you modify a row multiple times in the same transaction each version therefore has the same start time so those preceding in the same transaction have the property starttime==endtime.

I get back only the current row! No way I can get the old rows too, but they exists! So, the history is all there but I can't get it!

Again this is by design. I'm not entirely sure I agree with this particular design decision but it is what it is! It only affects the standard querying syntax though, as you've identified the intermediate rows do exist so you can read them by querying the history table (union-all-ed with the base table) more manually. If you care about the order of the updates you will need to include your own revision counter in the table and modify it on each change, otherwise you can't tell which revisions came when within the set.

Temporal Tables start/end date timestamps are identical for rows that are created inside a … transaction

This is by design and is a SQL Server thing, not EF specific.

All start times for rows in temporal tables will be the same in a given transaction, the time of the start of the transaction. This makes sense from the point of view of transactions being atomic, there would either be ambiguity or excessive locking between overlapping transactions otherwise.¹

If you modify a row multiple times in the same transaction each version therefore has the same start time so those preceding in the same transaction have the property starttime==endtime.

I get back only the current row! No way I can get the old rows too, but they exists! So, the history is all there but I can't get it!

Again this is by design. I'm not entirely sure I agree with this particular design decision but it is what it is! It only affects the standard querying syntax though, as you've identified the intermediate rows do exist so you can read them by querying the history table (union-all-ed with the base table) more manually. If you care about the order of the updates you will need to include your own revision counter in the table and modify it on each change, otherwise you can't tell which revisions came when within the set.


[1] For this reason I expect to see similar behaviour in implementations of the feature in other DBs², not just SQL Server
[2] In fact I'd not be surprised if it isn't in the standard³ given system-versioned tables are sometimes called “transaction time tables”
[3] system-versioned tables and related features were introduced in SQL:2011

Temporal Tables start/end date timestamps are identical for rows that are created inside a … transaction

This is by design and is a SQL Server thing, not EF specific.

All start times for rows in temporarily versionedtemporal tables will be the same in a given translationtransaction, the time of the start of the transaction. This makes sense from the point of view of transactions being atomic, there would either be ambiguity or excessive locking between overlapping transactions otherwise.

If you modify a row multiple times in the same transaction each version therefore has the same start time so those preceding in the same transaction have the property starttime==endtime.

I get back only the current row! No way I can get the old rows too, but they exists! So, the history is all there but I can't get it!

Again this is by design. I'm not entirely sure I agree with this particular design decision but it is what it is! It only affects the standard querying syntax though, as you've identified the intermediate rows do exist so you can read them by querying the history table (union-all-ed with the base table) more manually. If you care about the order of the updates you will need to include your own revision counter in the table and modify it on each change, otherwise you can't tell which revisions came when within the set.

Temporal Tables start/end date timestamps are identical for rows that are created inside a … transaction

This is by design and is a SQL Server thing, not EF specific.

All start times for rows in temporarily versioned tables will be the same in a given translation, the time of the start of the transaction. This makes sense from point of view of transactions being atomic, there would either be ambiguity or excessive locking between overlapping transactions otherwise.

If you modify a row multiple times in the same transaction each version therefore has the same start time so those preceding in the same transaction have the property starttime==endtime.

I get back only the current row! No way I can get the old rows too, but they exists! So, the history is all there but I can't get it!

Again this is by design. I'm not entirely sure I agree with this particular design decision but it is what it is! It only affects the standard querying syntax though, as you've identified the intermediate rows do exist so you can read them by querying the history table (union-all-ed with the base table) more manually. If you care about the order of the updates you will need to include your own revision counter in the table and modify it on each change, otherwise you can't tell which revisions came when within the set.

Temporal Tables start/end date timestamps are identical for rows that are created inside a … transaction

This is by design and is a SQL Server thing, not EF specific.

All start times for rows in temporal tables will be the same in a given transaction, the time of the start of the transaction. This makes sense from the point of view of transactions being atomic, there would either be ambiguity or excessive locking between overlapping transactions otherwise.

If you modify a row multiple times in the same transaction each version therefore has the same start time so those preceding in the same transaction have the property starttime==endtime.

I get back only the current row! No way I can get the old rows too, but they exists! So, the history is all there but I can't get it!

Again this is by design. I'm not entirely sure I agree with this particular design decision but it is what it is! It only affects the standard querying syntax though, as you've identified the intermediate rows do exist so you can read them by querying the history table (union-all-ed with the base table) more manually. If you care about the order of the updates you will need to include your own revision counter in the table and modify it on each change, otherwise you can't tell which revisions came when within the set.

Wording tweak
Source Link
David Spillett
  • 32.4k
  • 3
  • 49
  • 91

Temporal Tables start/end date timestamps are identical for rows that are created inside a … transaction

This is by design and is a SQL Server thing, not EF specific.

All start times for rows in temporarily versioned tables will be the same in a given translation, the time of the start of the transaction. This makes sense from point of view of transactions being atomic, there would either be ambiguity or excessive locking between overlapping transactions otherwise.

If you modify a row multiple times in the same transaction each version therefore has the same start time so those preceding in the same transaction have the property starttime==endtime.

I get back only the current row! No way I can get the old rows too, but they exists! So, the history is all there but I can't get it!

Again this is by design. I'm not entirely sure I agree with this particular design decision but it is what it is! It only affects the standard querying syntax though, as you've identified the intermediate rows do exist so you can read them by querying the history table (union-all-ed with the base table) more manually. If you care about the order of the updates you will need to include your own revision counter in the table and modify it on each change, otherwise you can't tell which revisions came when within the set.

Temporal Tables start/end date timestamps are identical for rows that are created inside a … transaction

This is by design and is a SQL Server thing, not EF specific.

All start times for temporarily versioned tables will be the same in a given translation, the time of the start of the transaction. This makes sense from point of view of transactions being atomic.

If you modify a row multiple times in the same transaction each version therefore has the same start time so those preceding in the same transaction have the property starttime==endtime.

I get back only the current row! No way I can get the old rows too, but they exists! So, the history is all there but I can't get it!

Again this is by design. I'm not entirely sure I agree with this particular design decision but it is what it is! It only affects the standard querying syntax though, as you've identified the intermediate rows do exist so you can read them by querying the history table (union-all-ed with the base table) more manually. If you care about the order of the updates you will need to include your own revision counter in the table and modify it on each change, otherwise you can't tell which revisions came when within the set.

Temporal Tables start/end date timestamps are identical for rows that are created inside a … transaction

This is by design and is a SQL Server thing, not EF specific.

All start times for rows in temporarily versioned tables will be the same in a given translation, the time of the start of the transaction. This makes sense from point of view of transactions being atomic, there would either be ambiguity or excessive locking between overlapping transactions otherwise.

If you modify a row multiple times in the same transaction each version therefore has the same start time so those preceding in the same transaction have the property starttime==endtime.

I get back only the current row! No way I can get the old rows too, but they exists! So, the history is all there but I can't get it!

Again this is by design. I'm not entirely sure I agree with this particular design decision but it is what it is! It only affects the standard querying syntax though, as you've identified the intermediate rows do exist so you can read them by querying the history table (union-all-ed with the base table) more manually. If you care about the order of the updates you will need to include your own revision counter in the table and modify it on each change, otherwise you can't tell which revisions came when within the set.

Typo fixed.
Source Link
David Spillett
  • 32.4k
  • 3
  • 49
  • 91
Loading
Source Link
David Spillett
  • 32.4k
  • 3
  • 49
  • 91
Loading