Temporal Tables & SysEndTime - Which Nested Transaction?

Question

In "Temporal tables" (https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables) there is information about how the SysEndTime is set based on the the current transaction (https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables#how-does-temporal-work).

It is not specific, thought, about what the current transaction is when there are nested transactions.
In a nested transaction scenario, is the "current transaction" the innermost one that contains the temporal table DML statement (INSERT, UPDATE, DELETE), or the outermost one that actually commits the changes to the database?

Thank you,
Dan

Answer 1

There are no nested transactions in SQL Server. If you say BEGIN TRANSACTION that starts a transaction. If you then say BEGIN TRANSACTION, that only serves to increment @@trancount by 1, but that's all that happens. No new transaction starts. If you now say COMMIT TRANSACTION, nothing is committed. All that happens is that @@trancount is decremented by 1.

When you say COMMIT TRANSACTION and this brings down @@trancount to 0, the transaction is committed, and it is the timestamp for this commit you will see in the temporal table.

Answer 2

Based on the documentation "Temporal tables", the system stores the previous value of the row in the history table and sets the value for the SysEndTime column to the begin time of the current transaction (in the UTC time zone) based on the system clock. I think the current transaction should contains the temporal table DML statement.

Answer 3

@Erland Sommarskog , thank you. I wrote some test code, and your answer is consistent with what I found (assuming you meant to say "... brings down @@trancount to 0" in your second paragraph.

In the code sample below, I put the output from each statement in a comment after the statement. It clearly shows that the hstEnd value (2021-08-16 22:24:26.153) is set to the time just before the outer transaction is opened (2021-08-16 22:24:26.1529567).

Thank you.

use tempdb  
GO  
  
DECLARE @Tbl nvarchar(100) = N'dbo.Status';  
IF (2 = (SELECT temporal_type FROM SYS.TABLES WHERE object_id = OBJECT_ID(@Tbl, 'U ')))  
  EXEC(N'ALTER TABLE ' + @Tbl + N' SET (SYSTEM_VERSIONING = OFF);');  
EXEC(N'DROP TABLE IF EXISTS ' + @Tbl + N'_hst;');  
EXEC(N'DROP TABLE IF EXISTS ' + @Tbl +     N';');  
  
GO  
  
CREATE TABLE dbo.Status (  
  hstStart  datetime2(3) GENERATED ALWAYS AS ROW START  
, hstEnd    datetime2(3) GENERATED ALWAYS AS ROW END  
, PERIOD FOR SYSTEM_TIME(hstStart, hstEnd)  
, RPTY_ID             int               NOT NULL  
, CACHE_IQ            int               NOT NULL  
, RUN_REPO_ID         int                   NULL  
, RowzR               int                   NULL  
, RowzD               int                   NULL  
, CONSTRAINT PK_RPST PRIMARY KEY NONCLUSTERED (RPTY_ID, CACHE_IQ)   
)  
WITH (  
  SYSTEM_VERSIONING = ON (  
    HISTORY_TABLE = dbo.Status_hst  
  )  
);  
GO  
  
insert dbo.Status (RPTY_ID, CACHE_IQ, RUN_REPO_ID, RowzR, RowzD)  
values (2, 1234, 444, 1, 2)  
;  
/*  
(1 row affected)  
*/  
  
GO  
select SYSUTCDATETIME()  
;  
/*  
---------------------------  
2021-08-16 22:24:26.1529567  
(1 row affected)  
*/  
begin tran  
;  
waitfor delay '00:00:05'  
;  
select SYSUTCDATETIME()  
;  
/*  
---------------------------  
2021-08-16 22:24:31.1686990  
(1 row affected)  
*/  
begin tran  
;  
update  st set  
  RowzR = 11  
, RowzD = 22  
from    dbo.Status st  
where   st.RPTY_ID = 2  
and     st.CACHE_IQ = 1234  
;  
/*  
(1 row affected)  
*/  
waitfor delay '00:00:05'  
;  
select SYSUTCDATETIME()  
;  
/*  
---------------------------  
2021-08-16 22:24:36.1844797  
(1 row affected)  
*/  
commit tran  
;  
waitfor delay '00:00:05'  
;  
select SYSUTCDATETIME()  
;  
/*  
---------------------------  
2021-08-16 22:24:41.2001498  
(1 row affected)  
*/  
commit tran  
;  
  
select * from dbo.Status for system_time all  
;  
/*  
hstStart                    hstEnd                      RPTY_ID     CACHE_IQ    RUN_REPO_ID RowzR       RowzD  
--------------------------- --------------------------- ----------- ----------- ----------- ----------- -----------  
2021-08-16 22:24:26.153     9999-12-31 23:59:59.999     2           1234        444         11          22  
2021-08-16 22:24:26.106     2021-08-16 22:24:26.153     2           1234        444         1           2  
(2 rows affected)  
*/