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 it is datetime and local server time and appear to be different.

• system versioned: 2017-04-11 14:00:59.4690673
• active transactions: 2017-04-11 15:00:59.467

Answer 1

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 time (only stored with datetime precision), 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 2

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 3

You can obtain the value using a temporary 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;