Where can I get SQL Server Transaction Begin Time from?

Question

I am working with temporal tables in SQL Server and the generated time is the transaction begin time in UTC datetime2

Is there a function or table in SQL Server to get that same transaction begin time to use elsewhere that does not require me to write to and select from a system versioned table first?

I have some non-system versioned tables that are part of the same transaction and I would prefer if the datetime2 recorded against them matched instead of using SysUTCDateTime and having it vary.

I tried to pull it from sys.dm_tran_active_transactions but its datetime and local server time and also appears to be different ?

system versioned: 2017-04-11 14:00:59.4690673

active transations: 2017-04-11 15:00:59.467

Answer 1

You can get the id of the current transaction from sys.dm_tran_current_transaction.

Then you can get the transaction_begin_time column from sys.dm_tran_active_transactions.

So, use one of the following T-SQL statements:

SELECT transaction_begin_time = dtat.transaction_begin_time
FROM sys.dm_tran_current_transaction dtct
    INNER JOIN sys.dm_tran_active_transactions dtat 
        ON dtct.transaction_id = dtat.transaction_id;

or

SELECT transaction_begin_time = dtat.transaction_begin_time
FROM sys.dm_tran_active_transactions dtat 
WHERE dtat.transaction_id = CURRENT_TRANSACTION_ID();

CURRENT_TRANSACTION_ID() returns the value of the transaction_id column from the sys.dm_tran_current_transaction.

Answer 2

that does not require me to write to and select from a system versioned table first?

As you are already writing to at least on versioned table in the transaction, you can use the OUTPUT clause to record SysStartTime (or other name if you are not using the common one) from there like so:

DECLARE @StampsOfTime TABLE (ts DATETIME2)
INSERT Testo (SomeField) OUTPUT (INSERTED.SysStartTime) INTO @StampsOfTime VALUES (1)
-- if using it multiple times it would be more efficient to put it in a variable instead of using the table var in future statements
DECLARE @ts DATETIME2 = (SELECT TOP 1 ts FROM @StampsOfTime)

You can then use this to populate the needed columns in non-temporal tables, and you've avoided having to write an extra row (the transaction was making this change anyway) or ferreting around system tables just to read back the full precision time, though it does require creating a table variable and selecting from that which may be no better. Another thing to watch for here is that you may get NULL back if the INSERT statement uses a dynamic result which could potentially return zero rows. The OUTPUT clause also works with DELETE, UPDATE, and MERGE statements.

Answer 3

new try with temporary AND temporal table:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN TRANSACTION;    
DECLARE @SYSUTCDATETIME_1st datetime2(7) = SYSUTCDATETIME();    
WAITFOR DELAY '00:00:00.100';    
DECLARE @SYSUTCDATETIME_2nd datetime2(7) = SYSUTCDATETIME();    
CREATE TABLE #SYSUTCTRANSBEGINTIME 
 (PK bit
  , SysStartTime datetime2(7) GENERATED ALWAYS AS ROW START
  , SysEndTime datetime2(7) GENERATED ALWAYS AS ROW END
  , PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
);

INSERT INTO #SYSUTCTRANSBEGINTIME (PK) VALUES (0);    
SELECT 
    SysStartTime AS SYSUTCTRANSBEGINTIME
   , @SYSUTCDATETIME_1st AS SYSUTCDATETIME_1st_sometimes_greater_SYSUTCTRANSBEGINTIME
    , @SYSUTCDATETIME_2nd AS SYSUTCDATETIME_2nd 
 FROM #SYSUTCTRANSBEGINTIME;    
DROP TABLE #SYSUTCTRANSBEGINTIME;    
COMMIT;

Answer 4

declare @TRANSACTION_BEGIN_UTC datetime2(7); set @TRANSACTION_BEGIN_UTC = SYSUTCDATETIME()

but MUST be saved in Variable at BEGINNING because SYSUTCDATETIME() changes during Transaction

Feb.1 2021: NOT WORKING! : found Example where SysStartTime is earlier:
SYSUTCDATETIME() = 2021-02-01 06:54:58.2040041
SysStartTime     = 2021-02-01 06:54:58.2030165