3

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

2

I'm not certain how you can reliably get the transaction start date as a datetime2, other than by saving the results of SELECT SYSUTCDATETIME(); at the start of the transaction.

However, as a work-around, you can return the automatically generated row start date for a temporal table, as in the following example.

First we'll create a system versioned temporal table:

IF OBJECT_ID('dbo.TemporalTest', 'U') IS NOT NULL
BEGIN
    ALTER TABLE dbo.TemporalTest SET (SYSTEM_VERSIONING = OFF);
    DROP TABLE dbo.TemporalTest;
END
IF OBJECT_ID('dbo.TemporalTestHistory', 'U') IS NOT NULL
DROP TABLE dbo.TemporalTestHistory;
CREATE TABLE dbo.TemporalTestHistory
(
    TemporalTestID int NOT NULL 
    , RowStartDate datetime2 NOT NULL
    , RowEndDate datetime2 NOT NULL
    , SomeData varchar(100) NOT NULL
) ON [PRIMARY];

CREATE CLUSTERED INDEX CX_TemporarlTestHistory 
ON dbo.TemporalTestHistory(TemporalTestID, RowStartDate, RowEndDate);

CREATE TABLE dbo.TemporalTest
(
    TemporalTestID int NOT NULL IDENTITY(1,1)
        CONSTRAINT PK_TemporalTest
        PRIMARY KEY CLUSTERED
        WITH (DATA_COMPRESSION = PAGE
            , ALLOW_ROW_LOCKS = ON
            , ALLOW_PAGE_LOCKS = ON
            , FILLFACTOR = 100
            , PAD_INDEX = OFF
            )
    , RowStartDate datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL
    , RowEndDate datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL
    , PERIOD FOR SYSTEM_TIME (RowStartDate, RowEndDate)
    , SomeData varchar(100) NOT NULL
) ON [PRIMARY]
WITH (
        SYSTEM_VERSIONING = ON (
            HISTORY_TABLE = dbo.TemporalTestHistory
            , DATA_CONSISTENCY_CHECK = ON
        )
    );

GO

Now, our transaction that will insert a row into the system versioned table, then modify that row, and finally display the inserted start date:

BEGIN TRANSACTION;

DECLARE @t TABLE
(
    RowStartDate datetime2 NOT NULL
);

INSERT INTO dbo.TemporalTest (SomeData)
OUTPUT inserted.RowStartDate
INTO @t (RowStartDate)
VALUES ('Max');

UPDATE dbo.TemporalTest SET SomeData = 'Max2' WHERE TemporalTestID = 1;

SELECT tt.TemporalTestID
    , tt.SomeData
    , tt.RowStartDate
    , tt.RowEndDate
FROM dbo.TemporalTest tt
UNION ALL
SELECT tth.TemporalTestID
    , tth.SomeData
    , tth.RowStartDate
    , tth.RowEndDate
FROM dbo.TemporalTestHistory tth

SELECT TOP(1) StartDate = t.RowStartDate 
FROM @t t;

COMMIT TRANSACTION;

The results:

+----------------+----------+-----------------------------+-----------------------------+
| TemporalTestID | SomeData |        RowStartDate         |         RowEndDate          |
+----------------+----------+-----------------------------+-----------------------------+
|              1 | Max2     | 2017-04-11 17:39:29.5290911 | 9999-12-31 23:59:59.9999999 |
|              1 | Max      | 2017-04-11 17:39:29.5290911 | 2017-04-11 17:39:29.5290911 |
+----------------+----------+-----------------------------+-----------------------------+

+-----------------------------+
|          StartDate          |
+-----------------------------+
| 2017-04-11 17:39:29.5290911 |
+-----------------------------+

The table variable, @t contains a row for each row inserted into the temporal table, which we use to obtain the row start date.

  • Yep I had considered this but seems extreme, I was hoping MS had the foresight to add a SYSUTCTRANSBEGINTIME or similar command considering they were using it themselves and it seems much more useful than SYSUTCDATETIME for the majority of scenarios to group related statements in a transactions – Trotts Apr 11 '17 at 17:53
  • Why can you not save the value of SYSUTCDATETIME() at the start of the transaction? Also, your suggestion might be a good thing to add to connect.microsoft.com – Max Vernon Apr 11 '17 at 18:17
  • It wouldnt match the value that is automatically generated when inserting into a system versioned table. Ill look to add it to connect as a feature request – Trotts Apr 12 '17 at 12:12
  • observationally, it matches for me. Still, I agree there is no reason why it would match every single time. – Max Vernon Apr 12 '17 at 15:45
0

I've just been interested in this as well.

AFAICT using sys.dm_tran_current_transaction you can get the id of the current transaction.

Then with sys.dm_tran_active_transactions you can get all transactions with the transactions_begin_time

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.